Linked SQL Tables (Access 2000)

Topic

New Reply

I discovered something today which has me confused on what needs to reside on a client pc in order to use an Access app that contains linked SQL tables.

In the app, I’m linking to the tables via ODBC using a dsn. I deploy the app as an mde to a user pc. I was under the impression that on a user pc, I had to have a SQL 2000 Client installed for connection only, and that the dsn also had to be in the client’s ODBC Data Sources folder.

However, today I discovered that neither of these were necessary. The app worked fine on the user pc. It seems like as long as the user had a valid SQL login, they had access to the linked tables within the app.

So, is the dsn connection info stored with the app? Is that why the dsn doesn’t need to reside on the user pc and the SQL Client doesn’t need to be installed?

Any enlightenment would be greatly appreciated!!

Thanks!!!

Reply | Quote

Replies

You certainly don’t need SQL Server Client installed to be able to connect to a SQL Server database. If you really connect through a DSN, I would expect that the end user would need to have a DSN of the same name on his/her PC. However, it is possible to create a DSN-less connection. In that case, the user doesn’t need anything special (except, of course, a valid login to the SQL Server database/

Reply | Quote

Thanks, Hans.

So, just for clarification, you’re saying that when you link a table in MS Access via ODBC and furnish all the dsn connection info, that this dsn info is stored with the app. Once the app is deployed to the user, the user is connecting to it via the stored dsn info. It’s a dsn-less connection in that the user doesn’t have to have a dsn of the same name on his pc.

Is that right?

Reply | Quote

A DSN is not stored in the Access database, but either in a file (file DSN) or in the Windows registry (user DSN and machine DSN). So if you use a DSN to connect to SQL Server, another user won’t be able to use your database unless he/she also creates a DSN. If you bypass the use of a DSN by specifying the connection string in the database itself, other users will be able to use your database as long as they have a valid login name/password.

Reply | Quote

Well, that ‘s interesting Hans. Now I’ve got to go look at their registries.

I am using a file dsn to link the tables in the app. The dsn resides on my pc, and dsn configuration indicates that SQL use the Windows login for verification.

I’m still not sure how the user is actually reading this file dsn, since it does not reside in the targeted folder on their pc. Maybe it is in their registry. Or maybe, since a file dsn, by definition, can be shared by users with the same drivers installed, the users don’t have to have the dsn reside on their pc.

I’ll have to research this some more. I thought I understood this better…

Thanks again, Hans!

Reply | Quote