dao update works on all but one record?

[MP]

vb6, dao, mdb, win2k (no access)

db.Execute "Update " & TABLE_NAME & " Set fldMark = 'unassigned'"

i thought that would update all records in table
it updated all but one

to read them back i used
"SELECT <fldlistFROM " & TABLE_NAME & " WHERE FLDMARK <'unassigned'
ORDER BY <fldlist>"

result:
A : 1 : 0 : 24 : 1NA : >>>>2 <<<<<< this is fldMark

it should have returned no records
so all but one were updated, if i read them all they are correct except for
this one

is there a way to know a record was skipped during an update on all records?
a way to get error codes during an update?

any thoughts on how this would occur?

thanks
Mark

[Lyle Fairfield]

"MP" <No****@Thanks.comwrote in
news:45***********************@news.nationwide.net :

vb6, dao, mdb, win2k (no access)

db.Execute "Update " & TABLE_NAME & " Set fldMark = 'unassigned'"

i thought that would update all records in table
it updated all but one

to read them back i used
"SELECT <fldlistFROM " & TABLE_NAME & " WHERE FLDMARK <>
'unassigned' ORDER BY <fldlist>"

result:
A : 1 : 0 : 24 : 1NA : >>>>2 <<<<<< this is fldMark

it should have returned no records
so all but one were updated, if i read them all they are correct
except for this one

is there a way to know a record was skipped during an update on all
records? a way to get error codes during an update?

any thoughts on how this would occur?

thanks
Mark

Without testing I would conjecture that this would occur if there exists on
the table a unique compound(multi-field) index which includes fldMark and
updating that particular record would have resulted in a non-unique
combination of values for the fields involved.

--
Lyle Fairfield

[Granny Spitz via AccessMonster.com]

MP wrote:

any thoughts on how this would occur?

It won't update if that record is locked at the time of the update or if a
constraint is violated. And it might have been updated with "unassigned" but
was changed to "2" by another process before your readback code executed.

is there a way to know a record was skipped during an update on all records?
a way to get error codes during an update?

You should use error handling to tell you when the code fails so you'll know
why something failed. The syntax to call the error handler when the update
query fails (and then rolls back the transaction) is this:

db.Execute "Update " & TABLE_NAME & " Set fldMark = 'unassigned'",
dbFailOnError

You can also check the database object's RecordsAffected property to show a
count of how many records were updated.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1

[Lyle Fairfield]

"MP" <No****@Thanks.comwrote in
news:45***********************@news.nationwide.net :

vb6, dao, mdb, win2k (no access)

db.Execute "Update " & TABLE_NAME & " Set fldMark = 'unassigned'"

i thought that would update all records in table
it updated all but one

to read them back i used
"SELECT <fldlistFROM " & TABLE_NAME & " WHERE FLDMARK <>
'unassigned' ORDER BY <fldlist>"

result:
A : 1 : 0 : 24 : 1NA : >>>>2 <<<<<< this is fldMark

it should have returned no records
so all but one were updated, if i read them all they are correct
except for this one

is there a way to know a record was skipped during an update on all
records? a way to get error codes during an update?

any thoughts on how this would occur?

thanks
Mark

