Results 1 to 3 of 3
  1. #1
    Join Date
    May 2006

    Unanswered: How can I run a batch of update queries?

    Hi everyone,

    I have been normalising a database that I have inherited and need to populate a field in my main table with unique identifiers that are the primary key in a linked table. I would like to run

    UPDATE maintable SET idfield=1 WHERE start_date="1980" AND end_date="1990"
    UPDATE maintable SET idfield=2 WHERE start_date="1981" AND end_date="2000"
    uPDATE maintable SET idfield=3 WHERE start_date="1982" AND end_date="1995"

    there are a coupl of thousand of these update queries.

    I have created all the queires, I just need to find a way of running them all together as a batch rather than running them one by one in the SQL query view. Does anyone have any ideas?

    My VB knowledge isn't vast so if the solution involves VB I will need to know the basics as well as the main commands.

    Thank you!

  2. #2
    Join Date
    May 2006


    Hi everyone,

    Have found an answer on a Google group
    so thought I would post it in case anyone else has the same trouble.

    Sub cmdButton_Click()
    On Error Goto cmdButton_Click_Err
    Dim wrk As Workspace, db As Database
    Set wrk = DbEngine(0)
    Set db = wrk(0)


    db.Execute "your sql here"
    db.Execute "or you can even"
    db.Execute "specify the"
    db.Execute "name of"
    db.Execute "a saved"
    db.Execute "query here"
    db.Execute "as it's compiled"


    On Error Resume Next
    ' This'll roll back only if code didn't get as far
    ' as the .CommitTrans otherwise an error occurs but
    ' will be ignored due to the preceeding On Error statement

    ' Clean up code
    Set db = Nothing
    set wrk = nothing
    Exit Sub

    Select Case Err
    ' handle specific errors here
    Case Else
    MsgBox Error, 16,"Error #" & Err In cmdButton_Click()"
    End Select
    Resume cmdButton_Click_Exit
    End Sub

  3. #3
    Join Date
    Apr 2006
    Huddersfield, UK
    You could also have created a macro to run each query! Then had this macro load onload() or at the click of a button;

    sometimes simple is best.... and i'm just a simple fellow.

Posting Permissions

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