Access 2007 – INSERT and instant SELECT doesn’t retrieve the inserted data

by

I did some testing and there does seem to be some buffering going on with ACE OleDb. I found that

  1. If I did some INSERTs and then immediately did a SELECT on the same OleDbConnection the new rows were available right away.

  2. If I opened two OleDbConnection objects, INSERTed on con1, and then immediately SELECTed on con2 the new rows took approximately 5 seconds to become available on the con2 connection.

  3. If I opened two OleDbConnection objects, INSERTed on con1, did con1.Close(), and then immediately SELECTed on con2 the new rows were available right away.

So, it appears that closing the OleDbConnection has the effect of “flushing” the writes and making them available to other connections sooner. My guess is that OleDb is implicitly buffering (or “batching”) updates because ACE/Jet is a shared-file database and it is trying to reduce the contention on the database file.

(When I did the same test using SQL Server there was no significant difference between the case when I closed con1 and the case when I left it open. In every case the new records were available instantly.)

More Related Contents:

  1. Microsoft.ACE.OLEDB.12.0 provider is not registered
  2. OleDbCommand parameters order and priority
  3. VBScript & Access MDB – 800A0E7A – “Provider cannot be found. It may not be properly installed”
  4. Insert SQL command with Datetime in MS-Access
  5. Access 2007 – Left Join to a query returns #Error instead of Null
  6. Save password for ODBC connection to MS SQL server from MS Access 2007
  7. Trouble with InputBoxes
  8. Combine values from related rows into a single concatenated string value
  9. “General error Unable to open registry key Temporary (volatile) …” from Access ODBC
  10. Is there a simple way of populating dropdown in this Access Database schema?
  11. Is there any way to create multiple insert statements in a ms-access query?
  12. Running Microsoft Access as a Scheduled Task
  13. Trying to insert DateTime.Now into Date/Time field gives “Data type mismatch” error
  14. How to diagnose MS access crashes
  15. “Operation must use an updateable query” error in MS Access
  16. Run-time error ‘3061’. Too few parameters. Expected 1. (Access 2007)
  17. MS Access trigger?
  18. MS Access LIMIT X, Y
  19. Is MS Access (JET) suitable for multiuser access?
  20. How to loop through all controls in a form, including controls in a subform – Access 2007
  21. ConcatRelated() in Access, using two keys
  22. Concatenating multiple rows into single line in MS Access [duplicate]
  23. Different LIKE behaviour between my application and the Access query wizard
  24. Access VBA: Find max number in column and add 1
  25. ConcatRelated function in a query
  26. Create sequential ID value based on the year that a record is added
  27. How can I modify a saved Microsoft Access 2007 or 2010 Import Specification?
  28. SendKeys is messing with my NumLock key via VBA code in Access form
  29. How to make a passthrough / passthru query editable?
  30. Do we have transactions in MS-Access?

Leave a Comment