Access-Client-Server

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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".

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.