Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002
    Location
    Sydney
    Posts
    21

    Unanswered: Fastest method of copying a recordset

    Hi all.
    I am querying a database server in ADO, and copying the results to a temporary table in Access (and then will perform a lot of queries on this tmp recordset). What's the fastest method for doing this? I tried walking through the ADO recordset and using separate 'insert into' statements, but that's pretty slow.
    I am now trying walking through the ADO recordset and copying the values into a DAO recordset, eg:

    do while not ADOset.eof
    DAOset.addnew
    adoset("field") = DAOset("field")
    adoset("stuff") = DAOset("stuff")
    ADOset.movenext
    loop
    DAOset.batchupdate

    By the way, I haven't tried the above code, so it probably has problems, but anyway, you get the idea...

    Is this the fastest way or doing this, or is there some magic copy method that will work between the 2 recordsets?

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Are you trying to copy a table ? What db to db are you doing this ? When you say "a lot of queries" - what are you trying to do - queries are much different than inserts/deletes/updates ...

  3. #3
    Join Date
    Feb 2002
    Location
    Sydney
    Posts
    21
    I'm selecting a small number of applicable records from a very large table in SQL Server (soon to be MySQL - which is why I'm using ADO). The results are being used in queries linked to another database (in MySQL).

    So the source is SQL Server and the destination is the current MS Access database, into a temporary table.

    These records are then broken down further by their product code - we have 4: E, L, G, and T. After the initial tmp table is created, this is used in another query (in MySQL) which brings in descriptions of various ID fields in the query. I then have 4 tabs which hold the results; one for each product code.

    I used to be able to run the whole thing in one go without a temporary table in-between, but it's just getting too slow now. Access seems to requery the whole damned thing 4 times instead of caching the results of the query and simply linking on the appropriate product code.

    It's a bit messy currently, but hopefully I will have the whole thing ported to MySQL soon.

Posting Permissions

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