Solved

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

Posted on 2004-03-30
10
259 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:mSchmidt
Comment Utility
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 125 total points
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 11

Expert Comment

by:jmwheeler
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now