Modifying the "Key Violation" Error

[Amanda Payton]

I'm in the process of developing a program for my company that updates it's
records from a CSV file every morning. Unfortunately - due to someone being
asleep at the switch elsewhere in my company, I occasionally have records
with duplicate primary key values in that file. Of course - that means that
the first person to open the program each morning sees the ever-so-lovely
pop-up box "Access could not add (x) records due to a key violation". Which
is great - but not all that informative to the non-programmer.

I know just enough about error handling to be dangerous (that's the current
"zone" my learning curve is in right now). What I would like is for that
box to tell the user the value of the primary key at the point the error was
generated - and possibly drive an e-mail to the system admin to make sure the
duplicate is weeded out. (e-mail is optional... so long as the offending
record ID is easily identified)

I'm figuring out how to handle errors in my custom functions, but I'm a bit
thrown off by those inside intrinsic functions (docmd.transfertext), so I
don't know if it's possible for me to override the intrinsic error handling.

Thank you in advance for helping me reduce the stress-volume in my office!

Amanda

 

[Randy]

I'm in the process of developing a program for my company that updates
it's
records from a CSV file every morning. Unfortunately - due to someone
being
asleep at the switch elsewhere in my company, I occasionally have records
with duplicate primary key values in that file. Of course - that means
that
the first person to open the program each morning sees the ever-so-lovely
pop-up box "Access could not add (x) records due to a key violation".
Which
is great - but not all that informative to the non-programmer.

I know just enough about error handling to be dangerous (that's the
current
"zone" my learning curve is in right now). What I would like is for that
box to tell the user the value of the primary key at the point the error
was
generated - and possibly drive an e-mail to the system admin to make sure
the
duplicate is weeded out. (e-mail is optional... so long as the offending
record ID is easily identified)

I'm figuring out how to handle errors in my custom functions, but I'm a
bit
thrown off by those inside intrinsic functions (docmd.transfertext), so I
don't know if it's possible for me to override the intrinsic error
handling.

Thank you in advance for helping me reduce the stress-volume in my office!

Amanda

Amanda, as you said, you have to use VBA (code) to customize the data
importing and/or its messages using the "DoCmd.TransferText" function. In
your function do error trapping and have displayed your own message.

-Randy

 

[Amanda Payton]

Randy -

Unfortunately - that tells me nothing. Short of figuring out how the
transfertext method works and writing my own, I don't see how to change what
that box says. The function that I've written calls the transfertext method.
The only time MY error handling comes into play is if I hit cancel when the
transfertext error notification pops up... the box I want to change is
controlled completely from within the transfertext method - and I don't know
how to pass something to the transfertext method to override that (or even if
it's possible)

I don't think you quite understood what I was/am asking. Thanks for trying
though.

Amanda

 

[Randy]

Randy -

Unfortunately - that tells me nothing. Short of figuring out how the
transfertext method works and writing my own, I don't see how to change
what
that box says. The function that I've written calls the transfertext
method.
The only time MY error handling comes into play is if I hit cancel when
the
transfertext error notification pops up... the box I want to change is
controlled completely from within the transfertext method - and I don't
know
how to pass something to the transfertext method to override that (or even
if
it's possible)

I don't think you quite understood what I was/am asking. Thanks for
trying
though.

Amanda

Amanda,

After looking into it, I would like first to acknowledge some facts:

The TransferText method does not notify details about the record(s) that
could not be imported due to key violation rules. The function only reports
the event itself and the number of records causing the violation.

The TransferText method does not provide a way to retrieve more detailed
information about these records by error trapping this event.

The Import Text Wizard behaves the same way, and it cannot be interacted
moreover, since it surprisingly uses the same TransferText method, as I
analyzed.

So to know which record(s) Access cannot append to a table when importing a
CSV file, you will need to work-around by other ways involving more steps.

Let me describe my suggestion:

Import the text file into a temporary table. Then run a query to find out
the items from the temp table that are present on the append table. The
results will be the duplicates that will cause trouble on the final append,
so analyze, document and delete them (or delete them from the original text
file and re-import to temp again). Then run an append-query from temp to the
append table. Finally delete or purge the records on the temp table.

CODING RECOMMENDATIONS:

The temp table should have the same columns and data types as the append
table. To create an static temp table just copy the append table by
selecting it and clicking on Edit->Copy, and then paste it (Edit->Paste).
When prompt, specify that you want to copy the table structure only. Type
the name of the temp table and click on OK.

To import the file to the temp table you can use the Text Import Wizard.
Alternatively you can use the TransferText method (to avoid less user
interaction). Use it as follow:

DoCmd.TransferText acImportDelim, "YourFile Import Specification",
"AppendTable", "C:\YourFile.csv"

You will need to create a data-import-specification, which contains
information on how to read the fields from the file to the target (append)
table. You can create a data-specification by running the Text Import
Wizard. Run the wizard to end, and before clicking on the Finish button
click on the Advanced button, and click on "Save As." This specification is
saved within the database and you will need to refer to it when using the
DoCmd.TransferText method (the second parameter from above, next one after
acImportDelim).

A query that finds items from the temp table that are present on the append
table should look as follow:

SELECT [TempTable].*
FROM [TempTable] INNER JOIN [AppendTable]
ON [TempTable].ID = [AppendTable].ID

To append the records from temp to the append table use an append-query,
that should look like this:

INSERT INTO [AppendTable]
SELECT [TempTable].*
FROM [TempTable]

Records on the temp table should be purged prior importing the text file,
and also purged after the final appending for safety. To delete all the
record from the temp table you can use a delete-query, that may look like
this:

DELETE [TempTable].* FROM [TempTable]


ADDITIONAL RECOMMENDATIONS:

You can review and delete duplicated records manually from the queries'
output. You can create an additional query to delete these duplicates
without reviewing them or after doing so.

You may want to add another step to check for duplicates also within the
same text file or temp table. In that case, make sure the temp table do not
have key validation. To report and delete the dups, use a Find Duplicates
query on the temp table, that will do the job.

You can use code, forms and modules to glue all this together. If you do,
you may want to use the SetWarning method (DoCmd.SetWarnings [True|False])
to turn off the pop-up messages asking to confirm the operations for
deleting and appending records, messages that might result annoying once
after your project has been thoroughly analyzed, ran, and debugged.

-Randy