Linked tables to MS SQL fail with permission error

[pmbtech]

Hello,
A client of mine had there sever go belly up. They got a new servr and we re-installed MS SQl and restore with no problem.
They have an access app on the sever that is linked to the SQL Tables. I created a new ODBC connection using the same DSN as before and re-linked the tables. Everything runs fine from the server. The mdb is on the server and the clients have a shortcut to the mdb on thier machines. When they open the database from a client machine they get a permission denied error, if we open the mdb from the server evrything works fine. Doesn't the mdb use the DSN on the machine it is on to connect? Do I have to create an ODBC connection on each client? I created a user in SQL and gave that user permission to all the tables and used that user in the ODBC connection on the server. Is there something in Access I have to do? I didn't write the Access app and am at a loss as to why it won't connect when run from a client machine.

 

[Blorf]

Hi. When you create your ODBC Connection, specify you want a File DSN. Link the MDB using the File DSN (Located some were everyone can reach it) and you can avoid creating the ODBC on every machine.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[pmbtech]

I'll give that a try and let you know.

Thanks