DSN for linked tables to SQL server

[boerbende]

why DSN for linked tables to SQL server

Dear readers,

I have several Access 2010 databases uploaded to SQL server with the upload tool in Access. Result are front end in Access with linked tables to the data in SQL server.
I have added users with read and write permissions under SQL server with management studio.

Some of the databases work without extra configuration users. Other databases give an ODBC error (also for ALL users). To avoid the ODBC error I have to add a DSN on the users computer and then the Access frontend can get the data from SQLserver.
I checked several things, but there seems to be no difference in the userpermissions, no differences in the linked tables in Access and no differences in the database under SQL server.
Does anybody know why there could be this difference between databases?

Especially with the last database I converted, I have a lot of users. And off course this one requires a DSN which is a lot of configuration.
So you can imagine I would like to have the Access frontend working without DSN

Many thanks for thinking with me

regards,

Ben

 

[pbaldy]

One option is creating a File DSN on the server and linking with that. No configuration is necessary on individual computers. You can also look at DSN-less connections, which I haven't used but keep meaning to try.

 

[boerbende]

Hello

thank you for you answer. I do not know why databases 1 to 5 are running without dsn while suddenly database 6 requires a dsn. It was uploaded exactly thesame.
But using Dsn files: what do I have to do to make my database know where it can find the dsn file. You mentioned to put it on the server, but it is not clear to me how the client computers know this

regards

ben

 

[pbaldy]

It knows it from when you link the tables using it. Since yours are already linked, you either relink using it or delete the linked tables and link them again.

 

[boerbende]

Works fine! This is far less work than 20 computers 2x
Thanks

 

[pbaldy]

Happy to help!