2

I set up a linked tables in Microsoft Access to connects to an remote database using ODBC with a File DSN. But which DSN file is Access using now? I want to examine it and compare it with the other versions.

The Linked Table Manager only lists the tables and the name of the database.

I tried renaming all of the DSN files I could find, and the ODBC connection still runs. Is Access storing the connection string itself?

2 Answers 2

1

IIRC, Access uses either a machine DSN or a file DSN. I only have Access 2016 installed but I don't think this has changed much. There should be an External Data tab in the ribbon, click on ODBC Database. This shows you two tabs: File Data Source and Machine Data Source. enter image description here

This image shows the MS Access Database source but I generally see folks create one specific to the Access database they created.

You can also views this by opened the ODBC Data Sources from Administrator tools. There is a 32 and a 64 bit version of this and you may need to check both.

Also, with regards to Access storing the connection itself. It is possible to setup a DSN-less connection using macros. You can see this KB on how that is done to know where to check in your Access database.

5
  • I know I made it a File DSN. Sadly the MS Access 2007 ribbon doesn't have that lovely button, only Import and Export ones. How can it keep working even with the File DSN renamed and MS Access restarted? imgur.com/a/2EhPi Aug 7, 2015 at 14:02
  • Do you open Access as the "administrator" account? I recall I had one Access file I supported years back that they had multiple users and if I did not open it as the Admin it was locked down to the options I could see/look at.
    – user507
    Aug 7, 2015 at 15:26
  • Unfortunately this answer does not show which file DSN an existing linked table uses. Jul 13, 2017 at 19:03
  • @JeffPuckett Considering you can create DSN-less connections to linked tables, at least SQL Server there may not be one at all.
    – user507
    Jul 15, 2017 at 2:08
  • Maybe not, so perhaps the answer should be revised because in its current state, it doesn't really answer the particular question. This answer is helpful information nonetheless. Jul 15, 2017 at 14:51
0

If an ODBC linked table was created with a File DSN, then it's not using any DSN.

MS Access will pull the information from the File DSN into the linked table and build a connection string using that information directly, rather than storing the path to the File DSN. Once this connection string is built, the linked table no longer references the File DSN for anything. For better or worse this functions exactly as if you had hardcoded the connection string and never used a DSN in the first place.

On the other hand, creating a linked table with a machine data source behaves as expected (or at least, as I would expect). The linked table maintains a reference to the DSN it was created with, and will only work if that DSN is present.

To see this for yourself, the connection string can be viewed by hovering over the linked table:

  • If the connection string was created with a File DSN, it will have a SERVER property:

    enter image description here


  • If the connection string was created with a Machine DSN, it will have a DSN property: enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.