[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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
?
269 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses

834 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