Edit method in DAO changes values?

[Paul]

I've seen some code today that does something perplexing:

The scenario goes like this:

Some records are selected from some tables and joined by a query
Edit is called and one if the fields is changed
A value is changed
Update is called

<other stuff happens>

Edit is called again and the value in the underlying table reverts to
what is was before the last change. Infact just opening a separate
recordset of that table and doing an Edit on it has the same effect of
making the value revert.

The change clearly happens across the call to Edit, so what's going on ?
If the value of the underlying table is changed directly rather than
through the joining query the effect doesn't seem to happen.

No amount of requerying seems to change anything, but sticking the whole
thing in a trasnaction seems to make the problem go away.

I wouldn't rule out something like mismatched Edits and Updates, but I'd
hope for an error or something else in that case, not values that
magically change when you call Edit.

Does anyone know anything about this or has seen anything like it?

I've seen articles about changes being discarded if you don't move of the
record before closing and wondered if it might be something like that.

Thanks,

Paul

 

[Allen Browne]

Paul, what version of Access is this?

If you are seeing something that makes no sense, the first thing would be to
ensure you have the latest Office Service Pack for your version, and also
the latest service pack for JET. Both downloadable from
support.microsoft.com.

Is it possible an error is occuring and you don't know about it? Temporarily
remove any error handling (including parent procedures) while debugging
this.

If the Update is occuring inside a transaction, and you then perform some
other operation (even a DLookup()) before committing the transaction, the
new value will not have been written to disk.

If you are working in a transaction, discover a bug, and reset your code,
this does NOT rollback the transaction on the default workspace, because the
default workspace is not reset. In the Immediate Window:
dbEngine(0).RollBack
repeatedly until you receive an error since transactions can be nested 5
times, and you may have more than one running.)

If none of these issues apply, the remaining possibilties are some kind of
corruption (import into a new database) or multi-user issue.

 

[Paul]

Paul, what version of Access is this?

Access 2000

If you are seeing something that makes no sense, the first thing would
be to ensure you have the latest Office Service Pack for your version,

I know I don't have the latest SPs, but neither will the people who will be
eventually running this :-(

Is it possible an error is occuring and you don't know about it?
Temporarily remove any error handling (including parent procedures)
while debugging this.

Unlikely, I've stepped through from the start of the code to where the
oddity happens.

If the Update is occuring inside a transaction, and you then perform
some other operation (even a DLookup()) before committing the
transaction, the new value will not have been written to disk.

If I put it in a transaction the effect vanishes.

If you are working in a transaction, discover a bug, and reset your
code, this does NOT rollback the transaction on the default workspace,
because the default workspace is not reset. In the Immediate Window:
dbEngine(0).RollBack
repeatedly until you receive an error since transactions can be nested
5 times, and you may have more than one running.)

Worth knowing

If none of these issues apply, the remaining possibilties are some
kind of corruption (import into a new database) or multi-user issue.

Well Access seemed to think it was a multi user issue, but there's nobody
else there using it. There has been a case where the code finishes running,
I open the table to look at it, I click in the field that was edited,
Access complains that someone else has made changes (someone else in this
case being the code presumably), and the field zips back to the value way
it was.

Closing and opening the database again seems to have the same effect. Its
as though the new value isn't being written back properly.

Paul

 

[Scott McDaniel]

Access 2000


