DELAY UPDATING DAO ACCESS 1

[dabineri]

I have experienced (using VB6, DAO, Access, CR 9) that after filling a table, closing it and then printing it that there are often a few missing records on the report. I believe that this is due to a time delay in updating the table.
I have solved the problem (apparently) by closing the entire data base and then re opening it after editing it but before printing it. I presume that this forces the update to take place.
Is there a better way around this issue? Are there others who have observed this behavior? Is this really what is happening?

Thanks for any suggestions on this issue.

David Abineri

 

[petermeachem]

I get this. Pain isn't it? There seems to be a 5 second delay on writing. I didn't really find a decent way round it.
That's not very helpful!

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[Golom]

Look at the DBEngine.Idle method with argument dbRefreshCache and/or dbForceOSFlush. They respectively force DAO to refresh data from the database and flush pending data to it.

 

[dabineri]

Thank you, Thank you Golom! I have been looking for this for over a year and it seems to be exactly what I need.

Thanks for taking the time to respond, you have solved an important problem for me.

David Abineri

 

[dabineri]

Golom, I am finding that I get an error when I try to open a recordset after a BeginTrans. What is the best way aound this problem.

Thanks for your help, David Abineri

 

[Golom]

Depends on what the error is. Can you post more detail?

 

[dabineri]

It says "object invalid or no longer set.

CODE:
Set MyTemp = MyDB.OpenRecordset ......
If MyTemp.RecordCount > 0 Then

The error occurs on the second line above but does NOT occur if I remove the BeginTrans and ComitTrans. The ComitTrans is before this segment of code and the ComitTrans is after.

Does this help know what is happening?

Thanks, David Abineri

 

[Golom]

Only thing I can think of is the possibility that you have defined "MyTemp" with

Dim MyTemp As New DAO.Recordset

If you've used the "New" keyword then an attempt to assign a different instance to it within a transaction will have a problem because you are destroying an existing instance (the one created by Dim) that was created outside the BeginTrans ... ComitTrans block.

You should try to ensure that everything happens within the block and there aren't activities that happen before it begins or after it ends that involve the objects in the block of transactions.

 

[dabineri]

Thanks again for your reply.

I am DIMing MyTemp as Recordset at the module level and NOT within the transaction.

The trouble is that I need to be able to get data from one table to update another.

Thanks again for any ideas.
David Abineri