Did DAO pop up an error dialog? Perhaps there are option parameters such
dbFailOnError that you can include which will result in that happening. (If
you're not in Access you might have to use a literal, &H80, for
dbFailOnError, I'm not sure.)

Did you try it in ADO? Did ADO pop up an error dialog?

--
Lyle Fairfield

[MP]

"Granny Spitz via AccessMonster.com" <u26473@uwewrote in message
news:68652b170d071@uwe...

MP wrote:

any thoughts on how this would occur?

It won't update if that record is locked at the time of the update or if a
constraint is violated. And it might have been updated with "unassigned"

but

was changed to "2" by another process before your readback code executed.

is there a way to know a record was skipped during an update on all

records?

a way to get error codes during an update?

You should use error handling to tell you when the code fails so you'll

know

why something failed. The syntax to call the error handler when the

update

query fails (and then rolls back the transaction) is this:

db.Execute "Update " & TABLE_NAME & " Set fldMark = 'unassigned'",
dbFailOnError

You can also check the database object's RecordsAffected property to show

a

count of how many records were updated.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1

Thanks I'll look into that

[MP]

"Lyle Fairfield" <ly***********@aim.comwrote in message
news:Xn*********************************@216.221.8 1.119...

"MP" <No****@Thanks.comwrote in
news:45***********************@news.nationwide.net :

Did DAO pop up an error dialog?

no

Perhaps there are option parameters such

dbFailOnError that you can include which will result in that happening.

I'll try that

Did you try it in ADO? Did ADO pop up an error dialog?

will try that next

thanks

[MP]

"MP" <No****@Thanks.comwrote in message
news:45***********************@news.nationwide.net ...

>
"Lyle Fairfield" <ly***********@aim.comwrote in message
news:Xn*********************************@216.221.8 1.119...

"MP" <No****@Thanks.comwrote in
news:45***********************@news.nationwide.net :

Did DAO pop up an error dialog?

no

Perhaps there are option parameters such

dbFailOnError that you can include which will result in that happening.

I'll try that

Did you try it in ADO? Did ADO pop up an error dialog?

will try that next

thanks

this is so weird
both dao and ado leave the same record untouched
neither throw errors
dao.RecordsAffected shows 37 of 38 are set

i'm sure nothing is running after this test that resets the value
very strange
is there a way to test a row of a recordset to see if it's somehow magically
locked, in the middle of an update?

[MP]

"MP" <No****@Thanks.comwrote in message
news:45***********************@news.nationwide.net ...

can an mdb become corrupted after many debug runs such that a field becomes
inoperable?
ado produces no error and still leaves the field untouched

log report:
attempt sql: Update tblTypeCond Set fldMark = 'testado'
Execute - no error
A : 1 : 0 : 8 : testado
A : 1 : 0 : 8 : testado
A : 1 : 0 : 12 : testado
A : 1 : 0 : 12 : testado
A : 1 : 0 : 18 : testado
A : 1 : 0 : 18 : testado
A : 1 : 0 : 18 : testado
A : 1 : 0 : 18 : testado
A : 1 : 0 : 24 : 2
A : 1 : 0 : 24 : testado
A : 1 : 0 : 24 : testado
....etc remaining records

[Granny Spitz via AccessMonster.com]

MP wrote:

this is so weird
both dao and ado leave the same record untouched
neither throw errors
dao.RecordsAffected shows 37 of 38 are set

I'll take a look at it if you want. Let me know and I'll give my email
address (properly disguised of course). Create a new database, import the
table into it and zip the file.

is there a way to test a row of a recordset to see if it's somehow magically
locked, in the middle of an update?

I don't think you can with a desktop database, only a client/server database.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1

[David W. Fenton]

"MP" <No****@Thanks.comwrote in
news:45***********************@news.nationwide.net :

vb6, dao, mdb, win2k (no access)

db.Execute "Update " & TABLE_NAME & " Set fldMark = 'unassigned'"

Always do

db.Execute strSQL, dbFailOnError

and then have an error handler for it.

Also, you can check the .RecordsAffected property of the db
variable, and then compare that to the number of records in the
actual table to be sure that all of them have been updated.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Granny Spitz via AccessMonster.com]

MP wrote:

can an mdb become corrupted after many debug runs such that a field becomes
inoperable?

An mdb can become corrupted, but it's from being interrupted during a write
to file operation. That can happen when a user hits the power switch, the
network connection drops, etc.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1

[MP]

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...

"MP" <No****@Thanks.comwrote in
news:45***********************@news.nationwide.net :

vb6, dao, mdb, win2k (no access)

db.Execute "Update " & TABLE_NAME & " Set fldMark = 'unassigned'"

Always do

db.Execute strSQL, dbFailOnError

and then have an error handler for it.

Also, you can check the .RecordsAffected property of the db
variable, and then compare that to the number of records in the
actual table to be sure that all of them have been updated.

That's what i've done
no errors
no update (one record only - out of 38 total)

