My application uses a Jet/Access database to store information. I have no problems with creating, updating, inserting new data etc. However, I do have an issue to which I can not find a successful solution. In code, for example, I update the database and close it. I then immediately open it to re-populate screens with the new data, but sometimes I still see the original data because it seems that my VB code is working more quickly than the database! If I refresh a couple of times it eventually gets at the new data. Is there any way to ensure that all the database updating has been completed before my code tries to access the database again?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Jet Database updating 2
- Thread starter sacsac
- Start date
-
1
- #2
dilettante
MIS
In a VB6 program it is preferable to open a single Connection once, and use it throughout the life of the program.
If you are doing something strange instead, like opening Recordsets against a connection string... well, you're going to have a lot of small issues including performance problems.
If you are doing something strange instead, like opening Recordsets against a connection string... well, you're going to have a lot of small issues including performance problems.
- Thread starter
- #3
dilettante
MIS
If you use the databases serially, open Connections serially. If you use them in parallel open multiple Connection objects or else used linked tables.
Improved performance in .Net? You're kidding, right?
But please, feel free to go for it.
![[noevil]](/data/assets/smilies/noevil.gif "[noevil] [noevil]")
Improved performance in .Net? You're kidding, right?
But please, feel free to go for it.
- Thread starter
- #5
-
1
- #6
I don't like leaving a connection open any longer than necessary.
I use the OpenDynaset method to open a table, make the changes and close it immediately with never a problem.
This means I don't often use grids for regular data entry, just for looking at it. I update it with a recordset from text entry boxes
However I find that a data grid is totally reliable as long as you move away to a new line before closing it or at leave drive it to a different line and make the exit confirm that a rowchange feed back from the grid has happened.
This may be why sometimes you see it and sometimes you dont!
I never use ADODC controls preferring to set the recordsource in software
I use the OpenDynaset method to open a table, make the changes and close it immediately with never a problem.
This means I don't often use grids for regular data entry, just for looking at it. I update it with a recordset from text entry boxes
However I find that a data grid is totally reliable as long as you move away to a new line before closing it or at leave drive it to a different line and make the exit confirm that a rowchange feed back from the grid has happened.
This may be why sometimes you see it and sometimes you dont!
I never use ADODC controls preferring to set the recordsource in software
- Thread starter
- #7
Thanks tedsmith for your advice. I too am updating via code rather than ADODC controls, and always close the connection as quickly as I can. I think my issues are probably to do with the grid updating routines which I am (or maybe am NOT) deploying!
dilettante
MIS
You're making a big mistake most of the time if you open, close, reopen like that. These are relatively expensive operations, and a close takes some time to fully complete.
Your problem probably stems from concurrency control and Jet's caching of dirty pages. You can reduce some of the intervals involved by tweaking some Provider-specific properties but thrashing around with multiple connections means that at best you tighten some of the timing windows.
Your problem probably stems from concurrency control and Jet's caching of dirty pages. You can reduce some of the intervals involved by tweaking some Provider-specific properties but thrashing around with multiple connections means that at best you tighten some of the timing windows.
- Thread starter
- #9
I am not suggesting you open and close for every keystroke or change of field in a datagrid.
I only open when the form or datagrid appears and close when either are closed, not for the life of the whole application.
Usually with a datagrid, changing to a new line always stores the data in the underlying database anyway.
I only open when the form or datagrid appears and close when either are closed, not for the life of the whole application.
Usually with a datagrid, changing to a new line always stores the data in the underlying database anyway.
dilettante
MIS
You should not be opening/closing connections based on loading different forms. One connection per program works best, used as long as the program needs to access the database.
When opened shared (instead of exclusive) Jet 4.0 waits 50 milliseconds before flushing writes to disk by default. However Page Timeout defaults to 5 seconds, meaning another connection won't see a change until 5 seconds or so have passed.
The various caches and performance delays mean muitple cnonections or thrashing them opened and closed will lead to hard to diagnose problems like the one described here in the original question.
When opened shared (instead of exclusive) Jet 4.0 waits 50 milliseconds before flushing writes to disk by default. However Page Timeout defaults to 5 seconds, meaning another connection won't see a change until 5 seconds or so have passed.
The various caches and performance delays mean muitple cnonections or thrashing them opened and closed will lead to hard to diagnose problems like the one described here in the original question.
- Status
Similar threads
