[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2004-03-30
10
Medium Priority
?
270 Views
Last Modified: 2010-05-02
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 ?

0
Comment
Question by:mSchmidt
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 8

Expert Comment

by:dds110
ID: 10713437
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
 

Author Comment

by:mSchmidt
ID: 10713573
atm that one says 60 seconds and if i wait just 1 second iam usually fine, it doesnt only update every 60 seconds...
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 10714256
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:mSchmidt
ID: 10714422
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
 
LVL 28

Accepted Solution

by:
AzraSound earned 375 total points
ID: 10714473
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
 
LVL 11

Expert Comment

by:jmwheeler
ID: 10715758
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
 

Author Comment

by:mSchmidt
ID: 10716861
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
 
LVL 11

Expert Comment

by:jmwheeler
ID: 10718479
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
 

Author Comment

by:mSchmidt
ID: 10721080
i figured out that reconnect the connection to the database resulted in me not having to wait at all...
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 10725208
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month10 days, 1 hour left to enroll

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question