Saving Action Query Warnings

[StatBoy]

I'm working on automation for a bulk data import function that will be used
repeatedly. I'd like to find a way to SAVE the warnings that Access often
displays during execution of an "append" query. These would be useful
history for the user to review and it would be very convenient to simply
stash them in rows of a table characterizing the overall data import
operation.

Say for instance I'm trying to append 2000 records. And Access in response
displays a message indicating 12 key violations, and 20 validation rule
violations. I'd like to be able to store that message for more detailed
examination by the user at a later time.

Can't seem to find a direct, straightforward way to do this. I've
investigated VBA approaches using "Connection.Execute strSQL..." and
"CurrentDB.Execute strSQL...", with subsequent VBA examination of the
"Errors" collection. However, at present it seems that these approaches just
stop the import operation ENTIRELY as soon as any SINGLE error arises. That
doesn't really match my needs here. If 1950 out of 2000 records CAN be
loaded, I want that to be done... so long as I have a record of what I need
to check about the records that did NOT load.

I'm hoping there is a way to do this short of constructing a VBA function
that submits individual SQL "insert" commands for each individual incoming
record...

It also seems to me that SOMEONE out there must have done something like
this already...

Can anyone offer sage experience and suggestions?

Thanks much.

 

[Klatuu]

If you need to do this, you will be reduced to a one at a time situation.
Bulk imports and exports are just one command and there is no facility to
exclude records that can't be imported.

There is one other option, but whether it would work for you would depend on
the nature of the data you are importing. What format is the data coming in
in? Excel, text file, external database, etc?

 

[Tony Toews [MVP]]

I'm working on automation for a bulk data import function that will be used
repeatedly. I'd like to find a way to SAVE the warnings that Access often
displays during execution of an "append" query. These would be useful
history for the user to review and it would be very convenient to simply
stash them in rows of a table characterizing the overall data import
operation.

Say for instance I'm trying to append 2000 records. And Access in response
displays a message indicating 12 key violations, and 20 validation rule
violations. I'd like to be able to store that message for more detailed
examination by the user at a later time.

Can't seem to find a direct, straightforward way to do this. I've
investigated VBA approaches using "Connection.Execute strSQL..." and
"CurrentDB.Execute strSQL...", with subsequent VBA examination of the
"Errors" collection. However, at present it seems that these approaches just
stop the import operation ENTIRELY as soon as any SINGLE error arises. That
doesn't really match my needs here. If 1950 out of 2000 records CAN be
loaded, I want that to be done... so long as I have a record of what I need
to check about the records that did NOT load.

I import data into temporary tables. Then I run various queries
against the temporary and permanent data which locate all such errors
such as duplicate key violations, etc, etc. If the data is coming
from an Excel spreadsheet I will even store the row number in the temp
table and display those on a form. Then the user can clean up the
data in either the Excel spreadsheet or the temp tables until it's
clean.

Then it imports cleanly.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/