Results 1 to 2 of 2
2007-02-17, 09:57 #1
- Join Date
- Aug 2003
- Brisbane, Queensland, Australia
- Thanked 0 Times in 0 Posts
Access / SQL Server performance (Access2003/SQL 2005)
I am using an Access Project (Office 2003 but Access 2000 format) to connect to a SQL Server database (2005). I am moving records from one table to another in VBA and before I add each one I look to see if it is already there (the original table has multiples of each record but my target table will have only 1 of each) and if it is I just modify one of the target fields.
SQL Server does not support SEEK so I am using the FIND to see if the record is already there. But to make the FIND work correctly I have to precede each FIND with a MOVEFIRST. This one action slows the lookup down almost to a stop. Is there a faster alternative (the difference is about 15 minutes to 10 hours - I have 100000 records in the source)?
2007-02-17, 10:48 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: Access / SQL Server performance (Access2003/SQL 2005)
I'd try to do it all in SQL or in T-SQL, so that it can be run on SQL Server.