fwiw the properties of the one field...in case that shows anything strange
about that field (i have no idea if it does or not)

fldMark Props0: Value Value: 2
fldMark Props1: Attributes Value: 34
fldMark Props2: CollatingOrder Value: 1033
fldMark Props3: Type Value: 10
fldMark Props4: Name Value: fldMark
fldMark Props5: OrdinalPosition Value: 4
fldMark Props6: Size Value: 255
fldMark Props7: SourceField Value: fldMark
fldMark Props8: SourceTable Value: tblTypeCond
fldMark Props9: ValidateOnSet Value: False
fldMark Props10: DataUpdatable Value: True
fldMark Props11: ForeignName Value: can't retrieve
fldMark Props12: DefaultValue Value:
fldMark Props13: ValidationRule Value:
fldMark Props14: ValidationText Value:
fldMark Props15: Required Value: False
fldMark Props16: AllowZeroLength Value: False
fldMark Props17: FieldSize Value: can't retrieve
fldMark Props18: OriginalValue Value: can't retrieve
fldMark Props19: VisibleValue Value: can't retrieve

got the one problem field and properties thus
(just hard coded the '2' in there cause that 's the one that's "bad"

sSqlRead = "Select fldTypeName, fldCondName, fldCondVar, fldLength, fldMark
From " & TABLE_NAME & " WHERE FldMark = '2' order by fldTypeName,
fldCondName, fldCondVar, fldLength, fldMark "
Set oRs = db.OpenRecordset(sSqlRead)
If Not oRs Is Nothing Then
oRs.MoveFirst
While Not oRs.EOF
For Each oFld In oRs.Fields
For i = 0 To oFld.Properties.Count - 1
sRpt = sRpt & oFld.Name & " Props" & i & ": " &
oFld.Properties(i).Name

'temporarily override normal error handler in routine(normally don't use
resume next!!!)
On Error Resume Next
sRpt = sRpt & vbTab & "Value: <" &
CStr(oFld.Properties(i).Value) & ">" & vbCrLf
If Err Then
sRpt = sRpt & vbTab & "Value: can't retrieve" &
vbCrLf
End If

'reset error handler
On Error GoTo 0
Next i
sRpt = sRpt & "Next fld"
Next oFld
oRs.MoveNext
Wend

[MP]

"Granny Spitz via AccessMonster.com" <u26473@uwewrote in message
news:68675ee00b647@uwe...

MP wrote:

>
I'll take a look at it if you want. Let me know and I'll give my email
address (properly disguised of course). Create a new database, import the
table into it and zip the file.

many thanks for the offer, but this is just a test db, not worth spending
much time on.

just wondering if there was a well known reason a field would get weird.

I tried dao repair but got error operation not supported
got this from msdn
DBEngine.RepairDatabase DATABASE_NAME

but there's no .RepairDatabase method on the object in objectbrowser

so i tried .CompactDatabase and that worked.

now I only have 37 fields so somehow that one field got messed up and is now
gone

i've been testing against this one for several days now so it may just be
'worn out'

:-)

now both ado and dao updates work....

Thanks again for your offer of looking at it, i really appreciate that
Mark

[MP]

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...

"MP" <No****@Thanks.comwrote in
news:45***********************@news.nationwide.net :

vb6, dao, mdb, win2k (no access)

db.Execute "Update " & TABLE_NAME & " Set fldMark = 'unassigned'"

Always do

db.Execute strSQL, dbFailOnError

and then have an error handler for it.

Also, you can check the .RecordsAffected property of the db
variable, and then compare that to the number of records in the
actual table to be sure that all of them have been updated.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

yahoo
..CompactDatabase got rid of the pesky no update field...wonder what had
happened to it?

just over use from all my errant test code no doubt
:-)

[David W. Fenton]

"MP" <No****@Thanks.comwrote in
news:45***********************@news.nationwide.net :

I tried dao repair but got error operation not supported
got this from msdn
DBEngine.RepairDatabase DATABASE_NAME

but there's no .RepairDatabase method on the object in
objectbrowser

so i tried .CompactDatabase and that worked

