Programming for query completeness

[wrightlife11]

I have a macro that runs about 10 update queries. Some work and other do
not. I have the warnings turned off because I do not want to see this
message "There isn't enough disk space or memory to undo the data changes
this action query is about to make. Do you want to run this action query
anyway?
For more information on freeing diskspace or freeing memory, search the
Windows Help index for 'disk space, freeing' or 'memory,troubleshooting'.
Yes or No buttons appear." These queries do not actually run although there
is no error either.

If I run them manually, they do just fine.

What can I do to ensure these update queries actual update the info?

I am using Access 2003.

Matthew

 

[Dale Fye]

I never have used macros much, primarily because of the inability to trap and
handle errors. I generally do this via VBA code, using the Execute method.
Something like:

Private Sub cmd_RunUpdates_Click

Dim db as dao.database
Dim strQueryName as string
Dim strErrMsg as string
On Error Goto RunUpdatesError

set db = currentdb

strQueryName = "qry1"
db.execute strQueryName, dbfailonerror
strQueryName = "qry2"
db.execute strQueryName, dbfailonerror
strQueryName = "qry3"
db.execute strQueryName, dbfailonerror

msgbox "Action completed"
Exit Sub

RunUpdatesError:
strErrMsg = "Error encountered running query: " & strQueryName _
& vbcrlf & Err.Number & ", " & Err.Description & vbcrlf _
& vbcrlf & "Select 'Abort' to terminate this action" _
& vbcrlf & "Select 'Retry' to retry this query" _
& vbcrlf & "Select 'Cancel' to move to the next query"

Select Case msgbox(strErrMsg, vbAbortRetryCancel)
Case vbAbort
Exit Sub
Case vbRetry
Resume
Case vbCancel
Resume next
End Select

End Sub

There really isn't much need to use the Retry option in this case (since it
would most likely generate the same error), but you can see how doing this in
code versus a macro gives you a wide variety of options for handling the
errors.

One of the things I do is use the value of Err.Number to handle specific
errors. Some can be ignored and return to the next line in the code using
Resume Next, others need to be handled. Either way, you see the flexibility
that this method affords you.

 

[dymondjack]

What can I do to ensure these update queries actual update the info?

As far as the real-world goes, there isn't much that you can do. The best
way to deal with making sure that queries do as they're supposed to through
automation is probably a two-step procedure. First, make sure your design
incorporates ever possibility you can think up for may not go as planned.
Not only in your sql code, but the preceding and post-code as well. Make
sure all of your ducks are in a row, so to speak, and have a way out if your
ducks are out of line by a little bit. This is all verification stuff that
can be done before running your query. (You may find vba easier than a macro
for handling this stuff).

As far as running the query and actually updating the data, unfortunately
there's no good way (that I'm aware of) to have access say "yes - everything
went as planned". Usually what I try to do here is see if I can
reverse-engineer the query (for lack of a better term). If there's a way to
automate it, there has to be a way to know what's being doing through code.
Keeping that in mind, if you know what's supposed to be done through code,
you can always check it afterword through code. Sometimes not very easily,
but it can be done. For example, if you are trying to move records 5 through
24 to table B, run a select query afterwords to verify that the records are
missing from the source table, and another one to verify that they are
present in the destiniation table.

Anyway, that's my take on verifying that an action query performed as it was
supposed to.

hth
--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill

 

[Chris O'C via AccessMonster.com]

You can guarantee all 10 queries run (or all 10 *don't* run) by using vba
code in a procedure. Use a workspace transaction (look it up in help for
example syntax). Execute each action query with the execute method and the
dbfailonerror argument:

db.execute "query1", dbfailonerror
db.execute "query2", dbfailonerror
' and the other queries
db.execute "query10", dbfailonerror

When the last query executes, commit the transaction. If any query fails,
the transaction never commits so you don't have to worry about some queries
failing and some not. By using the execute method, users don't see any
messages unless a query fails. Then they see your error handler's message.

Chris

 

[wrightlife11]

Many Thanks Chris! I will develop the code today.