ODBC and Linking (1 Viewer)

[Privateer]

I need some confirmation on something that I have noticed is causing a connection problem. When I build a front end I am linked to tables on SQL Server using an ODBC connection with the DSN of "BEData". When I send the accde to a client with the same DSN but pointing to their server, the connection fails. I was under the impression that Access would look for the DSN, find it, and connect. But this is not happening. Did I just skip over that chapter on ODBC?

So my suspicion is this, my connection string is attached to the table and causing it to fail when sent to a client. So the fix would be to run a query on open, get the connection string at the client and refresh the link on all the tables where the first four letters are ODBC. Can anyone confirm that this has to be done on every front end? Also, do I need to adjust the security for the installation folder so code will be allowed to run on their machine?

I have searched this issue and found various code options, but no one has really come out and listed all the things you have to do the make this work. As always, any help or links will be greatly appreciated.

 

[Ranman256]

Try the 'no code' option of putting the linking info in the ODBC app,
in control panel,
admin tools,
data sources ODBC?
adding the connection info here.

it also has a TEST button.

 

[Privateer]

yeah, that is in there and the test is successful. If I use link table manager and refresh the tables, the user can open them and see the data. But without the refresh, double clicking on a table gets you and ODBC error. So it that the answer, it should work with the info in the ODBC app?