Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Sep 2003
    Thanked 0 Times in 0 Posts

    Access Append Query Into SQL Server Table (Access 2000/SQL Server 7)

    I created a vanilla Access append query that appends 130,000 records into a SQL Server table. The SQL Server table is empty, contains 54 fields, a three field clustered primary key, and about 25 other field indexes. When I run the Access query (i.e., push the data to SQL Server) using an ODBC link to SQL Server, it takes 4 hours to complete. When I use the SQL Server DTS (i.e., pull the data from Access), it takes about two minutes to complete. What can I do to reduce the execution time of the Access query, and why does it take so long to execute?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: Access Append Query Into SQL Server Table (Access 2000/SQL Server 7)

    Running that large a query on an Access table would probably take hours as well. Jet and ODBC aren't really very good for that kind of data transfer. You would be better off creating a stored procedure in SQL Server and using that to move the data.

Posting Permissions

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