Language: Deutsch English















Last Update: 2024 - 05 - 21








Access-Client-Server

Table of Contents

  • Using other Backend-DBMS for Access-Data-Projects (ADPs)
  • 'The Microsoft Access project 'xyz' will be opened read-only...' with multiuser-ADP
  • Connecting an Access application to an ODBC data source fails on Windows 7 x64
  • How to change an Access-ADP-application’s connection at runtime
  • How to output data from an ADO-Recordset-Column as comma separated string using local sorting and filtering
  • How to Create and Install a SSL/TLS Certificate for SQL Server
  • Sort a Form Bound to an ADO.Recordset using Access UI Features
  • Access Bug - Assigning a Recordsource at runtime disables ODBC Multi-Row-Fetch for continuous form
  • SQL-Server-Express-Backup - Fundamentals
  • Automate SQL Server Express Database Backups
  • Restore SQL Server Express Database from backup
  • The Access ADP (Hi)Story
  • Choosing an Authentication Mechanism for Access + SQL-Server-Applications
  • ODBC Linked Tables in Access - Mechanisms and Peformance
  • New Bug in Access 365 V2205 - #Deleted in ODBC tables with NVarchar PK
  • How to extract server/database name from ADP file



  • Using other Backend-DBMS for Access-Data-Projects (ADPs)

    Is it possible to use Oracle/MySQL/Sybase/my DBMS as Backend in an Access Data Project (ADP)?

    No, it isn't! Right now it is simply not possible to use any other DBMS than Microsoft SQL Server and MSDE (Microsoft Data Engine) as Backend for an Access-Data-Project and there is definately no workaround to circumvent this limitation.

    Until now there is no official information available that Microsoft plans to support other DBMS as Backends for ADPs in the future. As to integrate support of another DBMS into the ADP-Architecture will incur a major effort, even with updates for new versions of the DBMS, and will highly depend on the features of that DBMS, I think it is extremly unlikely that Microsoft will support any Third-Party-DBMS in ADPs ever.



    Back to the TOC

    'The Microsoft Access project 'xyz' will be opened read-only...' with multiuser-ADP

    If you try to open an ADP-File (Access Data Project) in a multiuser environment that is already opened by another you get the message 'The Microsoft Access project 'xyz' will be opened read-only because one of the following occurred:'. What can one do about that?

    This is behavior by design. With a MDB-Application all information about which user is editing which object is stored within the .LDB-File. This enables multiple users to open and edit the same MDB-File at the same time. This issue is described in the Microsoft Knowledgebase article 318868.

    With an ADP-File there is no LDB-File at all, therefore Access has to assume that the first user that opens an ADP-File is making changes to the object-definitions of forms, report, etc. and locks the whole file. For this reason all subsequent users will only get read-only access to that file. This write-protection only affects objects local to the ADP-File (Forms, Report, etc,) but not the data stored in the SQL-Server-Database.

    The simple workaround for this problem is to open Access and the ADP-File in Runtime-Mode. Because it is not possible to make any changes to the object-definition in Runtime-Mode anyway, Access will not issue any warning about the project being opened read-only.

    You can apply this workaround by opening the ADP not directly but by using a shortcut which contains the Runtime-Switch in the command line. The command line of such a shortcut should roughly look like this:

    "C:\Path\to\MSACCESS.EXE" /runtime "C:\Path\to\YourApplication.adp"
    

    Unfortunately this workaround only does the trick with up to 20 concurrent users of the ADP-File. The 21st user opening the ADP will receive the message "The file is not in the correct format for a Microsoft Access project." and it is not possible to open any further instances of the ADP-App.

    A better solution to the whole problem is to put a copy of the ADP-File on each user's own client-computer. Besides solving the original problem this approach has several other advantages. It will result in slight performance benefits, because the Access-Objects (Forms, Reports, etc.) can be loaded from a local file and haven't to be loaded over the network any more. Furthermore this approach will increase stability of the front-end-file because corruption of Access-Files is extremely less likely if only one user works with a file at a time. Even if there is a corruption-issue nevertheless it will not affect all users of the application but only the one whose local copy of the file got corrupted.

    To update the local ADP-files with newer versions you can start your application using a script or a small startup-program that copies the new version to the client if required. You can either develop your own solution to this problem or you can use an existing solution like Tony Toews' Auto FE Updater.



    Back to the TOC

    Connecting an Access application to an ODBC data source fails on Windows 7 x64

    The problem

    You are running your Microsoft Access application on a 64bit version of Microsoft Windows 7 and you need to connect to an ODBC data source (e.g. a SQL-Server-Database). You configured a data source name (DSN) using the ODBC Data Source Administrator utility and verified that the DSN is correct by using the test feature of ODBC Admin. But if you want to add a new linked table to your Access application, the DSN you created is not visible. If you created the linked table previously on another machine, all connection attempts from your Access application fail with a SQLState 08001 error.

    The explanation

    Access usually is installed as a 32bit Application. On a Windows 64bit version operating system Access will run in the 32bit-WindowsOnWindows(WOW)-subsystem. But the all the default shortcuts to the ODBC Data Source Administrator utility on Windows 7 x64 will open the 64bit version of this utility. Whatever you see or configure there is not relevant, as your application running 32bit Access is not able to read these configuration values from the registry.

    The solution

    The Solution is simple, if you really understand the problem. You need to explicitly open the 32bit version of the ODBC Data Source Administrator utility and configure the data source there. To make absolutely sure you start the right version, use Windows Explorer to open the .exe file C:\WINDOWS\syswow64\odbcad32.exe directly.

    No problem on Windows 8

    This problem does not exist on Windows 8 any more, as the ODBC Data Source Administrator clearly shows in it’s title bar, which version you are using. Furthermore, as long as there is a 32bit- and 64bit-version of the database driver, all ODBC DSNs configured in Windows 8 will be created in the 32bit-WOW-environment as well as in the 64bit native environment.

    Addendum regarding OleDb UDL files

    A similar problems exists when editing the Data Link Properties of OleDb UDL files on Windows x64. If you open an UDL file from Windows Explorer with the OpenDSLFile-function of oledb32.dll (That is what a double click in explorer does by default) there will only 64-bit OleDB-Providers being displayed, because the 64-bit version of oledb32.dll is used.

    To get the list of available 32-bit OleDb-Provider you need to explicitly open the Data Link Properties with the 32-bit version of oledb32.dll. You can use this command line (run as Administrator) to add an “Open32” entry to the Windows Explorer context menu.

    reg add "HKEY_CLASSES_ROOT\MSDASC\shell\Open32\command"^ /t REG_EXPAND_SZ /v ""^ /d "%SystemRoot%\SysWOW64\rundll32.exe \"%CommonProgramFiles(x86)%\System\Ole DB\oledb32.dll\",OpenDSLFile %1"

    Thanks to Markus Melk, who pointed out this issue and created a reg file to add the Open32 menu item.



    Back to the TOC

    How to change an Access-ADP-application’s connection at runtime

    Is it possible to change the connection of a Microsoft Access Data Project (ADP) at runtime?

    If you know how, it is fairly simple to achieve this. However the required objects and methods are not so easy and intuitively to find in the Access object hierarchy. Therefore I’m going to explain this here.

    Opening (and closing) the connection of an ADP-application

    You would expect that you could just use the Connection.Open-Method of the CurrentProject.Connection (ADODB.Connection) for this. This is not quite right though. It doesn’t work to use the Connection-object directly, you have to use the OpenConnection and CloseConnection-Methods of the CurrentProject-Object instead.

    To find out the correct connection string for this connection can be another problem. If you look at the connection string you will see a "Microsoft.Access.OLEDB.10.0"-provider. But if you try to use this provider in your own connection string to open the connection, this will fail. You have to use the Provider "SQLOLEDB.1" typically used for OleDb connections in your connection string. You can look at the BaseConnection-Property of the CurrentProject-object to get an idea how your connection string should look like.

    It does not work to use a provider for a different database system than Microsoft SQL Server there. See my article on using other backend systems for ADPs.

    With this information it is now easy to write a VBA procedure that changes the connection of the ADP at runtime.

    Public Sub OpenADPConnection(ByVal strUser As String, ByVal strPassword As String) Const strCONNECTION_STRING As String = _ "Provider=SQLOLEDB.1;Data Source=yourServerName;Initial Catalog=yourDatabaseName;" CurrentProject.OpenConnection strCONNECTION_STRING, strUser, strPassword If Not CurrentProject.IsConnected Then MsgBox "Connecting the application failed" End If End Sub

    Changing the connection for deployment in a production environment

    Above is the solution to the core problem. Still you might find the following information on a closely related problem helpful.

    You usually want to connect your ADP-application to your client’s SQL Server after the application is deployed in the client’s environment. You encounter the inconvenient problem that, as soon as you open the project file, Access tries to connect to the database server that was previously used with the ADP project. Usually this will be your development server, which is probably not available in the production environment. Access tries to connect to this server anyway and hangs until the connection timeout has expired. Only then it cancels the connection attempt with the error message that server is unreachable.

    To avoid this annoying problem it is not sufficient to just explicitly close the existing connection before packing the file for deployment. As long as there is a formally valid connection information stored in the ADP-file, Access will try to open the connection based on this information the moment the file is opened.

    To solve this issue, you have to deploy your ADP application in a disconnected state with no connection information stored at all. You can remove the stored connection information from the ADP-file by calling the OpenConnection-method of the CurrentProject-object without parameters.

    Call CurrentProject.OpenConnection

    After that method call the ADP is connectionless and Access will not try to open any connection on startup. So your code to connect the ADP at startup can now run without delay to properly connect your application. Now your application is ready for deployment.



    Back to the TOC

    How to output data from an ADO-Recordset-Column as comma separated string using local sorting and filtering

    This article is to long to fit the format of this page. Follow the link to an extensive article describing how to use the Clone-, Filter- and Sort- Method/Properties to create a comma separated string from an existing ADO-Recordset in a client-server-scenario without querying the remote data source.



    Back to the TOC

    How to Create and Install a SSL/TLS Certificate for SQL Server

    Using SSL-encrpyted connections to SQL Server is something you should even consider in development when you connect via an unsafe netowrk. Configuring SQL Server for SSL/TLS is actually not that difficult. But there are a lot of pitfalls by incomplete documentation and stupid tools. As I do not want to waste another few hours on this ever again, I wrote a step-by-step guide on creating and installing a self-signed SSL/TLS certificate in SQL Server.



    Back to the TOC

    Sort a Form Bound to an ADO.Recordset using Access UI Features

    There are several issues with Forms bound to ADO.Recordsets, which are very powerful when working with Access and SQL Server backends. One of them is the problem that built in Access UI controls for sorting and filtering data will produce error messages. This is my long quest to workaorund these issues and find a solution to using these UI controls to sort and filter a form without error message.



    Back to the TOC

    Access Bug - Assigning a Recordsource at runtime disables ODBC Multi-Row-Fetch for continuous form

    There is a rather esoteric issue with continuous forms you will probably never care about. - Unless you are working with Microsoft Access and SQL Server backends hosted on remote Azure servers in the cloud. If this is the case you might be very interested in these workarounds for Access disabling ODBC Multi-Row-Fetch for continuous forms when you set the Recordsource at runtime.



    Back to the TOC

    SQL-Server-Express-Backup - Fundamentals

    SQL-Server-Express is the natural upgrade path from a plain Access database to a real client-server-application. Of course, a regular backup of your database is still vital. However, a basic file backup is not possible any more without further ado. Therefore I explain the fundamentals of SQL-Server-Backups, focusing on the requirements for SQL-Express-Databases.



    Back to the TOC

    Automate SQL Server Express Database Backups

    Microsoft SQL-Server-Express is a viable choice for a department or small company production database. The only thing it is missing is SQL Agent to schedule automatic backups.

    Here is a complete guide on how to create automatic, scheduled backups using only tools included with either SQL Express or the Windows operating system.



    Back to the TOC

    Restore SQL Server Express Database from backup

    This 3rd. and final article in the mini-series about backups of Microsoft SQL Express databases. I explain the two most common scenarios that require the restore of a database. So, this wraps up the topic by explaining how to restore a database from a backup created earlier.



    Back to the TOC

    The Access ADP (Hi)Story

    Access Data Projects were a very powerful project type in Microsoft Access. They were discontinued with Access 2013 and were a major reason why several of my customers froze their Microsoft Office installations at Office 2010 until now. For me the time has come to finally say goodbye to them and migrate these projects to AccDBs. - To bid them farewell I wrote down the story of ADPs in Access.



    Back to the TOC

    Choosing an Authentication Mechanism for Access + SQL-Server-Applications

    Microsoft SQL-Server is a much more secure platform for your data than a Microsoft Access backend database can ever be. However, there are several options and strategies for user authentication and authorization in SQL-Server based applications. The overall security of your application significantly depends on the right fitting choice for your application. This text should give you some advice on what to consider before making that choice.



    Back to the TOC

    ODBC Linked Tables in Access - Mechanisms and Peformance

    Linked ODBC tables are the recommend way to build a client-server-application with Microsoft Access and a backend DBMS like SQL Server, Oracle, or MySQL. I had to dig up this ancient text explaining the hidden mechanisms of ODBC tables in Access and their effect on performance.

    Back to the TOC


    New Bug in Access 365 V2205 - #Deleted in ODBC tables with NVarchar PK

    With the new Microsoft Office Update 2205 comes a new bug to Microsoft Access which causes some ODBC linked SQL Server tables to display "#Deleted".



    Back to the TOC

    How to extract server/database name from ADP file

    ADP files are mostly obsolete, but you still may need to know the server and database name an ADP connected to. I explain how to retrieve these ADP connection properties with and even without Access 2010.



    Back to the TOC

    Subscribe to my newsletter

    *

    I will never share your email with anyone. You can unsubscribe any time.
    This email list is hosted at Mailchimp in the United States. See our privacy policy for further details.

    Benefits of the newsletter subscription



    © 1999 - 2024 by Philipp Stiefel - Privacy Policiy