Connection between SQL Server and MS Access

Hi :

I have a question of the connection between SQL Server and MS Access.

I have a project, which is using SQL Server as backend, we use MS Access forms as the frontend.

Firstly, I created the datbase (db1) in the SQL Server, and create a few tables in db1. And then, I create a few forms in the MS Access, I used ODBC connection to link tables from SQL Server to MS Access, it is working fine. Those forms can access the linked tables.

After it is done, I put the MS Access database in the sharing drive, many people can share the files in this sharing drive.

When I go to my associate's computer, and then open the MS Access database in the sharing drive. The forms is working, but, it cannot access to linked tables from SQL Server.

I go back my computer again and then open the MS Access database, it is working fine.

What's the problem with it?
Does anybody need to install SQL Server software and also have db1 database in SQL Server and the path?

I want any user can access the linked tables from SQL Server, but I do NOT want any user can open the SQL Server and open the tables directly. I just want the users can access the linked tables through MS Access Forms only. Because I will make security in the MS Access. So, I don't let the users modify/open the tables in the MS Access, the users must access the tables via the forms only.

Please tell me the steps how to solve it.
Please help.

Thanks.

Hi Accessman2,
If you have the DSN that established the OBDC link on your computer, and it is not shared, the other user may not be able to establish the connection to the SQL server. You may also need to give all your users db_datareader access on the SQL database for them to be able to establish a connection.

Hope this helps,

Jay