I know I don't have the latest SPs, but neither will the people who will be
eventually running this :-(

A2K is well known to have a lot of troubles if it's not at least SP1a ...
can you not instruct your end users to update their machines?

 

[Paul]

A2K is well known to have a lot of troubles if it's not at least SP1a
... can you not instruct your end users to update their machines?

Something involing "pigs" "fly" and "might" I think. I tried the newest
service pack anyway and it didn't seem to fix anything.

Oh well, chalk up another reason not to use Access.

Paul

 

[TC]

Something involing "pigs" "fly" and "might" I think. I tried the newest
service pack anyway and it didn't seem to fix anything.

Oh well, chalk up another reason not to use Access.

Come off it!

I've found the occasional definite bug in Access, but that is hardly
"another reason not to use" it!

Name me a single software product that is guaranteed to have no bugs!

TC
(no bugs!)

 

[Allen Browne]

Applying the service pack will save you grief (assuming you continue to use
the product). If you want to ensure your users have the appropriate s.p.
applied, you can use the code in this link in your application to notify
them if they need a fix:
http://www.mvps.org/access/api/api0065.htm

It would be worth tracing through the code to find out the cause of the
error. Although we use recordsets less frequently than we used to, we do use
them in every app we write, and they do work reliably. I'll wager the
problem is not with the DAO Edit method, but with some other aspect of the
database/code.

If you don't have the time to do that, you already have a workaround for now
(a transaction).

Re-reading the end of your previous post, you have also established that the
issue does in fact relate to concurrency. You might even be able to solve it
by making sure that there are no unsaved edits in any of your forms or other
code at the time this code executes.

 

[david epsom dot com dot au]

A2K is well known to have a lot of troubles if it's not at least SP1a

Access 2K must be on at least SR1. Simple code to check:

'David Graham. (david) Right to attribution retained.
'Watch for word wrap

Option Compare Database
Option Explicit
Const mcModuleName = "mdlRP_DLLCheck"
'2003/02/26 dlg
Private Declare Function GetFileVersionInfo& Lib "Version" Alias
"GetFileVersionInfoA" (ByVal FileName$, ByVal lptr&, ByVal lSize&, lpvData
As Any)

Public Sub gsbDLLCheck()
If ("4.0.7328.0" > gfn_GetVersion("msjet40.dll")) Then MsgBox "Warning:
MSJET40.dll is " & gfn_GetVersion("msjet40.dll") ' & ", not SP7"
If ("9.0.0.3822" > gfn_GetVersion("msaccess.exe")) Then MsgBox "Warning:
MSACCESS.EXE is " & gfn_GetVersion("msaccess.exe") ' & ", not SR1"

End Sub

Public Function gfn_GetVersion(FileName$)
'2003/02/26 dlg --minimalist version -- Right to attribution retained
Dim iBuf(0 To 99) As Integer
Call GetFileVersionInfo(FileName$, 0&, 200, iBuf(0))
gfn_GetVersion = iBuf(25) & "." & iBuf(24) & "." & iBuf(27) & "." &
iBuf(26) 'FILE VERSION

End Function
---------
The release manifest from MS contains the version numbers, if you wish
to convert back to release number.

(david)

 

[david epsom dot com dot au]

Jet caches data. Cache data is read & written on a background
thread. If you work on just one connection, you should see
any data you have cached, but it is sometimes not obvious that
you are working on separate connections.

Explicit transactions are synchronous by default. That means
that the transaction does not complete until the data has
been written back to the file system.

Implicit transactions (action queries and dao update/append/
delete) are asynchronous by default.

Here are some approaches to your problem:

1) Recode your application so that the write and read are
on the same connection.

2) Issue to the Jet Engine an explicit OS flush command,
followed by explicit cache refresh command.

3) Use explicit transactions

4) Tell jet to use synchronous updates for implicit transactions.

(david)

 

[TC]

(snip)

Implicit transactions (action queries and dao update/append/
delete) are asynchronous by default.

Erm, action queries are not wrapped in implicit transactions since A97,
methinks

TC

 

[david epsom dot com dot au]

Jet 3.5:
"Large queries run faster, due to improvements in the transactional behavior
for SQL data manipulation language (DML) statements and to new registry
settings that force transactions to commit when a certain lock threshold is
reached."

Jet 3.0:
"· Implicit transactions are built into the Jet database engine, so you can
take advantage of the improved speed provided by transactions without using
the BeginTrans and CommitTrans methods in your code. To retain full control
over when data is written to disk, you should construct explicit
transactions by using the BeginTrans and CommitTrans methods."

"ImplicitCommitSync Specifies whether the system waits for a commit to
finish. A value of No instructs the system to proceed without waiting for
the commit to finish; a value of Yes instructs the system to wait for the
commit to finish. The default is No."

DAO 3.6:
"DAO 3.6 has been updated to use the Microsoft® Jet 4.0 database engine.
This includes enabling all interfaces for Unicode. Data is now provided in
unicode (internationally enabled) format rather than ANSI. No other new
features were implemented. "

Jet 4.0:
There is unfortunately no proper documentation or help file for Jet 4.0, but
the changes include support for 'ANSI' SQL, Replication, and Unicode.

(david)

 

[TC]

Then the docs are wrong! Update queries in Access 97 are not wrapped in an
implicit transaction - whatever the docs might say. This is so, regardless
of whether you do or don't use dbfailonerror. I'm certain of this, because I
investigated it recently, as I was not sure how it behaved, myself. I
believe this change was introduced in A97. In A95, they >are< wrapped. In
A97 (& presumeably later), they are not.

Cheers,
TC

 

[david epsom dot com dot au]

