Run Stored Procedure in SQL database from Access VBA without windows authentication

Question

I am trying to run a stored procedure (using Access VBA) in the SQL database. I can find a few examples of this on SO and on other sites but what I am unable to find is connection string where I can provide my login details as windows authentication won't work because I have to use a different user to connect to SQL database

Here is what I have tried so far (got this from one of the post in SO, unfortunately, I can't find the post anymore):

Sub RunSQLProc()
    Dim cdb As DAO.Database, qdf As DAO.QueryDef
    Set cdb = CurrentDb
    Set qdf = cdb.CreateQueryDef("")
    qdf.Connect = "ODBC;" & _
            "Driver=SQL Server;" & _
            "Server=myServer;" & _
            "Database=myDatabase;" & _
            "UID=myUsername;" & _
            "PWD=myPassword;" & _
            "Trusted_Connection=yes;"
'            "Driver={SQL Server Native Client 11.0};" & _
'            "MARS Connection=True;"
    qdf.SQL = "SQLStroedProcedure;"
    qdf.ReturnsRecords = False
    qdf.Execute dbFailOnError   '<-- this line throws the error
    Set qdf = Nothing
    Set cdb = Nothing
End Sub

On qdf.Execute dbFailOnError line, I get the following error:

Run-time error '3151': ODBC--connection to 'SQL ServermyServer'failed.

I have managed to connect to the SQL database and run the stored procedure through SQL Server Management Studio. This tells me that the details in my connection string are correct (although I haven't provided the actual details here) but I suspect there is an issue with my connection string.

Happy to provide more details. Any help is much appreciated, thanks

P.S. Apologies for my lack of knowledge in Access. I just don't use it very often

Z . · Accepted Answer · 2019-04-16 12:21:05Z

1

to use SQL Server security remove Trusted_Connection=yes.

https://www.connectionstrings.com/microsoft-sql-server-odbc-driver/

answered Apr 16, 2019 at 12:21

Z .

12.8k2 gold badges34 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Zac Over a year ago

Hi Zdravko, thanks for the response. Unfortunately I've tried that but I still get the same error as in my post

Z . Over a year ago

Change Driver=SQL Server to Driver={SQL Server}. What version of SQL server?

Zac Over a year ago

Just tried that, same result unfortunately. Could it be possible that SQL server is rejecting the connection because it's not windows authentication?

Z . Over a year ago

yes, it is possible. the sql server needs to be configured to accept sql server auth. try to connect with something like the Enterprise Manager or similar tool to verify that the account actually works.

Z . Over a year ago

no, that is windows auth. in the login dialog select SQL Server auth and then try.

|

Collectives™ on Stack Overflow

Run Stored Procedure in SQL database from Access VBA without windows authentication

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related