Data Source issues with Windows 10 and Access 2016

Question

Prior to our company moving to Windows 10 Enterprise 2016, if I made a change to a front-end Access 2016 database and copied it to other users in the department, they might have to "trust" the database, but I never had to go and relink all the backend ODBC datasource tables. Now I have relink them using the Linked Table Manager for every PC I am copying the modified database to. Any ideas?

Answer 1

As a general rule, you should just have to re-link on your developer work station, and then you can freely distribute to all the other workstations like you did in the past.

You should not have to re-link for each workstation.

Once you link the tables (and I assume you using a FILE DSN), then access creates all table links as DSN-less, and like before you were free to distribute the front end to each workstation.

So it not clear “now” why you have to re-link.

However, there are a few things that would cause this issue.

You perhaps re-linked the tables on your developer workstation using the newer “native 11” or some later ODBC driver that you installed. However, if this same driver is NOT on each workstation, then you will (as you are now!!!) have to re-link, since those workstations don’t have the Native 11 (or later) drivers installed.

All computers since windows XP have the long time “legacy” SQL driver installed.

“SQL Server”

So if you choose above, then you can be sure that driver is on all workstations. Now I will say that the “newer” drivers such as:

“SQL Server Native Client 11.0”

Is a better choice, but you THEN have to ensure that this driver is installed on each workstation. If in the past you did not update these workstations with a newer driver, then obvious it is better to use the older “SQL Server”.

So I would double check what your workstation settings are that you used to link the table.

CLEARY something has changed since as you note in the past you never had to re-link.

So I would re-link on your workstation and ensure you pick a driver that you know to be on each workstation.

Next up:

Ensure you choose + create a FILE DSN, since Access always converts that to DSN-less, and thus you don’t need to setup, configure any kind of DSN on each workstation.

Last but not least:

You “can” consider some VBA re-link code, but as you note, you worked in the past without issues.

Regards,

Albert D. Kallal (Access MVP, 2003-2017)

Edmonton, Alberta Canada

Answer 2

Scott,

Did not try this since I do not have Admin rights and need I/T to add/set all ODBC configurations.  I finally had enough time, with I/T help, to test what Albert Kallal's suggested below. I set up a File DSN on my PC and copied it to each PC in my department. Then I/T updated everyone's SQL driver to "SQL Server Native Client 11.0". Then I relinked all my backend tables to the new File DSN. So far, it seems to have fixed the issue. I appreciate your suggestion. It sounds intriguing!

Dan

Answer 3

Thank you Albert. I am working with our network manager and still are having issues with the DSN, but we truly haven't had enough time to work on it. We think we are almost there!

Answer 4

Thank you Scott. I will give it a try!

Answer 5

Yes, You should use code in your database to do the relinking for you. For example:

http://www.jstreettech.com/downloads.aspx

Please Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.