Solved Linked database fails when opened on a fresh PC

[Garren.Shannon]

Hey all,

Preface - I have not worked in Access or SQL is a good many years and I know things have changed so thanks for the help in advance.

I have a working database on my PC where I setup links to 5 tables. On my system, the database works fine. I changed the extension to .accdr to try to lock it down a bit and copied it to a new PC running the same version of Access 2019.

On a new PC, I logged in as admin, setup a System DSN connector on the PC and verified the link to the SQL server. Then I logged out and back in as a standard user and if I open the ODBC tool, I can see the connector but I can't edit it of course. However, when I open the database I get a runtime error and Access closes.

If I change the name back to .accdb and open it I get "ODBC = Connection to <database> failed. Error 3151".

If I go to add an external data source >SQL, and select "Machine Data Source" I don't see the connector, which I am sure is the cause for the whole thing not working.

I am sure this is easy but I am at a loss. How can I embed access to the linked table inside the app so it will work as designed?

 

[theDBguy]

Not an expert but try using a File DSN instead.

 

[Garren.Shannon]

Not an expert but try using a File DSN instead.

Thanks DB Guy. I have never done that before but it was easy enough. Can you program Access VB to include a file based DSN on load?

 

[arnelgp]

you can also create a DNS-less connection:
Create DSN-less connection to SQL for linked table - Office | Microsoft Learn

 

[GPGeorge]

Hey all,

Preface - I have not worked in Access or SQL is a good many years and I know things have changed so thanks for the help in advance.

I have a working database on my PC where I setup links to 5 tables. On my system, the database works fine. I changed the extension to .accdr to try to lock it down a bit and copied it to a new PC running the same version of Access 2019.

On a new PC, I logged in as admin, setup a System DSN connector on the PC and verified the link to the SQL server. Then I logged out and back in as a standard user and if I open the ODBC tool, I can see the connector but I can't edit it of course. However, when I open the database I get a runtime error and Access closes.

If I change the name back to .accdb and open it I get "ODBC = Connection to <database> failed. Error 3151".

If I go to add an external data source >SQL, and select "Machine Data Source" I don't see the connector, which I am sure is the cause for the whole thing not working.

I am sure this is easy but I am at a loss. How can I embed access to the linked table inside the app so it will work as designed?

Are the accdbs on both computers 32 bit, or both 64 bit?

 

[theDBguy]

Thanks DB Guy. I have never done that before but it was easy enough. Can you program Access VB to include a file based DSN on load?

You can, but you don't need to. Access automatically converts a file dsn into a dsn-less connection.

 

[Isaac]

Hey all,

Preface - I have not worked in Access or SQL is a good many years and I know things have changed so thanks for the help in advance.

I have a working database on my PC where I setup links to 5 tables. On my system, the database works fine. I changed the extension to .accdr to try to lock it down a bit and copied it to a new PC running the same version of Access 2019.

On a new PC, I logged in as admin, setup a System DSN connector on the PC and verified the link to the SQL server. Then I logged out and back in as a standard user and if I open the ODBC tool, I can see the connector but I can't edit it of course. However, when I open the database I get a runtime error and Access closes.

If I change the name back to .accdb and open it I get "ODBC = Connection to <database> failed. Error 3151".

If I go to add an external data source >SQL, and select "Machine Data Source" I don't see the connector, which I am sure is the cause for the whole thing not working.

I am sure this is easy but I am at a loss. How can I embed access to the linked table inside the app so it will work as designed?

A User DSN is just that - made for one user.

Try a file DSN as dbguy suggested.

After you manually create a file DSN, please right click and open it in Notepad. You will notice it is nothing but a plain text file in its guts.

Thus, you can execute VBA code to simply CREATE that "plain text file", only make its file extension match however your file DSN looked.
Look into CreateObject("scripting.filesystemobject"), and Textstreams.

Of course all of this is much easier assuming you have your Windows Explorer folder Options set to always show file extensions - one of the first silly defaults [to not show them] that I change when setting up a new PC on a modern Windows version.

 

[Garren.Shannon]

Thanks all... you guys are AWESOME!!! The DSN-Less connection worked best for this application. This group is a true godsend.

 

[Garren.Shannon]

Are the accdbs on both computers 32 bit, or both 64 bit?

Both are 64bit PC's.