My understanding of an 'implicit transaction' without dbFailOnError
is that all locks are held until the action completes. I'm not sure
that it means anything else at all.

If you run an update query in the Access IDE or Delete a record
from an open Form, Access will ASK you if you wish to commit
the transaction. If you say no, Access will Roll-Back the transaction.

If you use DAO, and use DAO.dbFailOnError, the entire action query
will be rolled-back if there is an error.

Transactions are broken in Jet 3+, and you may sometimes get a
'partial commit' even when using explicit transactions.

However, in my testing, I could not demonstrate asynchronous
actions or partial commits at all. How did you test for implicit
transactions?


(david)

 

[Paul]

Applying the service pack will save you grief (assuming you continue
to use the product).

Not my choice

It would be worth tracing through the code to find out the cause of
the error. Although we use recordsets less frequently than we used to,
we do use them in every app we write, and they do work reliably. I'll
wager the problem is not with the DAO Edit method, but with some other
aspect of the database/code.

What do you use if not Recordsets?
I have stepped through, table open in one pane, code open next to it. I
can refresh and query the table to my heart's content it stays put. I
take a one line step, executing the Edit call and the table changes.

I'm not denying there might be a problem elsewhere, but two wrongs don't
make a right. I can't think of any mistake one should be able to make
that causes the contents of a table to change like that.

If you don't have the time to do that, you already have a workaround
for now (a transaction).

That wasn't the only 'move the code around a bit' option that made it go
away, and the issue has long since been forgotten (so nobody is ever
going to look into it now). But I don't like problems that just magically
appear and equally magically disappear.

Re-reading the end of your previous post, you have also established
that the issue does in fact relate to concurrency. You might even be
able to solve it by making sure that there are no unsaved edits in any
of your forms or other code at the time this code executes.

There is no other code running, but the suggestion from the Access error
message does seem to point towards some sort of concurrency issue, as
though it has two different versions of that record in two different
places and gets confused about which one it is supposed to keep.

Are there any issues concerning calling Edit again on a Recordset without
having moved the cursor? That particular entry also appears elsewhere in
the selected recordset and I wonder whether that causes the problem.
Changes not made through the 'problem' query seem to stick, but calling
edit on anything that will change that table changes it back.

Whatever the answer is, I don't think the secret will be yielded easily,
by whatever is keeping it.

Paul

 

[Paul]

Transactions are broken in Jet 3+, and you may sometimes get a
'partial commit' even when using explicit transactions.

I've seen transactions that seem to stick even when not committed, this was
working with an external ODBC data source though. I'm afraid I didn't chase
it, there never is enough time to chase everything.

As I said in my original posting, if I stick the whole shebag in a
transaction then it behaves as expected (well as I expected at any rate),
but I don't understand why.

Paul

 

[Paul]

Access 2K must be on at least SR1. Simple code to check:

I'm pretty sure they are all SR1. I didn't think there was an SP1 ?

Private Declare Function GetFileVersionInfo& Lib "Version" Alias
"GetFileVersionInfoA" (ByVal FileName$, ByVal lptr&, ByVal lSize&,
lpvData As Any)

I don't think I've ever seen that syntax for declaring a function return
type before, not in VB anyway. I can't remember details far back enough to
tell what other BASICs did (well the ones that had functions anyway...)

The release manifest from MS contains the version numbers, if you wish
to convert back to release number.

I have no idea what you just said there

Paul

 

[cafe]

My understanding of an 'implicit transaction' without dbFailOnError
is that all locks are held until the action completes. I'm not sure
that it means anything else at all.

An "implicit" transactin is a transaction that is created & managed "behind
the scenes" by Access, DAO, Jet, or whatever. An "explicit" transaction is
one that is created & managed by the programmer using the BeginTrans,
CommitTrans & Rollback methods. dbfailonerror is unrelated to those
definitions IMO.

If you run an update query in the Access IDE or Delete a record
from an open Form, Access will ASK you if you wish to commit
the transaction. If you say no, Access will Roll-Back the transaction.

Sure. That is because >Access< wraps the update in a transaction.

If you use DAO, and use DAO.dbFailOnError, the entire action query
will be rolled-back if there is an error.

Sorry, that is just not so in A97(+?). You can easily confirm this by
running an update that you know will >succeed< on at least 1 record, and

fail< on at least one other record (eg. with a duplicate key error). Run

the update using db.execute (not from the IDE). This is what happens:

* If you specified dbfailonerror, the statement will fail with the duplicate
key error (or whatever). However, the statement has NOT been wrapped in an
implicit transaction. The "good" updates, up to the point of the "bad" one,

