Max Wanadoo
max.wanadoo at gmail.com
Thu Jan 14 12:42:24 CST 2010
R, Personally, I would create a unique index on those fields and let Access deal with the dupes - simple, easy and foolproof. Time is relative. I doubt if they are standing their watching the screen. Max -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin Sent: 14 January 2010 18:33 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 -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jurgen Welz Sent: Thursday, January 14, 2010 9:52 AM To: accessd at databaseadvisors.com Subject: Re: [AccessD] Fastest Way Using the queries is probably the quickest. Creating a five field index requires a lot of work to maintain. Joining all five fields to an intermediate query of the master table, perhaps matching the date range only, and using the result as the basis for a bulk delete against the import data and then appending the balance would be very fast. An 'unmatched records' query would definitely be slower. Ciao Jürgen Welz Edmonton, Alberta jwelz at hotmail.com > From: Lambert.Heenan at chartisinsurance.com > To: accessd at databaseadvisors.com > Date: Thu, 14 Jan 2010 12:05:11 -0500 > Subject: Re: [AccessD] Fastest Way > > > As they say, suck it and see. :-) > > I suspect that Query #3 will return the complete result set a lot faster than FindNext will take to locate them one at a time. So just throw the query together and see how long it take to run. > > 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 11:28 AM > To: 'Access Developers discussion and problem solving' > Subject: Re: [AccessD] Fastest Way > > Lambert: > > That looks like a good approach. Query on 2) would be interesting but I'm not sure I would need that information if I use the query in 3) to process just the incoming records that are not already present. I'm a little concerned about the time the Unmatched Query will take when there are 3-4,000,000 records in the table especially since the table in the back end is on a server. But it wouldn't take long to implement. > > Thanks > > Rocky _________________________________________________________________ -- 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