Excel VBA ADO Insanity

AskMyDog

New Member
Joined
Nov 13, 2015
Messages
16
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?

Code:
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

Board Regular
Joined
Feb 28, 2016
Messages
182
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

New Member
Joined
Nov 13, 2015
Messages
16
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

Board Regular
Joined
Feb 28, 2016
Messages
182
Can I ask, what is the reasoning behind using ADO to update what appears to be a "database" in an excel file?
 

AskMyDog

New Member
Joined
Nov 13, 2015
Messages
16
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?
 

Forum statistics

Threads
1,081,989
Messages
5,362,581
Members
400,683
Latest member
LogChief

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top