3

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?

Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
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
    – taddy hoops
    Commented 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
    Commented Aug 7, 2015 at 15:26
  • 1
    Unfortunately this answer does not show which file DSN an existing linked table uses.
    – Jeff Puckett
    Commented 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
    Commented Jul 15, 2017 at 2:08
  • 1
    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.
    – Jeff Puckett
    Commented Jul 15, 2017 at 14:51
1

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
Improve this answer
1
  • What I find very confusing is that (in my case at least), when adding a connection through Machine DSN, the connection string shows both the DSN and the SERVER property when hovering over the linked tables. If I then make changes on the DSN configuration (eg. changing the server or making the connection read-only), these do not seem to be applied on the client side.
    – onietosi
    Commented Jun 2 at 14:48

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