The Repair is never necessary by itself. That is there for backwards
compatibility, as repair and compact used to be separate operations,
but in Jet 3.5, it was discovered that a repair to an uncorrupted
MDB could damage it, so in Jet 3.5 SP2, Repair was neutered and
folded into the Compact process. Now, a compact first checks if
there's anything that needs to be repaired, and if so, it repairs
before the compact. If nothing is wrong, the repair is skipped and
it just compacts.

If it's Jet 4 data, you should have an MSysCompactErrors table that
should tell you what was wrong.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"MP" <No****@Thanks.comwrote in
news:45***********************@news.nationwide.net :

.CompactDatabase got rid of the pesky no update field...wonder
what had happened to it?

just over use from all my errant test code no doubt

Are there memo fields?

Are you using a good version of Jet? Make sure the version is at
least 4.0.6xxx. If the build is lower than that, you need to
download the latest Jet service pack. Earlier versions had problems
with corrupted indexes, which is very consistent with the kind of
error you were getting.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Granny Spitz via AccessMonster.com]

MP wrote:

just wondering if there was a well known reason a field would get weird.

Corruption or an old Jet 4.0 service pack. There were 8 of them because
there were problems with the first 7.

but there's no .RepairDatabase method on the object in objectbrowser

so i tried .CompactDatabase and that worked.

Yes, there's no separate repair operation in more recent versions of Access.
But the /repair commandline switch will compact the database, including the
MSysAccessObjects in Access 2002.

Thanks again for your offer of looking at it, i really appreciate that

You're welcome. Glad everything's back to normal.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1

[Lyle Fairfield]

MP wrote:

many thanks for the offer, but this is just a test db, not worth spending
much time on.

just wondering if there was a well known reason a field would get weird.

Arggggggggggggggggghhhhhhhhhhhhhhhhhhhhhhhhh!

[Larry Linson]

"Lyle Fairfield" wrote

MP wrote:

>many thanks for the offer, but this is just
a test db, not worth spending much time on.

>just wondering if there was a well known
reason a field would get weird.

Arggggggggggggggggghhhhhhhhhhhhhhhhhhhhhhhhh!

Well, now that you mention it, some Fields "get wierd" when they hear
"Arggggggggggggggggghhhhhhhhhhhhhhhhhhhhhhhhh! " in either a Texas or
Canadian accent.

(Note: this is a little-known, undocumented feature, so you won't find it in
Help or the literature.)

<GRINNIN', DUCKIN', & RUNNIN'>

Larry

[MP]

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...

"MP" <No****@Thanks.comwrote in
news:45***********************@news.nationwide.net :

.CompactDatabase got rid of the pesky no update field...wonder
what had happened to it?

just over use from all my errant test code no doubt

Are there memo fields?

Are you using a good version of Jet? Make sure the version is at
least 4.0.6xxx. If the build is lower than that, you need to
download the latest Jet service pack. Earlier versions had problems
with corrupted indexes, which is very consistent with the kind of
error you were getting.

checking google for how to find version of jet it seems i need to write some
api stuff to get that info?
is that correct or is there a way to get that from dao?

since the previous post it's happened again, and compacting, while fixing
the 'problem' for the moment also deletes one record
so it's ok for my debugging process but i need to find an actual fix before
using this app in production.

Thanks
Mark

[MP]

"MP" <no****@Thanks.comwrote in message
news:f4******************@tornado.rdc-kc.rr.com...

>
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...

"MP" <No****@Thanks.comwrote in
news:45***********************@news.nationwide.net :

.CompactDatabase got rid of the pesky no update field...wonder
what had happened to it?
>
just over use from all my errant test code no doubt

Are there memo fields?

Are you using a good version of Jet? Make sure the version is at
least 4.0.6xxx. If the build is lower than that, you need to
download the latest Jet service pack. Earlier versions had problems
with corrupted indexes, which is very consistent with the kind of
error you were getting.

checking google for how to find version of jet it seems i need to write

some

api stuff to get that info?
is that correct or is there a way to get that from dao?

since the previous post it's happened again, and compacting, while fixing
the 'problem' for the moment also deletes one record
so it's ok for my debugging process but i need to find an actual fix

before

using this app in production.

Thanks
Mark

after more googling i just looked at msjet40.dll in windows explorer
properties shows version to be
4.00.9025.0
so i guess i'm ok on that
which is bad cause it means i have some other unknown problem
:-)

[Lyle Fairfield]

"MP" <no****@Thanks.comwrote in
news:rh*****************@tornado.rdc-kc.rr.com:

which is bad cause it means i have some other unknown problem

I think not. Verbosity is quite a well-known problem.

--
Lyle Fairfield

from http://msdn.microsoft.com/library/de...l=/library/en-
us/dnmdac/html/data_mdacroadmap.asp

Obsolete Data Access Technologies
Obsolete technologies are technologies that have not been enhanced or
updated in several product releases and that will be excluded from future
product releases. Do not use these technologies when you write new
applications. When you modify existing applications that are written using
these technologies, consider migrating those applications to ADO.NET.
The following components are considered obsolete:
....
Data Access Objects (DAO): DAO provides access to JET (Access) databases.
This API can be used from Microsoft Visual Basic®, Microsoft Visual C++®,
and scripting languages. It was included with Microsoft Office 2000 and
Office XP. DAO 3.6 is the final version of this technology. It will not be
available on the 64-bit Windows operating system.
.....

[MP]

"Lyle Fairfield" <ly***********@aim.comwrote in message
news:Xn*********************************@216.221.8 1.119...

"MP" <no****@Thanks.comwrote in
news:rh*****************@tornado.rdc-kc.rr.com:

which is bad cause it means i have some other unknown problem

I think not. Verbosity is quite a well-known problem.

--
Lyle Fairfield

No, i meant in *addition* to *that* one!
:-)

[MP]

"Lyle Fairfield" <ly***********@aim.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...

MP wrote:

many thanks for the offer, but this is just a test db, not worth

spending

much time on.

just wondering if there was a well known reason a field would get weird.

Arggggggggggggggggghhhhhhhhhhhhhhhhhhhhhhhhh!

well, being new to this forum I'm not sure what that means?

in the context of someone offering to look at the specific problem .mdb file
i responded
<< not worth spending much time on.

don't know if you took that to mean the problem itself was not worth the
time?
what I mean is that the mdb file is created on the fly reading values from
an external program
any given mdb file is thus "temporary" in nature...being used to do some
calculations.
The mdb file itself is not too important...the process i need to solve *Is*
important.

but maybe I'm misinterpreting "
Arggggggggggggggggghhhhhhhhhhhhhhhhhhhhhhhhh!"
:-)

[David W. Fenton]

"MP" <no****@Thanks.comwrote in
news:f4******************@tornado.rdc-kc.rr.com:

>
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...

>"MP" <No****@Thanks.comwrote in
news:45***********************@news.nationwide.ne t:

.CompactDatabase got rid of the pesky no update field...wonder
what had happened to it?

just over use from all my errant test code no doubt

Are there memo fields?

Are you using a good version of Jet? Make sure the version is at
least 4.0.6xxx. If the build is lower than that, you need to
download the latest Jet service pack. Earlier versions had
problems with corrupted indexes, which is very consistent with
the kind of error you were getting.

checking google for how to find version of jet it seems i need to
write some api stuff to get that info?

I right click the file in Windows Explorer and check the version
tab.

is that correct or is there a way to get that from dao?

since the previous post it's happened again, and compacting, while
fixing the 'problem' for the moment also deletes one record
so it's ok for my debugging process but i need to find an actual
fix before using this app in production.

I use code to detect and log Jet version in my Access apps, because
at one time it was easy for machines to revert to bad versions. But
since the Windows Updates for Win2K and WinXP got past Jet SP6
(because Jet 4 is an OS component used by Active Directory for its
data store), this hasn't been as much of a problem.

I don't know what happens if you try to install an old version of
Jet on one of the later service packs of Win2K or WinXP. I'd think
that it wouldn't allow it, but you'd want to check (it's in the
SYSTEM32 folder under Windows, msjet40.dll).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/