Finding name of last query run in VBA code

[ridders]

Hi

I have a procedure that runs a long list of append queries in turn.
As it occasionally fails due to e.g. a key violation, I would like to add
debug lines after each query to show that these ran successfully (or not)
This will help me finally sort out what the cause of the occasional failures
is...
For example, part of the code is:
'Transfer all student leaver Archived Student Attendance - after 30 days -
30/07/2008
DoCmd.OpenQuery "qryAppendLeaversArchivedStudentAttendance",
acViewNormal, acEdit
Debug.Print "qryAppendLeaversArchivedStudentAttendance" & " DONE"
'Transfer all student leaver Class Records for current year - after 30
days - 30/07/2008
DoCmd.OpenQuery "qryAppendLeaversClassRecords", acViewNormal, acEdit
Debug.Print "qryAppendLeaversClassRecords" & " DONE"

Rather than copy & paste the specific query name int each part of the code,
is there an easier way of doing this?

 

[Allen Browne]

No. There's not a way to retrieve the name like that.

If you are using OpenQuery to execute a series of action queries, I imagine
you have turned off SetWarnings. If so, you don't really know whether the
query did anything or failed or partially succeeded. Unless there is
something really wrong with the query (e.g. it got deleted), you won't be
notified if if failed because a validation rule was not met, or a related
record was required, or a unique index was violated, or numerous other
reasons.

A better solution would be to Execute the queries. Your code would then look
like this:
Dim db As DAO.Database
Dim strQry As String
Set db = CurrentDb()

strQry = "qryAppendLeaversArchivedStudentAttendance"
db.Execute strQry, dbFailOnError
Debug.Print db.RecordsAffected " record(s) in " & strQry & " at " &
Now()

This makes it easy to copy'n'paste the series of queries, gives you a better
trace of what happened, and notifies you of errors (trappable.) This also
opens up the possibility of wrapping the entire operation in a transaction,
so you can get an all-or-nothing result, rather than the inconsistency where
the first couple succeeded and the last one failed.

More info:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

 

[ridders]

Hi Allen

That's brilliant thanks - just what I needed
I've not used db.Execute before so that's something else I've learned

BTW you website is a great source of info for me
Am about to follow your advice on securing an Access 2003 db in 2007
If I have problems can I contact you direct?

 

[Allen Browne]

If I have problems can I contact you direct?

Please post any questions to the newsgroup.

We try to help, but can't help everyone individually.