I’m trying to connect to a new SQL Express Db and can establish the connection with a BAT file, but not in VBA. The name in SQL Server Management Studio is: Data Source=James-desktopSQLEXPRESS.History. I can connect with SMS as well at the BAT. I do have Integrated Security (Windows Authentication) setup, and since it works with the BAT, that presumably can’t be the problem.

I have used each of the connectionStr values shown below and they all produce the same error (the connection string output is different for all, but the rest of the error msg is unchanged).

Error:
Attempting to establish connection to the database…
Connection String: Provider=MSOLEDBSQL;Data Source=James-desktopSQLEXPRESS;Initial Catalog=History;Integrated Security=True;Connect Timeout=30
Error Number: -2147217887
Error Description: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Failed to establish connection to the database.
Connection failed!

Here’s the VBA, and again, I tried all of those connectionStr statements seperately.:

Sub TestDatabaseConnection()
Dim dbFilePath As String
Dim connectionStr As String
Dim conn As Object

' Set the database file path and connection string
dbFilePath = "C:BeakerMarket DataDatabaseHistory.mdf"


connectionStr = "Provider=MSOLEDBSQL;Data Source=James-desktopSQLEXPRESS;Initial Catalog=History;Integrated Security=True;Connect Timeout=30"
connectionStr = "Provider=SQLOLEDB;Data Source=James-desktopSQLEXPRESS;Initial Catalog=History;Integrated Security=True;Connect Timeout=30"
connectionStr = "Provider=MSOLEDBSQL;Data Source=James-desktopSQLEXPRESS;Initial Catalog=History;Integrated Security=True;Connect Timeout=30"
connectionStr = "Provider=SQLOLEDB;Data Source=(LocalDb)v16.0;Initial Catalog=History;Integrated Security=True;Connect Timeout=30"
connectionStr = "Provider=MSOLEDBSQL;Data Source=James-desktopSQLEXPRESS;AttachDbFilename=" & _
    dbFilePath & ";Integrated Security=True;Connect Timeout=30"

' Print the connection string to the immediate window for debugging
Debug.Print "Attempting to establish connection to the database..."
Debug.Print "Connection String: " & connectionStr

' Open the connection
Set conn = CreateObject("ADODB.Connection")
On Error Resume Next ' Continue execution even if an error occurs
conn.Open connectionStr
If Err.Number <> 0 Then
    ' Print the error details to the immediate window
    Debug.Print "Error Number: " & Err.Number
    Debug.Print "Error Description: " & Err.Description
    Debug.Print "Failed to establish connection to the database."
End If
On Error GoTo 0 ' Reset error handling to default behavior

' Check if the connection was successful
If conn.State = 1 Then
    Debug.Print "Connection successful!"
    ' Close the connection
    conn.Close
Else
    Debug.Print "Connection failed!"
End If

' Cleanup
Set conn = Nothing

End Sub

Here is the BAT file I can use and it connects fine:
set server=James-desktopSQLEXPRESS
set database=History

rem Test connection using sqlcmd with Windows Authentication
sqlcmd -S %server% -d %database% -E -Q “SELECT 1”

rem Check errorlevel to determine success
if %errorlevel% equ 0 (
echo Connection successful!
) else (
echo Connection failed!
)

Any ideas?