Suppressing messages, but logging them too.

[Jack]

Hello,

I'm using Access 2002. I'm running several queries via a macro. In the
macro, I'm turning off warning messages and turning them back on when the
macro has run it's last query. However, I do not want to lose certain
messages, such as when a certain number of records cannot be inserted due to
key violations, and would like to log them in an error log. Is there anyway
to capture those? I've considered executing them from either a database or
connection object but those objects don't seem to store the message. Do I
have any options?

Jack

 

[Jeff Boyce]

Jack

Have you already considered using a few more of the Warnings Off/On pairs.
You could turn them off to start, back on for the query you wish details on,
back off after, and so on...

 

[Jack]

Thanks for the reply, I appreciate it. The process I need to log messages for
is one that is run with new data over and over again and I can't predict
which queries may result in significant messages that I need to log. So the
ideal situation is for me to run a query, see if a significant message, like
violating the primary key, was (or would have been) displayed, and log it to
a file. It's a long process so I'd rather log the message instead of the user
being there to click the OK button.

Jack

 

[John Vinson]

Thanks for the reply, I appreciate it. The process I need to log messages for
is one that is run with new data over and over again and I can't predict
which queries may result in significant messages that I need to log. So the
ideal situation is for me to run a query, see if a significant message, like
violating the primary key, was (or would have been) displayed, and log it to
a file. It's a long process so I'd rather log the message instead of the user
being there to click the OK button.

I'd suggest that you use VBA to run the queries, using the querydef
Execute method; and trap (and log) the errors. It'll be a bit more
work at the start but it's the only way I can think of to accomplish
this. While you're at it, you might want to create a small table with
two fields - the query name and an order of execution, and perhaps
even a yes/no field indicating which queries should be executed.

Your code could be something like:

Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim rsRun As DAO.Recordset
Dim strRun As String
On Error GoTo Proc_Error
Set db = CurrentDb
strRun = "SELECT QName FROM QueryList WHERE RunThis ORDER BY Seq"
Set rsRun = db.OpenRecordset(strRun)
Do Until rsRun.EOF
Set qd = db.Querydefs(rsRun!Qname)
qd.Execute dbFailOnError
rsRun.MoveNext
Loop
Proc_Exit:
Exit Sub
Proc_Error:
Select Case Err.Number
Case ...
<detect and log the errors you want to trap>
Resume Next
Case Else
MsgBox "Unhandled error " & Err.Number & vbCrLf _
& Err.Description
Resume Proc_Exit
End Select


John W. Vinson[MVP]