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.
UPDATE USERS SET USERS.ROLE="ABC"
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.
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:
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Tbl_Queries", dbOpenSnapshot)
Do Until .EOF
If Len(Nz(!SQL, "")) > 0 Then CurrentDb.Execute !SQL, dbFailOnError
Set rst = Nothing
If the queries are stored in a text file [Queries.txt] (one query/line), you can use:
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