Results 1 to 2 of 2
  1. #1
    Star Lounger
    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)?



  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts