CODEWITHSUNDEEP
vbams-accessdaojetms-jet-ace
Boerdom
Boerdom

Reputation: 13

Excel to Access (ACE DAO) - How to flush write cache manually

To summarise my setup, I'm using an Excel/VBA front-end with an Access database (accdb) backend. The front end has a number of GUIs that use VBA to read/write to/from the database. When the application starts, it uses ACE DAO to open the database and this 'connection' remains open until the user closes the app.

The problem I'm having is that updates to records are not immediately visible to other users. I believe this is because updates/changes to records are being cached instead of being immediately written. The only way I've found to clear this cache and apply the changes is to close the connection.

Closing the connection isn't really an option because it would need to be done frequently (I would then need to constantly reconnect which takes time). The stripped down code I'm currently using is:

Opening the database:

Set oDB = DBEngine.OpenDatabase(TARGET_DB, False, False, "MS Access;PWD=" & TARGET_DB_PASS)

I use query definitions within the access database to retrieve recordsets. Once I have the right DAO.QueryDef, I call the following to get the recordset:

Set oRS = oQryDef.OpenRecordset

I then modify the fields I want to change, call .Update and close the recordset:

With oRS
        .Edit
        .Fields("Record_Locked") = True
        .Fields("Locked_By") = UCase(Environ("username"))
        .Fields("Locked_Date") = Now
        .Update
        .Close
End With

At this point, the changes are not made to the database until oDB.Close is called.

What I have tried so far:

Explicitly opening the recordset as dbDynatset with dbSeeChanges

Set oRS = oQryDef.OpenRecordset(dbOpenDynaset, dbSeeChanges)

Putting the changes to fields inside a transaction and using CommitTrans with dbForceOSFlush 

DBEngine.BeginTrans
With oRS
        .Edit
        .Fields("Record_Locked") = True
        .Fields("Locked_By") = UCase(Environ("username"))
        .Fields("Locked_Date") = Now
        .Update
        .Close
End With
DBEngine.CommitTrans dbForceOSFlush

Upvotes: 1

Views: 758

Answers (1)

Kostas K.
Kostas K.

Reputation: 8518

Try changing the recordset Type and Options enums.

Set oRS = oQryDef.OpenRecordset(dbOpenDynaset, dbSeeChanges)

'dbOpenDynaset = 2
'dbSeeChanges = 512

Upvotes: 0

Related Questions