MSAccess vba dsnless connection to sql server; password issues

Connie S 21 Reputation points
2021-05-17T13:27:44.68+00:00

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?

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
825 questions
0 comments

8 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. DBG 2,301 Reputation points
    2021-05-17T17:05:44.31+00:00

    Hi. For yourself, when you create the linked table, don't forget to check the Save Password box. However, this would mean all users of your db will use your credentials. If you don't want that, you'll have to add a login form and prompt the user for their credentials.

    0 comments
  2. Connie S 21 Reputation points
    2021-05-19T12:28:12.387+00:00

    :-o

    Well now...don't I feel silly! As many times as I've made linked tables, I've never looked at that little checkbox!

    Thanks thedbguy...I've made the change and it seems to work for me. Now need to see about pushing it out to others.

    0 comments
  3. DBG 2,301 Reputation points
    2021-05-19T14:31:57.65+00:00

    Hi. Glad to hear you got it sorted out. Good luck with your project.

    0 comments
  4. Connie S 21 Reputation points
    2021-05-24T13:13:28.197+00:00

    thedbguy, finally got a chance to test this on another computer and getting error 3151.

    Somewhere I read that if I set up a file dsn, I wouldn't have to worry about setting it up on each machine to run this. Is this true? Or do I need to set up a file dsn on each machine? Although I've been coding vba in msaccess for 20 years, I've not done much with odbc connections.

    0 comments