1

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
Boerdom
  • 13
  • 4
  • You're not specifying the Recordset type or locking types when you open the Recordset. Have you tried explicitly requesting a `dbOpenDynaset` type? – ThunderFrame Jun 06 '17 at 01:23
  • @ThunderFrame, it seems to be defaulting to `dbOpenDynaset` (I've checked oRS.Type which equals 2). – Boerdom Jun 06 '17 at 01:32
  • Are you not seeing the changes in the Access database, or the Excel front-end? This could well be a problem with the front-end that only reads the database when creating the GUI, or even creates a snapshot-type RS. – Erik A Jun 06 '17 at 14:00
  • @ErikvonAsmuth I'm not seeing the changes in the access database itself. My test method is to make a change using the front end, and open up the access database on a separate machine once the change is made. Currently the access database does not reflect the changes until the front-end disconnects from the database. It seems the date modified also only changes once the database connection is disconnected. – Boerdom Jun 06 '17 at 22:17
  • You may find [this answer](https://stackoverflow.com/a/17063542/2144390) interesting. – Gord Thompson Jun 07 '17 at 00:23
  • Thanks @GordThompson, I have updated the question – Boerdom Jun 07 '17 at 23:10

1 Answers1

0

Try changing the recordset Type and Options enums.

Set oRS = oQryDef.OpenRecordset(dbOpenDynaset, dbSeeChanges)

'dbOpenDynaset = 2
'dbSeeChanges = 512
Kostas K.
  • 8,293
  • 2
  • 22
  • 28
  • No luck unfortunately. When the front-end updates a record, the changes are still seemingly not applied to the database until the connection itself is closed (which I'm testing via opening the database on another machine once the change is made but connection is still open). The date modified of the database also does not change until the connection is closed. – Boerdom Jun 06 '17 at 22:19