Extracting Connection Info from ADP Projects (Server and Database Name)

by Philipp Stiefel, originally published 2024-02-15

last revision on 2024-01-25

Photo by marian anbu juwan on Pixabay

Microsoft Access Data Projects (ADP) are mostly obsolete today. Nonetheless, it may still happen that a customer contacts you to extract and restore the data from an ADP file. For applications not deemed critical before that migration, this often happens after the customer’s Access installations were updated to a newer version and the customer noticed that they cannot open .adp or .ade files anymore.

There is no easy way to convert an Access ADP to the current .accdb file format. The conversion is a manual process that should be done by a developer with sufficient Access and SQL Server knowledge.

What About the Data?

The good news and also the bad news is that there is no data stored in the ADP file itself. An .adp/.ade file is a pure front-end file containing only the application user interface and the client-side business logic of the application. – If your intention was to extract data from the ADP file, you can stop right now.

All data of an ADP is stored in a Microsoft SQL Server database. As long as the server database file or its most recent backups still exist, it is technically easy to retrieve all the data using SQL Server standard tools.

If the server is still in operation, you can connect to it using SQL Server Management Studio and then export the data or continue to use the database with a new frontend application.

If the server itself does not exist anymore, but you got the database files or a current backup, you can attach the database to a different server or restore the backup on that server. – Some hurdles regarding the server version compatibility might have to be overcome. This is a story for another article on this site. Until then, you should easily find other sensible resources on that matter elsewhere.

Where is the Server? – Extracting Server and Database Name

You might think this is a ludicrous question…

…until you have to answer it. Often the users who worked with the ADP application have no clue which server and database it connected to. Server administrators usually know about their databases but often have no idea which client applications connect to their databases.

So, unless there is current documentation of the application available, answering the question is not as easy as you might have thought.

Looking up Server and Database Name in Access 2010

If you still have a working installation of Access 2010 available, the server name and the database name can be quickly retrieved; you just need to know where to look.

Open the .adp or .ade file in Access 2010, click “File” in the ribbon, go to “Info” and in the list of commands of the “Server” dropdown, click “Connection”. (The “Server Properties” dialog will only display the information if the ADP is currently connected and thus is of no use to us in our scenario.)

This will open a “Data Link Properties” dialog in which the server name and database name are displayed.

Once you got this information, you can now connect to the server using other tools such as SQL Server Management Studio and/or identify the responsible administrators.

If the above dialog does not display the desired information, it was manually deleted by a user. Doing this makes sense before deploying an .adp/.ade file from a development/test environment to production. Often the ADP would be reconnected in the production environment, and then would again include the changed connection info. If the dialog is empty in a file that was actually used in production, the ADP project must include VBA code that reads the connection string from a configuration file or from constants embedded in the code, and then connects the application at runtime.

If you have Access to the VBA source code, you should investigate the code that is run by either the AutoExec macro or the startup form of the application.

If you do not have the source code, you are not in the best of positions. You can try to open the compiled .ade file in a hex editor and search for terms associated with the connection string or for a file path potentially pointing to a configuration file.

You can also try the approach explained below, even though it may only produce old, superseded values.

Looking up Server and Database Name in a Hex Editor

If you do not have Access 2010 available, you might still be able to quickly retrieve the information. Open the .adp or .ade file in a hex editor. – I use NEXT-Soft Hex-Editor MX as a simple but effective tool for quite some time.

Hint: Before you open the file in an unfamiliar tool, it might be a good idea to make a backup first.

Once you opened the .adp or .ade (it works with both) file in the hex editor, you need to find the relevant locations within the file. As you are searching for terms you do not know yet, it would be an arduous task without further information. Luckily, I’ve got some more directions for you.

You can use the search functionality of the hex editor to find the following terms.

Please note: These text strings in the file are stored in Unicode with zeroes in between ANSI characters. If your editor has no built-in feature to convert text terms from ANSI to Unicode it’s easier to search for the hex representation of the strings, which I also provide below.

The database name of the ADP project is stored following the term "Property0*233" in the file. The star (*) character is single digit that may vary from file to file.

Similarly, the server name is stored following the term "Property0*59". Again, the digit represented by star (*) character may vary.

Unfortunately, my hex editor does not support wildcard search. So, I must search for just "Property0" (Hex: 500072006F007000650072007400790030) and then manually cycle through all results (usually ~10-20) to find the desired values.

As you can see in the screenshot, the ADE file I inspected above was connected to the database “TestDb” on the SQL Server instance “corumsql2022”.

You may find multiple occurrences of the above properties and their values. – I’ve never seen more than two. – This appears to be the current, active value of the property and probably the most recent previous value.

On the one hand these duplicate occurrences are unfortunate, because they may leave you with two possible options for each setting. On the other hand, this will allow you to retrieve the original values after they were cleared from the connection dialog in the Access UI (see above). Whether these superseded values still have any relevance for you I cannot tell.

If you don’t find any of the above search terms in the file, you can try searching for:

"ProviderCLSID" (Hex: 500072006F007600690064006500720043004C00530049004400)

This term must be in the file and the server and database name will most likely be located very close to it. – This term may also be in the file twice!

Of course, it is possible though very unlikely that there is no server and database name in the file at all.

Disclaimer

All the above information about the ADP file internals is reverse engineered. I believe it to be universally correct, but obviously cannot guarantee anything.

Share this article: