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
adoset("field") = DAOset("field")
adoset("stuff") = DAOset("stuff")
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?
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.