Excel VBA ADO Insanity

[AskMyDog]

Hi all,

I need help troubleshooting this code. When I run it, it errors out at the .Update saying [Microsoft][ODBC Excel Driver] Syntax error (missing operator) in query expression '(1stMedication=Pa_RaM011 AND 2ndmedication IS NULL AND Qualifiers IS NULL AND Route=Pa_RaM012..."

How do I fix this?

Sub ADODBUpdate()


Dim DB_Sheet As Worksheet, Med_DB As ListObject, Change_Table As ListObject, Route_Index As Long, Disp_Index As Long, Type_Index As Long, Inter_Index As Long
Dim Type_Arr As Variant, Inter_Arr As Variant, TypeSplit As Variant, InterSplit As Variant, i As Long, j As Long, k As Long
Dim TypeIndexRange As Range, TableLeft As Long, TableTop As Long, LastRow As Long, Isect As Range
Dim InterText As String, InterList As Variant, TypeText As String, item As Variant
Dim Gen1 As String, Gen2 As String, Qual As String, Route As String, TradeName As String, GenericName As String, MedType As String, Pronunciation As String, CommonUses As String, Interactions As String, Notes As String




Set DB_Sheet = Sheets("Updated Worksheet")
Set Med_DB = DB_Sheet.ListObjects("table9")
Set Change_Table = Sheets("DB Changes").ListObjects("table2")
With Med_DB
    Gen1_index = .ListColumns("1stMedication").index
    Gen2_index = .ListColumns("2ndMedication").index
    Qual_Index = .ListColumns("Qualifiers").index
    Route_Index = .ListColumns("Route").index
    Disp_Index = .ListColumns("DisplayName").index
    Trade_Index = .ListColumns("TradeName").index
    Gener_index = .ListColumns("GenericName").index
    Type_Index = .ListColumns("Type").index
    Inter_Index = .ListColumns("Interactions").index
    Pronun_Index = .ListColumns("Pronunciation").index
    Use_Index = .ListColumns("CommonUses").index
    Note_index = .ListColumns("Notes").index
    TableTop = .Range.Row
    TableLeft = .Range.Column
    LastRow = .Range.Rows.Count
End With




Dim importSQL As String, sConnect As String, DBPath As String
Dim Conn As Object, oRecordSet As Object


Set Conn = CreateObject("ADODB.Connection")
Set oRecordSet = CreateObject("ADODB.Recordset")


DBPath = ActiveWorkbook.Path & "\Medication Database.xlsm"


sConnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"




Conn.Open sConnect


With oRecordSet
'.CursorLocation = adDynamic
.CursorType = 1
.LockType = 3




    For i = 1 To Change_Table.ListColumns(Disp_Index).DataBodyRange.Rows.Count
        MedDisp = Change_Table.DataBodyRange(i, Disp_Index).Value
        If Not Change_Table.DataBodyRange(i, 1) = "" Then
            DispName = Change_Table.DataBodyRange(i, Disp_Index).Value
            Gen1 = Change_Table.DataBodyRange(i, Gen1_index).Value
            Gen2 = Change_Table.DataBodyRange(i, Gen2_index).Value
            Qualifiers = Change_Table.DataBodyRange(i, Qual_Index).Value
            Route = Change_Table.DataBodyRange(i, Route_Index).Value
            TradeName = Change_Table.DataBodyRange(i, Trade_Index).Value
            GenericName = Change_Table.DataBodyRange(i, Gener_index).Value
            MedType = Change_Table.DataBodyRange(i, Type_Index).Value
            Pronunciation = Change_Table.DataBodyRange(i, Pronun_Index).Value
            CommonUses = Change_Table.DataBodyRange(i, Use_Index).Value
            Interactions = Change_Table.DataBodyRange(i, Inter_Index).Value
            Notes = Change_Table.DataBodyRange(i, Note_index).Value
            importSQL = "Select * from [Medication Database$] WHERE [DisplayName] = '" & MedDisp & "'"
            .Open importSQL, Conn
        If (.BOF And .EOF) Then
            ' no match found; add new record
            .AddNew
            ![DisplayName] = "'" & DispName & "'"
            
            ![1stMedication] = "'" & Gen1 & "'"
            ![2ndMedication] = "'" & Gen2 & "'"
            ![Qualifiers] = "'" & Qual & "'"
            ![Route] = "'" & Route & "'"
            ![TradeName] = "'" & TradeName & "'"
            ![GenericName] = "'" & GenericName & "'"
            ![Type] = "'" & MedType & "'"
            ![Pronunciation] = "'" & Pronunciation & "'"
            ![CommonUses] = "'" & CommonUses & "'"
            ![Interactions] = "'" & Interactions & "'"
            ![Notes] = "'" & Notes & "'"
        Else
            ' matching record found; update it
            ![1stMedication] = "'" & Gen1 & "'"
            ![2ndMedication] = "'" & Gen2 & "'"
            ![Qualifiers] = "'" & Qual & "'"
            ![Route] = "'" & Route & "'"
            ![TradeName] = "'" & TradeName & "'"
            ![GenericName] = "'" & GenericName & "'"
            ![Type] = "'" & MedType & "'"
            ![Pronunciation] = "'" & Pronunciation & "'"
            ![CommonUses] = "'" & CommonUses & "'"
            ![Interactions] = "'" & Interactions & "'"
            ![Notes] = "'" & Notes & "'"
        End If
       .Update
       .Close
        End If
    Next




End With


Set rst = Nothing
         
Conn.Close


Change_Table.DataBodyRange.Clear


End Sub

Thanks!

 

[DaveBlakeMAAT]

Hi

Given that it fails when you try to update, I would look at your LockType. I only use ADO for read only, but that is probably the 1st thing I would try.

 

[AskMyDog]

Thanks for getting back to me. I tried every variation of the lock type, cursor type, without luck. The LockType in the macro I wrote is for adLockOptimistic. Before it was saying that the I figured out that using the constants like adReadOnly don't work, I assume because I didn't download the reference.

Any other thoughts?

 

[DaveBlakeMAAT]

Can I ask, what is the reasoning behind using ADO to update what appears to be a "database" in an excel file?

 

[AskMyDog]

Sure. I have absolutely zero experience when it comes to coding, and I needed a centralized database located in a shared location that would retain changes to the individual databases in the local copies users were accessing. Currently, both the workbook with my program and the database are in the same share drive on our intranet. This is the solution I came up with. Any better ideas?

Also, I was able to fix the code, I can post it later, but I could not for the life of me figure out how to add or update and entire row/entry in the database at once. Do you know how to do that?