Build and Execute Multiple Update Queries

[wadev1]

I am looking for some code that would allow be to build and execute multiple
Update queries using vba. Below is the sample of the actual update query.
The table name is ADDRESS, and field names are STREETNAME and ZIP, the Update
Query is calling on a function.

UPDATE ADDRESS SET ADDRESS.STREETNAME = RemoveSpaces([STREETNAME]),
ADDRESS.ZIP = RemoveSpaces([ZIP]);

I would also like to know the proper syntax to add multiple update queries
to the code, I will be calling on several functions to update these two
fields. Thanks in Advance!!

 

[Tim Ferguson]

I am looking for some code that would allow be to build and execute
multiple Update queries using vba. Below is the sample of the actual
update query. The table name is ADDRESS, and field names are
STREETNAME and ZIP, the Update Query is calling on a function.

UPDATE ADDRESS
SET STREETNAME = RemoveSpaces([STREETNAME]),
ADDRESS.ZIP = RemoveSpaces([ZIP]);

There does not seem to be much wrong with this. Since RemoveSpaces is (I
assume) a vba function in a code module, you have to run this from the
GUI, using a querydef, or the DoCmd.RunSQL method. Using db.Execute
bypasses the query interpreter and therefore the engine cannot see the
vba reference.

I would also like to know the proper syntax to add multiple update
queries to the code, I will be calling on several functions to update
these two fields.

I am not quite sure what you mean by this. You can build a SQL command
just like any other string:

strSQL = "UPDATE MyTable " & _
"SET " & strFieldName & " = " & Quoted(strNewValue) & ", " & _
" " & strOtherField & " = " & SQLDate( Now() ) & " " & _
"WHERE " & strFilterFieldName & " = TRUE "

debug.Print strSQL

so you can poke anything in you like that makes sense to the SQL
interpreter. Do remember to print out or MsgBox the sql until you are
really used to getting the delimiters etc. right -- and even after!

Hope that helps


Tim F