VBA Code connecting to sql server 2019

Question

SQL Server 2019 is installed locally. I do not understand this error

Answer 1

The connection to SQL Servr works fine. Trying to connect to sqlserver to write dynamic queries does not work.

Answer 2

thank you.

Show us the VBA, then. All of it. Not just snipped lines as in the original post.

I suspect your problem has to do with creating an ADO connection, as shown in the line you shared, but later you attempt to work with a DAO recordset instead of that ADO connection. But we have to see it all to know what is happening.

We need to see 100% of the procedure in question along with the specific line in it which raises the error.

Answer 3

Code

=======================================

Private Sub cmdProcessing_Click()

Dim File As String

File = "City.xlsx"

Dim DirFile As String

DirFile = "C:\Users\clowry\Desktop\CityOfChicago" & File

If Dir(DirFile) = "" Then

MsgBox "City.xlsx does not exist and must stop"

Else

End If

DoCmd.SetWarnings False

txtProcessing.Value = "Truncate City_renewal_temp_truncate"

DoCmd.OpenQuery "City_renewal_temp_truncate", acViewNormal, acEdit

txtProcessing.Value = "Truncate City_renewal_temp_truncate was successful"

txtProcessing.Value = "Importing CITY.XLSX file"

DoCmd.RunSavedImportExport "Import-CITY"

txtProcessing.Value = "Importing CITY.XLSX file was sucessfully"

txtProcessing.Value = "Truncating City_Match_Temp_Truncate"

DoCmd.OpenQuery "City_Match_Temp_Truncate", acViewNormal, acEdit

txtProcessing.Value = "process City_renewal_Temp Query"

DoCmd.OpenQuery "City_renewal_Temp Query", acViewNormal, acEdit

DoCmd.OpenQuery "City Renewal Match Query", acViewNormal, acEdit

DoCmd.OpenQuery "City_Update_PERSON", acViewNormal, acEdit

DoCmd.OpenQuery "City_Update_Renewal", acViewNormal, acEdit

DoCmd.OpenQuery "City_DELETE_Cards_1", acViewNormal, acEdit

DoCmd.OpenQuery "City_DELETE_Cards_2", acViewNormal, acEdit

DoCmd.OpenQuery "City_INSERT_CARD", acViewNormal, acEdit

DoCmd.OpenReport "Labels City_renewal_Final", acViewReport, "", "", acNormal

DoCmd.OpenReport "Daily Renewal Report", acViewReport, "", "", acNormal

txtProcessing.Value = "Reports and Labels are done"

End Sub

Private Sub FormHeader_Open()

DoCmd.OpenForm Form_frmBadge

txtProcessing.Value = "Continental Testing Services Processing Chicago Badges"

End Sub

Private Sub Form_Open(Cancel As Integer)

txtProcessing.Value = "Continental Testing Services Processing Chicago Badges"

Dim strConn As String

Dim ADOConn As New ADODB.connection

Let Server_Name = "cts-LT01"

Let Database_Name = "CTS"

Dim MyFile As String

Dim MyPath As String

MyPath = "c:\Users\clowry\Desktop\TJ Folder"

MyFile = Dir(MyPath)

Do While MyFile <> ""

xlsProcessing.AddItem MyFile 

MyFile = Dir 

Loop

'ADOConn.ConnectionString = "ODBC;DRIVER={ODBC Driver 17 for SQL Server};SERVER=[CTS-LT01];DATABASE=[CTS_Working];Trusted_Connection=Yes;TrustServerCertificate=Yes;Description=MyApp"

'ADOConn.ConnectionString = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=[CTS-LT01];DATABASE=[CTS_Working];Trusted_Connection=Yes;TrustServerCertificate=Yes;Description=MyApp"

ADOConn.ConnectionString = "Driver={ODBC Driver 17 for SQL Server};Server=[CTS-LT01];Database=[CTS_Working];Trusted_Connection=yes;"

ADOConn.Open

MsgBox "Connect"

''Debug.Print ADOConn.State

End Sub

========================================

ADOConn.ConnectionString = "Driver={ODBC Driver 17 for SQL Server};Server=[CTS-LT01];Database=[CTS_Working];Trusted_Connection=yes;"

ADOConn.Open

=========================================

Answer 4

Does that mean the line which causes the error is the one between the two dashed lines? I ask because you didn't say one way or the other; perhaps you assumed we'd guess correctly from context.

Seriously, we love to help, but meeting us half-way makes for a smoother process.

Answer 5

I apologize for being snippy; it's a bit frustrating to have to ask for details and clarification.

Try it again, but remove the square brackets.

ADOConn.ConnectionString = "Driver={ODBC Driver 17 for SQL Server};Server=CTS-LT01;Database=CTS_Working;Trusted_Connection=yes;"

ADOConn.Open