MS Access Data Changes Not Committing Before Next Read?

[Mark S. Milley, MCAD (BinarySwitch)]

This is an interesting problem...

I'm using MS Access for the backend for a simple task list web
application. (I know, I know, but Access is all my Web Host will allow
without killing me in hosting fees...)

I haven't used Access for the back end of a website since 1999, so
maybe there is something I'm missing here.

When updating or inserting records into the database using ADO.NET, the
changes are not committing to the database immediately. When I select
from the table immediately after the update--even with the same
connection--I get a recordset that reflects the table prior to the
change. If I refresh the page, or pause for debugging, it apparently
gives access enough time to commit the change.

I had taken care of this temporarily by putting in a
System.Threading.Thread.Sleep(500) after statement that executes the
update, but now that I'm experimenting with Ajax (asyncronous calls)
with it, this problem is creeping up again.

Is there any command or Jet-SQL statement to force access to commit on
demand?

Thanks,

-Mark

 

[David W. Fenton]

This is an interesting problem...

I'm using MS Access for the backend for a simple task list web
application. (I know, I know, but Access is all my Web Host will
allow without killing me in hosting fees...)

Uh, you're not using Access -- you're using JET.

I haven't used Access for the back end of a website since 1999, so
maybe there is something I'm missing here.

When updating or inserting records into the database using
ADO.NET, the changes are not committing to the database
immediately. When I select from the table immediately after the
update--even with the same connection--I get a recordset that
reflects the table prior to the change. If I refresh the page, or
pause for debugging, it apparently gives access enough time to
commit the change.

Is there a .Refresh method to the ADO connection object?

My guess here is that you are actually not getting current
information about the connection and some kind of refresh needs to
be done manually.

But I don't do ADO or ADO.NET, so I'm just guessing.

I had taken care of this temporarily by putting in a
System.Threading.Thread.Sleep(500) after statement that executes
the update, but now that I'm experimenting with Ajax (asyncronous
calls) with it, this problem is creeping up again.

Is there any command or Jet-SQL statement to force access to
commit on demand?

Access doesn't commit data -- JET does, so Access commands have
ZILCH to do with your commands.

What you need to find out is how your data interface (ADO.NET)
interacts with the Jet db engine.

 

[George Ter-Saakov]

May be this article will help
http://support.microsoft.com/kb/q200300/

-----------------------------------------------------------------------
"When I select from the table immediately after the update--even with the
same connection"
I do not think that your statement correct.

It is easy to be tricked with ADO because if connection is busy (let say you
have open recordset on that connection) the ADO will silently open up new
connection and you will not even know that you have new connection

George.