Last Update: 2018 - 06 - 02
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
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.
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 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.
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
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
© 1999 - 2018 by Philipp Stiefel - Privacy Policiy