Search code examples
sql-serverms-access-2016

MS Access DNSless connection to MS SQL Server userid/pass fail

I am connecting to a MS SQL server from a laptop with MS Access 2016. My intent is to insert some local data into the SQL server. I have a user account on the SQL server with the user type setting of, 'SQL user with login'. I use the connection string below:

connStr = "ODBC;Driver={ODBC Driver 13 for SQL Server};" & _
        "Server=" & dbServer & ";" & _
        "Database=" & dbName & ";" & _
        "User=" & UID & ";" & _
        "Password=" & PWD & ";"
Dim cdb As DAO.Database, qdf As DAO.QueryDef
Set cdb = CurrentDb
Set qdf = cdb.CreateQueryDef("")
qdf.Connect = connStr
qdf.SQL = "INSERT INTO theTble (field1, field2) VALUES ('data1', 'data2');"
qdf.ReturnsRecords = False
qdf.Execute dbFailOnError  <--- this line opens the dilogue box

When the line runs to execute the query I get a dialogue box:

SQL Server Login dialogue box.

If i change the Login ID field contents to the login used in the connection string and add the password to the password field and click OK, the connection is made and the SQL insert query is executed properly.

I have tried a number of connection strings; so far this is the only one that will get me this close. I need this to run without having to manually type in the userID/Pass. Also, a linked table is not going to work in my situation.

Thanks for looking.

Solution

  • Following on from my comments, I thought I'd submit an actual answer.

    The dialog box appears to have picked up the data from the connection string with the exception of the password. That leads me to believe that the Password parameter in the connection string is incorrect. I'm fairly sure that for ODBC connection strings, the parameter for password should be PWD={password}. (As a side note, I'm fairly sure the User should be UID={username} too, but the dialog seems to have picked your username up, so presumably User={username} is a suitable alternative.

    Here's a Microsoft Docs Link - scroll down to the connection string format... it shows UID={username};PWD={password} in the connection string.

    Failing that, does your SQL user have INSERT permission on to the table?