DAO and variables

[CALV]

Hi all,

Im currently trying to speed up my db, and Im stuck on a bit!

What I have is a table with 14 fields, on a network share, the table has a lot of records, and what Im trying to do, is drag over to a local table, via a query, the records I currently need, the query runs and shows the records between 2 dates, this bit is fine. I then want to store the results of this query in a local table which is basically a copy of the table on the network. I'm using DAO, and what I did was opened 2 recordsets, one for the query, one for the table on the network share, and did the following

I used rstnew and rst old as opened recordsets, then did:

totrecs = rstold.recordcount
for counter = 1 to totrecs
rstnew.fields("first field") =rstold.fields("first field")
rstnew.fields("second field") =rstold.fields("second field")
and so on up to 14th field
next counter

now this works fine, but I need to do this for several tables, is there a way to loop through all the fields in the table such as
for x = 1 to 14 ' nuber of fields
rstnew.fields(x) = rstold.fields(x)

etc, but somehow have it pick up the field name for x?

Hope that makes sense!

TIA

CALV

 

[dcx693]

My goodness. I'm assuming you're bringing this data to the local machine because the network connection is too slow? What is the backend of your database? I'm suprised that a field-by-field copy of the entire recordset is faster than querying it directly (or even copying the entire thing) but whatever works.

You can use rst.Fields.Count as the upper end of your loop like this:

For x = 1 to rstold.Fields.Count
  rstnew.fields(x) = rstold.fields(x)
Next x

 

[Mile-O]

Option Base 0

For x = 0 to rstold.Fields.Count - 1

 

[CALV]

Thanks!

dcx693, yes because the network is SLOW, so what I have done is have the user select between which dates they want furthur info, then run a query to get this info, then using the above code, transfer it from the query to a local table, I have no clue as to what better method to use, but this seems to work ok.

CALV

 

[pono1]

Calv,

Another option to consider: An action query is almost always faster than looping through a recordset.

INSERT INTO LocalTable
SELECT RemoteTable.*
FROM RemoteTable
WHERE HireDate < Now() - 30

Be sure to index the field in the criteria section of your query; otherwise, Access will drag all recs across the wire.

Regards,
Tim