Timing Issues

[mcescher]

Hi All,

I'm running into a weird problem, and hoping someone can help.

Using ADO, I'm appending part of Table1 into Table2. Table2 needs to
be empty before I start or else I get multiple copies of the records.
If I do a delete query (ADO) on Table2 first, and then do the append
part. The delete code hasn't finished, so it blows away the records
that I just appended also. I end up with no records in Table2

In testing, I placed some msgbox lines which slowed down the process.
Then it ran fine, but I don't need msgboxes there.

HELP!!

Thanks for your time,
Chris M.

 

[RoyVidar]

Hi All,

I'm running into a weird problem, and hoping someone can help.

Using ADO, I'm appending part of Table1 into Table2. Table2 needs to
be empty before I start or else I get multiple copies of the records.
If I do a delete query (ADO) on Table2 first, and then do the append
part. The delete code hasn't finished, so it blows away the records
that I just appended also. I end up with no records in Table2

In testing, I placed some msgbox lines which slowed down the process.
Then it ran fine, but I don't need msgboxes there.

HELP!!

Thanks for your time,
Chris M.

Hard commenting without seing any code, but first suggestion is to try
to execute it all on the same connection, i e

dim cn as adodb.connection
set cn = currentproject.connection ' or other connection
cn.execute "delete form table2", , adcmdtext+adexecutenorecords
cn.execute "insert into table2 select * from table1 where ..." _
, , adcmdtext+adexecutenorecords

 

[Guest]

Try adding this to your code:

Dim PauseTime, Start, Finish

PauseTime = 10
Start = Timer
Do While Timer < Start + PauseTime
Loop

Where PauseTime = seconds

This should pasue your routine long enough for everything to catch up.

Let me know if this works for you.

 

[RoyVidar]

Hard commenting without seing any code, but first suggestion is to
try
to execute it all on the same connection, i e

dim cn as adodb.connection
set cn = currentproject.connection ' or other connection
cn.execute "delete form table2", , adcmdtext+adexecutenorecords
cn.execute "insert into table2 select * from table1 where ..." _
, , adcmdtext+adexecutenorecords

If you need two connections, or wish more info, check out
http://support.microsoft.com/kb/200300

 

[mcescher]

Thanks for your help. I actually was able to fix the problem by
changing the connection string parameter from adOpenDynamic to
adOpenStatic

Thanks again for the help.

Chris M.

Thanks for your time,
Chris M.

Hard commenting without seing any code, but first suggestion is to try
to execute it all on the same connection, i e

dim cn as adodb.connection
set cn = currentproject.connection ' or other connection
cn.execute "delete form table2", , adcmdtext+adexecutenorecords
cn.execute "insert into table2 select * from table1 where ..." _
, , adcmdtext+adexecutenorecords