Rocky Smolin
rockysmolin at bchacc.com
Thu Jan 28 10:59:44 CST 2010
Thanks to all for your help on this. Since the 'brute force' method was already in place except for the addition of one field to the .FindFirst, I went the cheap route and told the client at the point where the time to import becomes unacceptable, I have a few good $olution$ in my back pocket. I'm thinking that for the short term, with few records in the target table, .FindFirst will work just fine (especially if the hospital upgrades his old 256MB XP machine which appears to be one of those old steam powered models). When he gets several hundred thousand it may be time for a different approach. But since all the fields in the .FindFirst are indexed, it may be OK for a long time. Best, Rocky Smolin Beach Access Software 858-259-4334 www.e-z-mrp.com www.bchacc.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond Sent: Sunday, January 17, 2010 4:47 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Fastest Way Rocky, Coming in late on this - I wouldn't recommend using a unique index for this (you would have to create a composite unique index on all of your columns which would in fact duplicate the size of your table and slow down imports). I would recommend an append query against an "unmatched query" as suggested originally by Lambert. But as opposed to Lambert I wouldn't execute the "unmatched query" against an *imported* spreadsheet but against a *linked* spreadsheet. If you import the spreadsheet you have to delete it after doing the appends and you then have to compress your database to prevent bloats. What I suggest is: 1. Link the spreadsheet to your database. 2. Create an append query like this: INSERT INTO YourTable(<Field1>,<Field2>,<FieldN>) SELECT YourLinkedSpreadsheet.Field1, YourLinkedSpreadsheet.Field2, YourLinkedSpreadsheet.Fieldn FROM YourLinkedSpreadsheet LEFT JOIN YourLinkedSpreadsheet ON YourLinkedSpreadsheet.Field1 = YourTable.Field1 AND YourLinkedSpreadsheet.Field2 = YourTable.Field2 AND YourLinkedSpreadsheet.FieldN = YourTable.FieldN WHERE YourTable.Field1 Is Null 3. Whenever you get a new Excel file then just overwrite the original Excel file: copy to the same folder using the same name as the original Excel file you have linked to the database. Asger -----Oprindelig meddelelse----- Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Rocky Smolin Sendt: 17. januar 2010 17:03 Til: 'Access Developers discussion and problem solving' Emne: Re: [AccessD] Fastest Way Lambert: I wouldn't care about the error message - I could tell the user to ignore it. But does this import assume that there is some indexing in the target table that I don't have already? TIA Rocky -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert Sent: Friday, January 15, 2010 6:17 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Fastest Way 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 -- 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