+ Reply to Thread
Results 1 to 4 of 4

Help running SQL Update Via VBA (Missing Operator Debug Error)

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    06-18-2010
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    546

    Help running SQL Update Via VBA (Missing Operator Debug Error)

    I am running this code unsuccesfully I might add:
    Public Sub Updates()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim wb As Workbook
    Dim tblName As String
    Dim sDate As String
    Dim sql1 As String
    Dim sOpen As String
    Dim dbName() As Variant
    Dim q As Variant
    
    sDate = Format(Date, "ddd")
    
    tblName = "number_processed"
    dbName = Array("Jose", "Richard", "Mark")
    
    Set cn = New ADODB.Connection
    cn.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=R\Test1\" & dbName(q) & "\\" & dbName(q) & ".mdb;"
    For q = LBound(dbName) To UBound(dbName)
        sOpen = "Driver={Microsoft Access Driver (*.mdb)};Dbq=R:\Test1\" & dbName(q) & "\\" & dbName(q) & "t.mdb;"
        Call UpdateCode(tblName, CStr(dbName(q)), cn)
    Next q
    End Sub
    Public Sub UpdateCode(tblName As String, dbName As String, cn)
    sql1 = "Update " & dbName & "_yesterday INNER JOIN tbl)_Master ON " & dbName & "_yesterday.status_name = tbl_Master.statuscheck INNER JOIN " & tblName & " ON " & dbName & "_yesterday.ID = " & tblName & " ID"
    sql1 = sql1 & " SET " & tblName & " checked = 'yes'," & tblName & "checkedDate = Date() "
    cn.Execute sql1
    It doesn't like my update statment for some reason...can someone poitn out my errors?
    Last edited by jo15765; 05-21-2012 at 04:35 PM.

  2. #2
    Forum Contributor wallyeye's Avatar
    Join Date
    05-06-2011
    Location
    Arizona
    MS-Off Ver
    Office 2010, 2007
    Posts
    308

    Re: Help running SQL Update Via VBA (Missing Operator Debug Error)

    I don't think it will like:

    tbl)_Master

    but that seems to be too obvious of an error. Have you tried running the sql statement directly in your SQL editor in Access? Or better yet, copy the SQL string to the SQL window then switch to the QBE grid. That should give you some decent error messages.

  3. #3
    Forum Contributor
    Join Date
    06-18-2010
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    546

    Re: Help running SQL Update Via VBA (Missing Operator Debug Error)

    I caught the table)_master shortly after posting the message, that wasn't the issue Thanks for the tip with trying to run the code directly in SQL, I will try that and see what errors that will produce.

  4. #4
    Forum Contributor
    Join Date
    06-18-2010
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    546

    Re: Help running SQL Update Via VBA (Missing Operator Debug Error)

    I figured it out I had to modify my SQL to read like this:
    sql1 = "Update " & dbName & "_yesterday INNER JOIN (tbl_Master ON " & dbName & "_yesterday.status_name = tbl_Master.statuscheck) INNER JOIN (" & tblName & " ON " & dbName & "_yesterday.ID = " & tblName & " ID)"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1