Heenan, Lambert
Lambert.Heenan at chartisinsurance.com
Fri Jan 15 08:17:09 CST 2010
Well at least in Access 2002 when you do this it is true, the unique data does get imported to the destination table, but the error handler does not get invoked. Here is some code I just tried out... Sub Importit() 10 On Error GoTo Importit_Error 20 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "MyTestTable", "H:\My documents\Access\Access 2002\test\MyTestData.xls", True, "MyTestNamedRange" 30 On Error GoTo 0 40 Exit Sub Importit_Error: 50 Debug.Print Err, Err.Description, Erl 60 Resume Next End Sub The TransferSpreadsheet action runs, and imports all the unique data. Then at the end it issues an error that is not trappable by VBA... "Microsoft Access was unable to append all the data to the table. The contents of fields in 0 record(s) were deleted, and 409 record(s) were lost due to key violations." etc. etc. Does anyone know how to trap the JET error? (I assume JET is issuing the error message.) Lambert -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo Sent: Friday, January 15, 2010 2:45 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Fastest Way I don't understand what you mean by brute force or findfirst. Just create the unique index on the 5 fields on your existing table. You only need do this once. Then import using transfer method with on error routine in place. If errors out on 3024 (or whatever the dupe record err No. is - this is air code) Then just resume next That's it. No findfirst or anything. The transfer method will import each record. If a dupe exists it will go to the error routine where it will be told to resume the input. Max -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin Sent: 14 January 2010 23:56 To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Fastest Way That was my first thought - brute force - time is not that critical. And it would only be a couple of lines of code additional in the current import routine - just a .FindFirst. If later it turns out that the time is unacceptable, I could bail to the other approach. Still thinking.... But with the brute force method I could quickly set up a test case. I already put start time, end time, and elapsed time test boxes on the form. Rocky -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo Sent: Thursday, January 14, 2010 1:36 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Fastest Way It's a done deal. If you want speed and efficiency, then go my way. Unique index on relevant fields. Let access handle the dupes. One read-One input-Finito. Max -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert Sent: 14 January 2010 21:22 To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Fastest Way I think we'd all be interested to know what method you eventually use, and how the performance is. Lambert -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin Sent: Thursday, January 14, 2010 1:33 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Fastest Way Actually don't want to delete and import - just bypass the incoming records that are already in the table. R -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com