Best way to update records with a number sequence?

[moonbase]

I have a cheque writing routine. It works in 2 phases.

Phase 1: enter the first cheque no and allocate the number sequence to the transactions.

Phase 2: print the transactions with the cheque no.

What is the best way to carry out phase 1?

I can loop round an ADO recordset and allocate the number but this is not satisfacory because although the records are updated eventually, they not updated before the report prints. It is a timing issue. I can set up a wait routine but this doesn't seem right.

 

[lynchg]

Are you sying that that you loop through a recordset updating records one at a time, then later in code you print a report whose data source is the table you just updated, and the records have not been updated before the report prints?

 

[moonbase]

Exactly!!!!! I couldn't believe it myself at first but that is exactly what's happening.

It's shaken my committment to Access because I think you should be able to rely on it.

A connection is opened, the recordset opened, records updated with the number, recorset closed, connection closed. The report prints but the number isn't present unless you step through or create a doevents loop to check.

I started another thread about this some time ago but never got to the bottom of it. In the end I put it down to some asynchronous issue between ADO (updating the recordset) and DAO (I assume this is used internally for the report).

So, I am now looking at some other way to get round it, e.g. by using some sort of update query.

 

[lynchg]

I forget if you can use the COMMIT statement in Access code, maybe the updates are all still pending.

 

[moonbase]

I have tried the committrans but it makes no difference. I guess the commit forces it to write but not immediately.

 

[RoyVidar]

Yes, this is probably what is often referred to as Jets lazy write cache. I think the usual recommandation, is to do the update and retrieval on the same connection - which of course isn't possible in this scenario.

Then there's this How To Synchronize Writes and Reads with the Jet OLE DB Provider and ADO - with some methods (.RefreshCache).

And I think also some connection properties (for jet) that perhaps can be tried?

[tt]cn.properties("Jet OLEDB:Transaction Commit Mode") = 1
cn.properties("Jet OLEDB:Flush Transaction Timeout") = 0[/tt]

I haven't tried any of these, though ..

Roy-Vidar

 

[moonbase]

That's useful. At least I am not alone. I was struggling to find any reference to it. This gives me some new lines of investigation.