Transferspreadsheet Trapping Error for Duplicate records

[sara]

I have a procedure to automate bringing several Excel files into our
Access tables, on a daily basis.

The problem is that if the user has a problem, and tries to run the
import again (maybe 3 files imported then there was a data problem and
they want to re-import after fixing the problem), I can't get the Error
handling to fire if the user is attempting to import duplicate key
records.

Message when I try to import records already on the table. There are
17 records on the .xls.

Microsoft Access was unable to append all the data to the table

The contents in the fields of 0 record(s) were deleted, and 17
record(s) were lost due to key violations.

Here is my code. I've tried trapping error 3022, 10014, 10018 and none
of them works.

I looked on prior posts, but didn't see the same problem. Thank you
very much.
Sara

Sub ImportData()
On Error GoTo ImportData_Err

SaleDate = Me.getSaleDate
strMsg = "Nothing Imported"
'------------------------------------------------------------
' POS10A - Store Sales T:StoreSalesData
'------------------------------------------------------------

strStepErrorMsg = "POS10A"
strImportFileName = Format([SaleDate], "mm-dd-yy") & " POS10A" &
".xls"

' MsgBox strImportFileName

DoCmd.TransferSpreadsheet acImport, 8, "T:Store Sales Data", _
"\\server-03\Building19\ProductionImports\" & strImportFileName,
True, ""

strMsg = "POS10A imported"

' 9 other import files here - all basically the same format
'------------------------------------------------------------
' MDYYMMDD.xls - Spags Tax Data
'------------------------------------------------------------

strImportFileName = "TX" & Format([SaleDate], "yymmdd") & ".xls"

' MsgBox strImportFileName

DoCmd.TransferSpreadsheet acImport, 8, "tblSpagsTaxInfo", _
"\\server-03\Building19\ProductionImports\" &
strImportFileName, True, ""

strMsg = "Spags Tax, " & strMsg

Importdata_Exit:
Exit Sub

ImportData_Err:
If Err.Number = 10014 Then
' If the problem is duplicate key, tell the user what was already
imported
' and the query or table that had the problem (**??? tried error
3022?)
MsgBox "Data was imported for " & strMsg
MsgBox "The problem file or query is " & strStepErrorMsg
Else
If Err.Number = 10018 Then
' If the problem is duplicate key, tell the user what was already
imported
' and the query or table that had the problem
MsgBox "Data was imported for " & strMsg
MsgBox "The problem file or query is " & strStepErrorMsg

Else
' If the problem is not dup key, display the error information
MsgBox "Data was imported for " & strMsg
MsgBox "The problem file or query is " & strStepErrorMsg
MsgBox Err.Number & " " & Err.Description & "Please call
Sara with this info."
End If
End If

Resume Importdata_Exit

End Sub

[Red]

I just want to reiterate my issues with the transferspreadsheet
method...

I hate using it, it's SO buggy.... and definately gives you errors (as
seen by Sara's problems with the above)

Now, Onto Sara...

There are two ways to 'error trap' this situation...

I will describe breifly how to do each..

1) Use the Transferspreadsheet methos to x-fer to a 'temp' table, and
then have a query add data that is not duplicated between the 2 tables

2) (The better way IMHO) Import progmatically, checking on each record.
Assuming you don't have thousands of lines in any of the spreadsheets,
it's really not a big deal to do it progrmatically...

I have started making a wiki on MrExcel.com on how to progmatically
transfer data to and from Excel/Access.. if you require more info, I
will point you in the right direction.

~Red

[sara]

I appreciate the thoughts, but I am not that sophisticated, so I'd
rather do the error trapping if I could.

Anyone else have ideas?

Thanks

[Linda Burnside]

Change the error handler so that it will show you the error number that
occurs when the user tries to add duplicate records:

msgbox err.Number & " " & err.Description

Then you'll know what error is occuring so you can trap it.

Linda

"sara" <sa*******@yahoo.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

I appreciate the thoughts, but I am not that sophisticated, so I'd
rather do the error trapping if I could.

Anyone else have ideas?

Thanks

[sara]

I do display the Error Number. The message I put in the post comes up
BEFORE it gets to error handling, so there is no number. If I say "NO"
to the message, I get 2501 - the Transferspreadsheet was cancelled. If
I say "Yes" the code just moves on, which isn't what I want.

Any ideas? Anyone?

Thanks

[Red]

LMAO... I think I just got what you are trying to do... heh...

kinda anyhow....

To get rid of the annoying "The contents in the fields of 0 record(s)
were deleted, and 17
record(s) were lost due to key violations. " message, use the command
"DoCmd.Setwarnings = False"

However you said:

If I say "NO"
to the message, I get 2501 - the Transferspreadsheet was cancelled. If
I say "Yes" the code just moves on, which isn't what I want.

So, what exactly DO you want it do after the message?

~Red

[sara]

I have been setting warnings to False (and then true later). However,
IF the user tries to import a day she already imported or (more likely)
corrected some data and wanted to import the NEW data, the message that
tells her there is already data for that date on the table is
suppressed, due to False.

I want to be able to trab the message that there are duplicate keys and
tell the user. The manager could then evaluate: Delete the "old"
records that they were trying to correct, or whatever.

We get our data emailed to us from a 3rd party every day, and, on
occasion, we have these problems. I just can't get the code to NOT
suppress that message - so I can deal with it myself.

Is this more clear? Any ideas?

Thank you -
Sara