Solved Fail in connecting to sql server

[KitaYama]

I've had the following code that's been working for several years.

Option Explicit

Private Const Server = "Server"
Private cn As New ADODB.Connection
Private rs As New ADODB.Recordset

Public Function ConnectToDB_ADO()
    Dim Con As String
    On Error Resume Next
   
    If cn.State = 1 Then Exit Function
    Con = "Driver={SQL Server};Server=" & Server & ";Database=Receiption;Trusted_Connection=yes;"
    cn.Open Con
    Debug.Print cn.State
    Do While cn.State <> adStateOpen
        DoEvents
        If cn.State = adStateClosed Then Exit Function
    Loop
   
End Function

I'm not sure since when, but recently this fails to open a connection to server.
There's no trouble in adding linked tables from sql server to my database.

I'm not sure, but there's a good chance it's happening since installing ODBC Driver 18.

Any kind of help is much appreciated.

 

[Minty]

Your connection string isn't right for the new driver.
It should be something like

DRIVER=ODBC Driver 18 for SQL Server;SERVER=...

 

[KitaYama]

Your connection string isn't right for the new driver.
It should be something like

DRIVER=ODBC Driver 18 for SQL Server;SERVER=...

1- After installing ODBC Driver 18, Am I not allowed to use the previous drivers? (in this case Driver={SQL SERVER})
2- Changed as suggested. Still I can not connect to server. cn.State returns 0

this is the exact code I'm using now:

Option Explicit

Private Const Server = "Server"
Private cn As New ADODB.Connection
Private rs As New ADODB.Recordset

Public Function ConnectToDB_ADO()
    Dim Con As String
    On Error Resume Next
   
    If cn.State = 1 Then Exit Function
    Con = "Driver=ODBC Driver 18 for SQL Server;Server=" & Server & ";Database=Receiption;Trusted_Connection=yes;Encrypt=no;"
    cn.Open Con
    Debug.Print cn.State
    Do While cn.State <> adStateOpen
        DoEvents
        If cn.State = adStateClosed Then Exit Function
    Loop
   
End Function

Any further advice is much appreciated.

 

[KitaYama]

I tested this one. Result the same.
Debug.print cn.State returns 0

Con = "ODBC;DRIVER=ODBC Driver 18 for SQL Server;SERVER=" & Server & ";DATABASE=Receiption;Trusted_Connection=Yes;Encrypt=no;"

 

[Minty]

Hmm interesting
1. You can of course use an older connection string provided that older driver is loaded on the machine. I would not mix drivers between tables in a production environment, it may lead to weird issues
2. Connect to a table manually using the wizard and the ver 18 driver, then examine the connection string and compare the two. Is the Database name correct? Receiption - should that be Reception ??

 

[KitaYama]

The database name is correct.
I will follow your reproducing the connection string.

I'll be back.
I'm not the terminator

 

[sonic8]

1- After installing ODBC Driver 18, Am I not allowed to use the previous drivers? (in this case Driver={SQL SERVER})
2- Changed as suggested. Still I can not connect to server. cn.State returns 0

Of course you can still use older drivers when newer ones are also installed.

However, with ADO you usually use an OLEDB provider, not an ODBC driver. So, your current connection string is not suitable for the task at hand.

 

[KitaYama]

@Minty
Million thanks for your help.
I don't normally switch off my PC. It's on 7/24.
It seems my PC was waiting for a reboot after a Windows update.

Rebooting the PC solved the problem.
I really thank you for your help.

For anyone visiting this thread later, both the following connection strings worked.

Con = "Driver={SQL Server};Server=" & Server & ";Database=******;Trusted_Connection=yes;"
Con = "DRIVER=OLEDB Driver 18 for SQL Server;SERVER=Server;DATABASE=******;Trusted_Connection=Yes;Encrypt=no;"

 

[KitaYama]

Of course you can still use older drivers when newer ones are also installed.

However, with ADO you usually use an OLEDB provider, not an ODBC driver. So, your current connection string is not suitable for the task at hand.

@sonic8 You mentioned this in my previous posts, and gave me a link to a site with different sort of connection strings.
Unfortunately I couldn't find a connection string with OLEDB that could work for me.

What's the correct connection string for OLEDB provider?
Thank you.

 

[KitaYama]

This seems to be OK. But it's only the rsult of trial and error.
Is this correct way of using this provider :

Public Function ConnectToDB_ADO()

    Dim Con As String
    On Error Resume Next
   
    If cn.State = 1 Then Exit Function
    Con = "Provider=sqloledb;Data Source=Server;Initial Catalog=MyDatabase;Trusted_Connection=Yes;"
    cn.Open Con
    Do While cn.State <> adStateOpen
        DoEvents
        Debug.Print cn.State
        If cn.State = adStateClosed Then Exit Function
    Loop
   
End Function

Thanks

 

[sonic8]

What's the correct connection string for OLEDB provider?

You posted one in your previous post. In "OLEDB Driver 18 for SQL Server" Microsoft changed the term "Provider" to "Driver". The important part to distinguish them is the "OLEDB" in front.

"Provider=sqloledb;Data Source=Server;Initial Catalog=Receiption;Trusted_Connection=Yes;"

This is also correct, but uses the older OLEDB provider for SQL Server.

The other connection string with ODBC driver, that also works, probably uses the "Microsoft OLE DB Provider for ODBC Drivers", which is sort of an adapter between the two different connectivites. This works but introduces another layer of translation, which should be avoided if there is a suitable OLEDB provider available.