ADO Slow transaction

[Marvin van Dongen]

Hello,

I have a VB App wich updates an Access DB by using ADO. The problem that I
am faced with now, is the following:

-I run 2 instances of my app.
-I open the same record in both instances
-I change the record in the first instance
-When I wait a few seconds, I update the same record with the second
instance.
-The app recognizes that the record has been changed since the opening, and
neatly displayes a message.

However, when I -DON'T- wait a few seconds before updating in the second
instance, the update triggered by the first instance hasn't been correctly
entered into the database, so my 2nd instance thinks nothing has changed.

So the problem seems to be with some sort of slow update. The first instance
is however completely finished with the update (and the connection is closed
afterwards). Maybe there is some kind of setting that changes the speed in
wich changes are applied. I hope someone can help.

Thnx,

Marvin

 

[Ron Hinds]

What are the arguments being passed to the ADO object? Can you post the
code?

 

[Marvin van Dongen]

Call ExecSQL(rs, cn, "SELECT * FROM [typ] WHERE [typ_id]=" &
txtNum, adOpenDynamic, adLockOptimistic)
'Check if the Update field of the item has changed since the
opening of that item
If rs!typ_update = tEditDate Then
'Dates are the same, so nothing changed. Save new values
rs!typ_name = txtName
rs!typ_desc = txtDesc
rs!typ_update = Now
rs.Update
tEditDate = rs!typ_update
rs.Close
cboType.List(cboType.ListIndex) = txtName
lblUpdated = tEditDate
cmdApply.Enabled = False

 

[Ron Hinds]

You might try using adLockPessimistic. Performance is less but i doesn't
sound like that would be an issue for you.

Call ExecSQL(rs, cn, "SELECT * FROM [typ] WHERE [typ_id]=" &
txtNum, adOpenDynamic, adLockOptimistic)
'Check if the Update field of the item has changed since the
opening of that item
If rs!typ_update = tEditDate Then
'Dates are the same, so nothing changed. Save new values
rs!typ_name = txtName
rs!typ_desc = txtDesc
rs!typ_update = Now
rs.Update
tEditDate = rs!typ_update
rs.Close
cboType.List(cboType.ListIndex) = txtName
lblUpdated = tEditDate
cmdApply.Enabled = False

What are the arguments being passed to the ADO object? Can you post the
code?

 

[Marvin van Dongen]

I can still beat the system by simple quickly switching between instances
and changing the same item, but it is a bit faster then before. Any more
tips?

You might try using adLockPessimistic. Performance is less but i doesn't
sound like that would be an issue for you.

Call ExecSQL(rs, cn, "SELECT * FROM [typ] WHERE [typ_id]=" &
txtNum, adOpenDynamic, adLockOptimistic)
'Check if the Update field of the item has changed since the
opening of that item
If rs!typ_update = tEditDate Then
'Dates are the same, so nothing changed. Save new values
rs!typ_name = txtName
rs!typ_desc = txtDesc
rs!typ_update = Now
rs.Update
tEditDate = rs!typ_update
rs.Close
cboType.List(cboType.ListIndex) = txtName
lblUpdated = tEditDate
cmdApply.Enabled = False

What are the arguments being passed to the ADO object? Can you post the
code?

Hello,

I have a VB App wich updates an Access DB by using ADO. The problem that
I
am faced with now, is the following:

-I run 2 instances of my app.
-I open the same record in both instances
-I change the record in the first instance
-When I wait a few seconds, I update the same record with the second
instance.
-The app recognizes that the record has been changed since the
opening,
and
neatly displayes a message.

However, when I -DON'T- wait a few seconds before updating in the second
instance, the update triggered by the first instance hasn't been
correctly
entered into the database, so my 2nd instance thinks nothing has changed.

So the problem seems to be with some sort of slow update. The first
instance
is however completely finished with the update (and the connection is
closed
afterwards). Maybe there is some kind of setting that changes the
speed
in
wich changes are applied. I hope someone can help.

Thnx,

Marvin

 

[Tony Toews]

I have a VB App wich updates an Access DB by using ADO. The problem that I
am faced with now, is the following:

Now this is DAO and not ADO but is closely related and may give you
enough keywords that you can continue your search on this problem.

ACC2000: DBEngine.Idle dbRefreshCache Refresh Is Not Immediate
http://support.microsoft.com/?kbid=202097

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm