Error messages when Access queries fail.

[The.Daryl.Lu]

Hey,

I'm trying to program a few queries in MS Access using VB. For my
insert query, I'm importing an Excel spreadsheet using the
'transferspreadsheet' function of VB. Now, if I try to insert two
rows with duplicate primary keys, the program only inserts one of the
rows. How do I show a message box that alerts me that ONE row was not
inserted? So, this message box would be dynamic in the sense that it
would ONLY appear when rows are NOT inserted and when it does appear,
it would state the number of rows that were not inserted.

As a bonus question, is it possible to state after the information box
perhaps a subform that would show what rows did not insert and why
(I'm guessing in another field in a table of sorts.

Much Appreciated,
Daryl

 

[Andy Hull]

Hi

I would import into an intermediate table with the same structure that
allowed the duplicates AND has its own autonumber primary key.

Then, this table can be checked for duplicates and you can store them in an
"errors" table and continue to insert the OK rows into the final table.

The reason for the autonumber primary key in the intermediate table is so
you can handle 2 otherwise identical rows separately if necessary - perhaps
to insert one into the final table and one into the error table.

Regards

Andy Hull

 

[Klatuu]

How are you doing the insert?
If you are using the Execute method, it is necessary to use the
dbFailOnError parameter for any error to be reported to Access because the
Execute does not go through the Access UI. In any case, you will have to
trap the error in your error handler.

One way to check for duplicate primary keys prior to performing the insert
would be to run a check for duplicates query on the imported table. You can
set one up with the query wizard.

 

[The.Daryl.Lu]

Hey,

I'm trying to program a fewqueriesin MSAccessusing VB. For my
insert query, I'm importing an Excel spreadsheet using the
'transferspreadsheet' function of VB. Now, if I try to insert two
rows with duplicate primary keys, the program only inserts one of the
rows. How do I show a message box that alerts me that ONE row was not
inserted? So, this message box would be dynamic in the sense that it
would ONLY appear when rows are NOT inserted and when it does appear,
it would state the number of rows that were not inserted.

As a bonus question, is it possible to state after the information box
perhaps a subform that would show what rows did not insert and why
(I'm guessing in another field in a table of sorts.

Much Appreciated,
Daryl

Currently, just using the 'transferspreadsheet' function of Access.
I'm pretty new at this so I wrote up the macro using the Design
Wizard. I figure as I progress and learn, I'll program straight VB
code.

Andy, how do you suggest checking the duplicates of the intermediate
query? Where [field 1]=[field 1] of two (or more) records are the
same? I was thinking about the intermediate table but was unsure
exactly of the write-up. Can this be done in the macro Design Wizard
or best to write in the VB Editor?

Thanks, Daryl