Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Posts
    2

    Unanswered: Can I stack Update queries in SQL Window?

    Created an update query but wondered if I can stack them to make editin easier.
    I have some updates to do but instead of using build window, I thought I could stack them up, this way I could write out in notepad to see what I need first.
    It seems it should be very straightforward but I'm out of practice.
    UPDATE USERS SET USERS.ROLE="EXC"
    WHERE ((([Users]![Team]) Like "Executive"*));

    Then another one after that but I can't join with & or AND.
    Next one:
    UPDATE USERS SET USERS.ROLE="ABC"
    WHERE ((([Users]![Team])="Sales";

    If I take out the semi-colon acess doesn't like it or AND.
    Maybe this can only be done in VBA?
    Any advice appreciated.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Let's suppose that the SQL of each query is stored in a table: [Tbl_Queries] in a column: [SQL]. In VBA you can write a procedure such as:
    Code:
    Sub BatchQueries()
    
        Dim rst As DAO.Recordset
        
        Set rst = CurrentDb.OpenRecordset("Tbl_Queries", dbOpenSnapshot)
        With rst
            Do Until .EOF
                If Len(Nz(!SQL, "")) > 0 Then CurrentDb.Execute !SQL, dbFailOnError
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        
    End Sub
    If the queries are stored in a text file [Queries.txt] (one query/line), you can use:
    Code:
    Sub BatchQueries()
    
        Dim intHandle As Integer
        Dim strLine As String
        
        intHandle = FreeFile
        Open "Queries.txt" For Input As #intHandle
        Do Until EOF(intHandle)
            Line Input #intHandle, strLine
            If Len(Trim(strLine)) > 0 Then CurrentDb.Execute strLine, dbFailOnError
        Loop
        Close #intHandle
        
    End Sub
    Have a nice day!

Posting Permissions

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