I have an application in which I am using remote views that can connect to different database backends. The remote views are normally defined connected to a VFP database. At runtime, the connection may be changed to allow it to use VFP, SQL Server, Oracle, or Access backends via ODBC.

This seems to work fine except that when I am using Access MDB, the database is not being updated quickly enough for any subsequent requery to see the changes unless I introduce at least 1/2 second delay before any requery of the base table.

Example
View1 is a remote view to table1 = optimistic Table buffering
View2 is a remote view to table1 = optimistic Table buffering with a parameter m.keyfield to get the newly added record with additional fields from other tables
tableupdate(0,.t.) is used to update any record that is added or changed.

Any subsequent view opened or requeried (within 1/2 second) on the base table does not see the updated records.

select view1
locate for keyfield = key value
if !found()
  append blank
  replace keyfield with key value, field1 with .....
  if !tableupdate(0,.t.,"view1")
     tablerevert(.f.,"view1")
     return .f.
  endif
endif

*!* record was inserted and updated *** 
*!* same result in access with tableupdate(t,.t.,"view1")
>
... process about 25 - 50  lines of code
>
*!* get the record just inserted from table1 
m.keyfield = key value
if used("view2")
  if view2.keyfield  != key value
      =requery("view2")
  endif
else
  select 0 
  use view2 alias view2
endif
if reccount("view2") > 0
   *!* Record just inserted into view1 was found - 
   *!* Gets to here if VFP, SQL Server or Oracle
   *!* gets to here if access and inkey(.5) is inserted after tableupdate()

   .... further processing

   return .t.
else
   *!* record not found in table1 even though tableupdate returned .t.
   *!* get this result if access unless I put inkey(.5) after table update above
   *!* Record just inserted into view1 was NOT found - 
  return .f.
endif

This appears to be caused by buffered update and the Access odbc driver. I have tried changing settings on the access ODBC connection, used tableupdate(1,.t.), tried flush, even tried closing the view2 and reopening it before requery. This only seems to work if the process of closing the second view causes a delay of .5 seconds. Closing the updated view or adding a .5 second delay after any change is the only way that I have been able to consistently get the second view to see the updated ACCESS records. Either case causes about a .5 second delay. I can conditionally add a wait state by checking if it is using an access driver but this really kills performance when using ACCESS database.

The above works flawlessly using VFP, SQL Server and Oracle and is relatively fast. However Access seems to buffer the update even after tableupdate is issued.

Has anyone else seen this with Access MDB and VFP Remote views? Does the ACCESS ODBC driver buffer updates even when Tableupdate is issued in VFP until a timeout interval or when the view is closed? If so why? Is there a solution that will immediately update the access tables so that any requery will see changes?