Access DB update problem, how can i wait for database to have written the data

I currently have a Booking program, a server, and a CashRegister. These are three different Visual Basic program, both the CashRegister and the Booking program interacts with each through the server.

Now thats actually working fine my problem even though is this
I am using a Access Database as database for the program, each program opens a connection to the database and gets all kind off information this way, now the problem is at some point my Booking program updates some information in the database
something like
 dim rs as adodb.recordset
 set rs = new adodb.recordset
 rs.open "Select * from regning_vare",frmfront.cnxn,adOpenDynamic,adLockOptimistic
 rs.addnew
 !vare = "Ol"
 rs.update

After this the Booking program tells the Server that it has done an update in the database which the other clients should be aware of, the server tells the clients to update their information, the problem here is that the information has not yet been updated in the database, which at some point gives errors or at least wrong information.

My question, how do i make my booking program wait to tell the server that it has done an update until it has actually been written to the database ?

mSchmidtAsked:
Who is Participating?
 
AzraSoundCommented:
I think Access is just quirky like that.  I have seen situation where people had to disconnect to get a quick update.  As far as knowing exactly when the data is updated, I don't know.  If you declare your recordset or connection objects WithEvents, there are some events there that may help you in this quest, but I have never played around with them to see how helpful they are.
0
 
dds110Commented:
Change the refresh interval in the access database and then have your prog wait that same specified amount of time.

Open Access, Click Tools>>Options

Click Advanced Tab

Change Refresh rate

HTH
0
 
mSchmidtAuthor Commented:
atm that one says 60 seconds and if i wait just 1 second iam usually fine, it doesnt only update every 60 seconds...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
AzraSoundCommented:
Are you closing the recordset that is doing the updating right after the update??  Does it update immediately if you close the connection from the program doing the updating?
0
 
mSchmidtAuthor Commented:
Ya iam closing the recordset immediately after i did the update
And yes it works better if i close and reestablish the connection, but this might also just be because this then takes some time to do ?

If i want to add a wait within my vb code, not creating a busy loop (which i tried, this doesnt solve anything) and not using a Timer control, how would i then do that ?

Still hopping someone could tell me how to make sure that data is updated in the database before proceeding within the function...
0
 
jmwheelerCommented:
I usually just use the Sleep function for half a second.  That seems to do the trick.

ex.
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
.
.
.

Sleep 500
0
 
mSchmidtAuthor Commented:
this is seriously the best way to do it ? hope that Sleeping 500 milis is enough (this way also adding a delay of half a second in ones program where it might not be neccesary)

there is no way using a events or something else to make vb wait for access ?
0
 
jmwheelerCommented:
I have never come across any way to have vb wait for access.  You probably don't need to wait a half second you could probably lower it to 200.  I used 200 in a program today and had no problems.
0
 
mSchmidtAuthor Commented:
i figured out that reconnect the connection to the database resulted in me not having to wait at all...
0
 
AzraSoundCommented:
That's really the only workaround I have seen for Access.  I suppose it has something to do with how Access handles updates/committing to disk/etc, but, I don't know enough about the internals of Access to tell you for sure.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.