Connecting access 2007 to sql server 2012

Question

I've linked to a table in an sql server 2012 database using an odbc connection. This was straightforward and worked fine using my log in. When I attempted to open the linked table on another users computer I got an error saying I could not connect. I tried to set up the connection on their PC but couldn't see the database on the server. I logged on to sql server and gave that user a log on to the sql and access to the database using windows log in.

I still could not gain access to the database through access. Any idea what I'm doing wrong.

Answer 1

One Idea, you shall create all linked tables with a privileged user account of the SQL Server.

For example, if you enabled Trusted_Connection in the SQL Server, you can use the administrator's account of Windows, for example, johndoe2 for connection. If your SQL Server is installed on the LAN host masterserver, a linked table can be created as this:

ODBC;DRIVER=SQL Server;SERVER=masterserver\SQLEXPRESS;Database=[MyDatabase];UID=johndoe2;Trusted_Connection=Yes;TABLE=dbo.[MyTable]

In our case we can access MyTable throuhg Access 2007 on the host biomas that is on the same LAN as the masterserver.