How to use ADODB on MS SQL Express in Access 2000 or 2016 VBA?

[kutlesh]

I have this connection string:

Dim cnnString as String = "Provider=SQLOLEDB;Data Source=myMachineName\SQLEXPRESS;" & _
"Initial Catalog=MyDatabase;" & _
"Integrated Security=SSPI;"

And my code snippet:

Private Sub adodbQueryBtn_Click()

On Error GoTo dbErrorLbl
                  
    Set dbConn = New ADODB.Connection
    dbConn.Open cnnString
    Set adoRST = dbConn.Execute("SELECT * FROM MyTable;")
    
dbErrorLbl:
    Debug.Print "------------------------------------------------"
    Debug.Print "ADODB conn error: " & Err.Number & ", " & Err.Description & ", " & Err.Source & ", " & Err.HelpContext & ", " & Err.HelpFile

End Sub

I keep getting this error:

ADODB conn error: -2147467259, [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified, Microsoft OLE DB Provider for ODBC Drivers, 1000440, C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA7.1\1033\VbLR6.chm

What do I do wrong?

[kutlesh]

Turns out I have been sending empty string as conn. string to my connection object.
It happens

When tired get rest first

[Zvoni]

Errr? Huh?
Dim cnnString as String = "Provider=SQLOLEDB;Data Source=myMachineName\SQLEXPRESS;" & _
"Initial Catalog=MyDatabase;" & _
"Integrated Security=SSPI;"

Declaring and assigning in the same line?

Dim cnnString As String
cnnString = "Provider=SQLOLEDB;Data Source=myMachineName\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=SSPI;"