have been updated into the database<. You can easily confirm this by

closing/reopening the database & checking the table(s). (Obviously, to run
this test, you need to ensure that the "bad" update is not the first one
attempted.) When talking of "good" updates and "bad" updates here, I am
talking of the individual updates that will be attempted by the single
db.execute statement.

* If you did >not< specify dbfailonerror, the whole update will seem to run
to completion. >No error will occur<. The "bad" update will have been
attempted - failed - and >discarded silently<. The good updates will have
been updated into the database. Again you can easily confirm this by
closing/reopening the database & checking the table(s).

This is what actually happens in practice, as determined by actually running
those tests. If the docs disagree: they are wrong.

In summary:

* Prior to A97, when an update query is run from code using the execute
method of the database object, the changes were automatically wrapped in an
implicit transaction (by DAO, Jet or whatever). If an error occurred, (and
dbfailonerror was set?), the transaction was rolled-back, so the update was
an "all or nothing" thing.

* In A97 (& I suspect all later versions), when an update query is run from
code using the execute method of the database object, the changes >are not<
wrapped in an implicit transaction. If an error occurs, the updates that
completed successfully (if any) >remain in the database<. This is so,

regardless of the presence or absence of dbfailonerror</ If that is not

what you want, you must wrap the update in your own, explicit transaction.

David, I'm always open to being told that I'm wrong. But I have investigated
this issue carefully, and I am saying that this is what actually happens, in
a real-life database, running real-life tests - regardless of what the
documents say.

Cheers
TC

 

[david epsom dot com dot au]

I'm pretty sure they are all SR1. I didn't think there was an SP1 ?

Semantics!
JET Service Releases are normally named Service Release 1,2,3 etc.
OFFICE Service Packs are normally named Service Pack 1,2,3 etc.

If you are using Access 2000, you must be at least on Service Pack 1
for Office 2000. If you go to Help/About in MS Access, it will show
something like

Microsoft(R) Access 2000 (9.0.6926 SP-3)

(Office 2000 went to SP-3)

Then you should be on the latest Jet 4.0 Service Release: currently
SR7 for Win98, SR8 for WinXP.

(david)

 

[TC]

Agree?
Disagree?
Don't Care Either Way?

TC

An "implicit" transactin is a transaction that is created & managed "behind
the scenes" by Access, DAO, Jet, or whatever. An "explicit" transaction is
one that is created & managed by the programmer using the BeginTrans,
CommitTrans & Rollback methods. dbfailonerror is unrelated to those
definitions IMO.



Sure. That is because >Access< wraps the update in a transaction.



Sorry, that is just not so in A97(+?). You can easily confirm this by
running an update that you know will >succeed< on at least 1 record, and
the update using db.execute (not from the IDE). This is what happens:

* If you specified dbfailonerror, the statement will fail with the duplicate
key error (or whatever). However, the statement has NOT been wrapped in an
implicit transaction. The "good" updates, up to the point of the "bad" one,
closing/reopening the database & checking the table(s). (Obviously, to run
this test, you need to ensure that the "bad" update is not the first one
attempted.) When talking of "good" updates and "bad" updates here, I am
talking of the individual updates that will be attempted by the single
db.execute statement.

* If you did >not< specify dbfailonerror, the whole update will seem to run
to completion. >No error will occur<. The "bad" update will have been
attempted - failed - and >discarded silently<. The good updates will have
been updated into the database. Again you can easily confirm this by
closing/reopening the database & checking the table(s).

This is what actually happens in practice, as determined by actually running
those tests. If the docs disagree: they are wrong.

In summary:

* Prior to A97, when an update query is run from code using the execute
method of the database object, the changes were automatically wrapped in an
implicit transaction (by DAO, Jet or whatever). If an error occurred, (and
dbfailonerror was set?), the transaction was rolled-back, so the update was
an "all or nothing" thing.

* In A97 (& I suspect all later versions), when an update query is run from
code using the execute method of the database object, the changes >are not<
wrapped in an implicit transaction. If an error occurs, the updates that
completed successfully (if any) >remain in the database<. This is so,
what you want, you must wrap the update in your own, explicit transaction.

David, I'm always open to being told that I'm wrong. But I have investigated
this issue carefully, and I am saying that this is what actually happens, in
a real-life database, running real-life tests - regardless of what the
documents say.

Cheers
TC

in

 

[david epsom dot com dot au]

Does care, but need to experiment/study! Need to defer experiments
until I have a couple of hours free....

(david)