VBA and queries - error checking

[diberlee]

Hi,

I have code that calls an append query and then a delete query as part of an import process. The queries were set up in the query design interface and are called by name in VBA. This works fine, but I wonder what options we have for error checking. I have turned off error message for this particular part of code as this database is used by non-technically minded people and they panic at the alerts about being about to permanently delete records etc...

My concern is that if the append query fails the next line of code calls the delete query which would then permanently remove all the data that I was trying to append. Does anyone have a suggestion of a better way to go about this?

Regards
Duane

 

[MarkK]

Does your code run the query in DAO or ADO? In DAO, errors are not raised to VBA by default. If you use the Execute method of the Database object, then you have a dbFailOnError option, which you'd invoke like . . .

currentdb.execute "DELETE FROM Table", [COLOR="Red"]dbFailOnError[/COLOR]

. . . and then you can handle DAO errors in VBA.

In addition, the DAO.DBEngine object maintains an Errors collection of DAO.Error objects, (not VBA.ErrObject objects) since some data operations can generate multiple errors (and the VBA.ErrObject is a singleton<-only allows a single instance, ever).

hth

 

[Mihail]

More, after using the .Execute method you can check how many records was affected:

CurrentDB.RecordsAffected

So, you can run a query to count how many records WILL be deleted;
Store this number into a variable;
Delete the records (using .Execute)
Compare the stored number with the affected records.

In addition with what lagbolt teach us about errors (Thank you !) should be safe enough.