ADODB error connecting to SQL

[JamesSF]

hello

im getting this error conecting to SQL

5 [Microsoft][SQL Server Native Client 11.0][SQL Server]Changed database context to 'chronos'.

with this code

Public Function Connect() As String
            
        On Error GoTo ErrHandler

         
         Dim CN As ADODB.Connection
         Set CN = New ADODB.Connection


        CN.ConnectionString = "DSN=chronosTEST;"
        CN.Open
        

        
ErrHandler:
               
        Err.Raise Err.Number
        Resume Next
        
End Function

Actually, I get this with any connect string I try

CN.Open "Driver={SQL Server};Data Source=s-tc-sql01;Database=chronos;User ID=user;Password=password"
CN.Open "Driver={SQL Server Native Client 11.0};server=sql.transcom-solutions.com;Database=chronos;User ID=user;Password=password;Trusted_Connection=yes"
CN.Open "Provider=MSDASQL;Driver={SQL Server Native Client 11.0};server=sql.transcom-solutions.com;Database=chronos;User ID=user;Password=password"

what am I missing? is it a problem with the SQL server?

thanks in advance!

[Minty]

Daft question - can you get any response from it - are you normally able to ping that server ?

[JamesSF]

yes - see screenshot - both internal and external name

[Gicu]

Maybe have a look at these links:

https://stackoverflow.com/questions/...th-classic-asp

https://social.msdn.microsoft.com/Fo...=sqldataaccess

Cheers,
Vlad

[JamesSF]

thanks - replacing the previous line with this

CN.Open "Provider=SQLOLEDB;Data Source=10.160.0.4;Initial Catalog=chronos;User ID=user;Password=password"

gives a new error

5 Invalid procedure call or argument

[Gicu]

I believe you have SQL Server Native Client 11 - try Provider=SQLNCLI11

https://www.connectionstrings.com/sq...ledb-provider/

[JamesSF]

I had downloaded the Microsoft® OLE DB Driver 18 for SQL Server
when I got the error

if I change the provider to this

CN.Open "Provider=SQLNCLI11;Data Source=10.160.0.4;Initial Catalog=chronos;User ID=user;Password=password"

I get the same error

5 Invalid procedure call or argument

[Gicu]

Do you have the MS Management Studio installed? Can you log in and see the database objects using the user/password you are trying in the connection strings? What permissions does the user have?

Cheers,
Vlad

[JamesSF]

Do you have the MS Management Studio installed? Can you log in and see the database objects using the user/password you are trying in the connection strings? What permissions does the user have?

Cheers,
Vlad

yes - can log in as the user in the connection string. can view data and edit

[Gicu]

And can you link a SQL table from the SQL chronos db into your Access app?

[JamesSF]

yes i can using the same machine DSN referenced in the first post.

[Minty]

The DSN refers to chronosTEST db, what happens if you change the DSN to attach to chronos db instead and test the connection in the DSN dialog?

[JamesSF]

The DSN refers to chronosTEST db, what happens if you change the DSN to attach to chronos db instead and test the connection in the DSN dialog?

good observation!

however, that is labeled test so its not used in production.

just to be sure - I changed to the code to another machine DSN - the live one used inside access

CN.ConnectionString = "DSN=s-tc-sql01;"

I get this error - 5 Invalid procedure call or argument

here is setup for that DSN

[Gicu]

So you have a live Access application running on the same machine/user that is working and another trying to connect to a different SQL db (chronos)? What kind of authentication is the SQL server set up for, is it in mixed mode or Windows authentication only? You use Windows authentication in the DSN pics, can you check in SSMS what permissions are attached to the user for the chronos db?

[JamesSF]

the live acccess db and a blank one with just this connection string are on the same machine.

here are the screenshots for that user account











thanks!