2

I am using Access as a front end database to connect to SQL Server data. On my computer, it works just fine. On a user computer, it doesn't work. Has anyone successfully done this without creating a DSN on each computer? Thanks, Jeff

Andre
  • 26,751
  • 7
  • 36
  • 80
jsic1210
  • 21
  • 1
  • 4
  • You need to explain what "it doesn't work" means. If there are error messages, provide them. If there are differences between the two PCs, explain those differences. But no, you probably can't do this without creating a DSN on the client PC since MS's instructions state that a DSN is needed. https://support.office.com/en-us/article/Link-to-SQL-Server-data-0474c16d-a473-4458-9cf7-f369b78d3db8 – alroc Nov 10 '16 at 13:54
  • Yes, it's called a **DSN-less** connection. See http://stackoverflow.com/a/23430539/3820271 – Andre Nov 10 '16 at 14:43
  • Possible duplicate of [How do I get ms-access to connect to ms-sql as a different user?](http://stackoverflow.com/questions/50786/how-do-i-get-ms-access-to-connect-to-ms-sql-as-a-different-user) – Andre Nov 10 '16 at 14:44
  • 1
    If you create a linked table and use a file DSN, then the resulting linked table is DSN less and should work when you transfer the database to another computer. It not clear if you using SQL auth, or windows auth when you connect. If you not on a "domain" network, then you need to re-link using a SQL server logon - once done then you should be able to freely to distribute the database to other users. – Albert D. Kallal Nov 10 '16 at 19:52

2 Answers2

0

You will need to make sure what ever user is trying to connect to the DB has rights in SQL Server to view it. Access won't just remove the security needed to view it. You could create a DB role then put everyone needing to get into that DB into the role and grant that role access. You could also get an AD group created for anyone that needs to view the table and get people added to that group then grant that group access.

Wes Palmer
  • 880
  • 4
  • 15
0

You can connect using an ODBC connection with saved credentials to trick the sql server into thinking the user is someone else. However, i would only do this and provide read only access. Then, you can revoke access to that username whenever and the whole system stops. Easy to do., just build an ODBC connection and save the username and password in it.

Destin Consulting
  • 86
  • 5