Jim, this is a big question. Hopefully you are willing to spend some time
learning this, because it *really* makes a monster difference to what you
can do with a database.
At the most basic level, the VBA is:
DoCmd.OpenQuery "Query1"
If Query1 is a SELECT query, this shows the query to the user. If it is an
Action query (Delete, Update, or Make Table), it executes the query.
For an Action query, it is probably better to use:
DoCmd.RunSQL "Query1"
These queries ask for user confirmation before the action. If you wish to
avoid that:
DoCmd.SetWarnings False
before the query, and:
DoCmd.SetWarnings True
afterwards.
To run 2 queries, without confirmation, the code would be:
DoCmd.SetWarnings False
DoCmd.RunSQL "Query1"
DoCmd.RunSQL "Query2"
DoCmd.SetWarnings True
These approaches are essentially the same as the macro. You are using VBA,
but you have not gained any benefit. A major problem with turning off
SetWarnings is that you get no message if the action query failed. For
example, if you were deleting records from a table so you could use it
again, you have no idea whether all records were actually deleted, or if
someone was using them and some were not deleted at all. And you have no
idea how many records were deleted.
A better approach in VBA is to Execute the query:
dbEngine(0)(0).Execute "Query1", dbFailOnError
With this approach, you do not need to turn SetWarnings off. But if the
query does not complete successfully, it triggers a trappable error. Your
VBA code can now handle the error and exit gracefully instead of ignorantly
continuing with the assumption that the data is ready to use. If the idea of
error handling is new, see:
http://allenbrowne.com/ser-23a.html
After an Execute, you can see how many records were deleted/appended/updated
via the RecordsAffected property:
dbEngine(0)(0).Execute "Query1", dbFailOnError
MsgBox dbEngine(0)(0).RecordsAffected & " record(s)."
But the Execute also has a limitation that RunSQL does not. If your query
has a parameter or refers to a text box on a form like this:
[Forms].[Form1]![Text0]
the Expression Service (ES) in Access will interpret the reference and the
query will run. The Execute cannot do that, so you need to build up the SQL
statement in your code, concatenating the value from the form into the SQL
string. Example:
Dim strSql As String
strSql = "DELETE FROM Table1 WHERE ClientID = " & _
Forms!Form1!Text0 & ";"
Debug.Print strSql
dbEngine(0)(0).Execute strSql, dbFailOnError
The Debug.Print line above is optional. It dumps the SQL string to the
Immediate Window. After running the code, you can open the Immediate Window
(Ctrl+G) to see the statement that executed. If there is a problem with the
SQL statement, the execute will fail, but you will be able to see what it
tried to execute, which empowers you to get it right.
When you concatenate values into a SQL string like that, you need delimiters
around the values. For dates, the delimiter is #, e.g.:
strSql = "DELETE FROM Table1 WHERE EntryDate = #" & _
Forms!Form1!Text0 & "#;"
For Text type fields, the delimiter is the double-quote. Trouble with that
is that VBA is already using quotes to delimit the string. The convention is
to double-them up when they are embedded in a string. So, if you want:
This string has a "word" in quotes
you code:
"This string has a ""word"" in quotes"
It looks a bit odd when the string ends with this word, since you get 3
quotes together:
"This string has a ""word"""
but that's the kind of thing you generally get:
strSql = "DELETE FROM Table1 WHERE City = """ & _
Forms!Form1!Text0 & """;"
So, you will certainly mock up a query using any sample critiera values,
switch it to SQL View, and copy the statement to use in your code. But you
still need to concatenate your variables into the string.
These SQL statements can be quite long, so you probably want to break them
into multiple lines like the Query window does as well. Effectively this is:
strSql = "SELECT * FROM Table1 " & "WHERE ID = 99;"
Use the underscore as the line continuation charcacter:
strSql = "SELECT * FROM Table1 " & _
"WHERE ID = 99;"
There's more power beyond that as well, such as wrapping multiple statements
in a transaction so you roll the whole thing back unless it all completes
successfully. There's more info on that here:
http://allenbrowne.com/ser-37.html
But I fear we have already given you indigestion.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Jim Jones said:
I am curently using a number of macros each of which runs a large number of
queries. I am told that this is not good practice, and that I should
write
VBA code which executes the SQL code for each of the queries in turn. So,
the VBA would do the same job as the macro, but in a single piece of code.
I
see myself designing the query in the Query Window, then pasting the
corresponding SQL code at the appropriate place in the VBA.
Can anyone suggest how a VBA novice like me could start experimenting with
SQL in VBA code. Perhaps, someone could show me how to write some VBA
code
which does the same job as a very simple macro (say, one which calls two
queries).
I hope this makes sense
Jim Jones
Botswana
