Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2004-03-30
10
262 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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 125 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…

808 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