File dsn location

Question

We are in the process of moving several users to Windows 10.  They have several file dsn's that are used for Access and I can't see m to find the file location of the File DSN.  The goal is to migrate the ODBC connections with little post.  Any advice would be greatly appreciated.

THanks!

Answer 1

Hi,

See if this previous thread helps.

Good luck!

Answer 2

A few things.

First up we talking about a “file” DSN. When you link a table with a file DSN, then Access DUMPS the file DSN, and from that point on does NOT use the DSN.

In effect, the “default” for Access is DSN-less. Once you link the database using that DSN, then you are free to distribute that application to any workstation and no need to copy + install + have that DSN copied to each workstation.

So do keep in mind that the “act” of copying or transferring those file DSN’s to your new computer will not change anything nor take effect UNTIL such time you re-link the tables and choose that DSN again during the table re-link process.

What the above suggests is that you much really don't need to copy over the DSN’s.

However, you can if you wish. DSN’s by default are placed here in windows 10:

C:\Program Files (x86)\Common Files\ODBC\Data Sources

Quite sure win 7 is the same. However, note that you find by default you can’t create DSN’s in the above when you let Access launch the ODBC panel (you have to choose browse and use a location like my documents). The reason for this is the above folder requires “admin” rights to change or modify – so you often find that Access cannot create DSN’s in that folder.

Also keep in mind that I am talking about a “file” DSN. If you use a “system” DSN (and I recommend you do not), then the setting are not in a file, but are saved in the registry.

So just keep in mind that once you link a table with a file dsn, from that point on Access ignores and does not use nor require that DSN. As noted, this allows you to distribute the application with linked tables to any workstation without having DSN’s on each workstation.

So you can copy the older DNS’s to the new machine, and use the above folder, or even create a folder in my documents. The key concept here is that Access tables once linked, are now DSN-less and do not require the original DSN file that is ONLY used during the re-link process.

Regards,

Albert D. Kallal

Edmonton, Alberta Canada

Answer 3

You mentioned ACCESS doesn't use the file dsn until the tables are linked.  Why when we test the Access project, it doesn't know the file DSN location eventhough it is the same location as before?  WE have placed the file on a new file server but we reused the same path

Answer 4

Well, perhaps some clarification is required here. You now introduced the NEW term “access project”. If you talking about an ADP project in Access, then all of the advice given above is NULL and void, and does not apply.

I would also point out that ADP projects are deprecated, and the last version that supports ADP projects was Access 2010 – versions after 2010 cannot use nor consume ADP projects.

So “context” here is everything. The assumed context was a standard “non ADP” project with linked tables.

Since ADP projects no longer exist with Access, then the general assuming here was that of using linked tables – not a ADP project which works rather different.

So if you talking about a standard access accDB application, then the advice given applies (Access ONLY uses the file dsn ONE time during linking and after that the DSN is not required). In the case of the deprecated ADP file type, then what you describe makes sense, and I believe that Access does use and require the DSN on start-up.

So you have to clarify if this is in fact an ADP project. If it is, then you likely want to start migrating the ADP to a standard accDB, since Access 2010 is the last version that supports ADP projects.

Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada