MSAccess vba dsnless connection to sql server; password issues

Question

I am trying to create a DNSless connection to an SQL server we host that is managed by someone else. I want to be able to create an accde front end so others on the network can run reports I’ve designed in modules I’ve created in vba, from data on said sql server. Here’s what I have so far:

I created an ODBC 32 bit file DSN connection to the sql server and for the authentication, chose ‘With SQL Server authentication using a login ID and password entered by the user’.

I then created a linked table in MSAccess to this table. As it is not a trusted connection, it asked me to log in. I was able to open the table and see the data.

If I close MSAccess, then reopened the database and tried to look at the table or run a vba query, I’m asked for a password again. It defaults to my windows user id.

So… I found how to edit the file dsn and add the password (already had the user name). I opened the file with notepad and added the info. When I open the file dsn via the odbc data source administrator, looks good (a password is there)!

I build a new linked table in msaccess and used the new connection with the password. No prompt when building the link. And I can open the table without a password!

I exit MSAccess and reload the database. Now I am prompted for the user id & password, defaulting to my windows id. Grrr!

How can I automate logging in to the sql server so that the users (or any scheduled reports that run) do not have to enter that user id?

Answer 1

If you use the ODBC Wizard on the new machine, are you able to get a successful connection?

Answer 2

Hi. That is correct. When you use a File DSN to connect to a server, Access converts it into a DSN-less connection. What does the error message for 3151 say?

Answer 3

ODBC connection failed

Answer 4

I am not. Tried copying my file dsn to this machine and it said it was invalid. Tried setting up a new one and could not find the server.

Thanks for your help! I will reach out to the network administrator and see what's up.

Answer 5

Okay, good luck. Sounds like it may be a network issue.