Strange delay...

[Chatbox]

I have two tables (T1 and T2, say). I update records in T1 using recordset (ADO). Then (immediately) I do a "INSERT INTO T2 SELECT....blah" query (ADOdb.Command), basically copying the updated records from T1 to T2.

However...There seems to be a delay between "After the recordset update statement is finished" and "The new data showing up in the SELECT section of the INSERT query"....

I seem to have to "pause/wait" a few seconds before the new data is showing up. Why is that?

(database is just an Access MDB file).

Any help would be great!

 

[Mile-O]

I have two tables (T1 and T2, say). I update records in T1 using recordset (ADO). Then (immediately) I do a "INSERT INTO T2 SELECT....blah" query (ADOdb.Command), basically copying the updated records from T1 to T2.

Why are you storing the same data in two different tables?

I seem to have to "pause/wait" a few seconds before the new data is showing up. Why is that?

The more data you are inserting the longer it takes - it isn't an instantaneous action.

 

[Chatbox]

Why are you storing the same data in two different tables?


The more data you are inserting the longer it takes - it isn't an instantaneous action.

T1 holds the current (up to date data), T2 holds all the data that ever existed...including the newly changed/updated ones.

It's not the insert that takes time, it's the Update (or more specifically, the rows that got updated) that takes time to show up.

 

[The_Doc_Man]

Typically, your problems with delays fall into two categories.

First, if the DB is a back end (particularly if it is an SQL backend and Access is front-end only), queries can be triggered on the backend server and the FE gets released as soon as the backend query is STARTED, not FINISHED.

Second, if the DB is pure Access, the problem in queries is that if they are open at the time, they don't instantly refresh. Its a Tools >> Options thing, I forget exactly which tab of the multi-tabbed final dialog box. The keyword here is Refresh.

A query works by making a list of records that match it. The list is STATIC once opened until either you or Access refreshes the list.

 

[Chatbox]

Hum....so what exactly do I need to refresh here? I'll go to Options and see what I can find...

Both queries are run by (and inside of) some VBA codes.

 

[Pat Hartman]

Since you have the T1 record in memory, why not just do exactly the same thing to T2 with ADO that you did to T1?

 

[Chatbox]

Since you have the T1 record in memory, why not just do exactly the same thing to T2 with ADO that you did to T1?

I'd love to know how to do that....inserting to a "different" table (different from the original source, T1) with the same record set.

Any links would be extremely appreciated.

 

[Pat Hartman]

look up AddNew in ADO help. You should find code samples.