AddNew fails - uses existing autonumber

[Rob Parker]

I have the following section of code (and several other similar ones, which
all work fine):

Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("qryInspAccess_Current")
qdf.Parameters("[CardID]") = Me.InspCardID
Set rst = qdf.OpenRecordset(, dbOpenForwardOnly)
Set rstSub = dbs.OpenRecordset("RWOP_DAT_InspAccess", dbOpenDynaset,
dbAppendOnly)
Do Until rst.EOF
rstSub.AddNew
rstSub!AccessID = rst!AccessItemID
rstSub!InspectionID = Me.InspID
rstSub.Update
rst.MoveNext
Loop

The problem is that the .AddNew statement generates error 3022 (changes not
successful, duplicate values ...). The problem is that the new record in
rstSub is being generated using an existing number for the InspAccessID, an
autonumber field in RWOP_DAT_InspAccess.

I get the same problem if I open the RWOP_DAT_InspAccess query and try to
add a new record in the query - it shows an existing autonumber field, and
fails with the same error message. I get the same problem if I try to add a
new record directly in the linked table which is the basis of the RWOP
query. And finally, I get the same problem if I try to add a new record
directly into the table in the back-end .mbd file. I've run a
compact/repair on that file, but it's had no effect. How can I fix this?
And, to prevent possible future recurrence of this, what could have caused
it?

TIA,

Rob

 

[Rob Parker]

I found a cure in the KB article 884185, at
http://support.microsoft.com/kb/884185/en-us

However, I'm a little puzzled as to why this occurred, since the only
condition I know which matched the Cause section there was that the database
had been compacted. Is it true that compacting a back-end database may
cause this problem?

Possibly related: I've also noticed that append operations carried out by
code such as:
strSQL = "INSERT INTO ..."
DoCmd.RunSQL strSQL
have been appending records using autonumbers within the current set (those
which were initially used for records which have subsequently been deleted).
This is happening in a different table than the one I referred to in my
original post. Is this normal, or am I about to be bitten again?

FWIW, I'm using Jet 4.00.8618.0 (from Windows XP SP2). This is a later
version than the one referred to in the KB article.

Again, any comments welcome,

Rob

 

[DAVID]

You get this problem when you append to an autonumber
field, and are using Jet SP8+.

Evidently there is some workaround in A2007: I didn't
follow that.

It is 'normal' for this to happen if you are using
Jet SP8+

You have already been bitten again, with the other
table, you just haven't gotten sick yet. In the
normal course of events, your autonumbers will advance
until they clash.


I think the 4 conditions in the 'cause' section of
the kb article are all requirements.

There are other ways to get similar problems, but
they all relate to earlier versions of Jet 4.0.
This behaviour is predicable and repeatable: it
took them 8 tries to get even this good, and there
is no suggestion that they are going to tempt fate
by trying to fix it some more.

I found a cure in the KB article 884185, at
http://support.microsoft.com/kb/884185/en-us

However, I'm a little puzzled as to why this occurred, since the only
condition I know which matched the Cause section there was that the database
had been compacted. Is it true that compacting a back-end database may
cause this problem?

Possibly related: I've also noticed that append operations carried out by
code such as:
strSQL = "INSERT INTO ..."
DoCmd.RunSQL strSQL
have been appending records using autonumbers within the current set (those
which were initially used for records which have subsequently been deleted).
This is happening in a different table than the one I referred to in my
original post. Is this normal, or am I about to be bitten again?

FWIW, I'm using Jet 4.00.8618.0 (from Windows XP SP2). This is a later
version than the one referred to in the KB article.

Again, any comments welcome,

Rob

I have the following section of code (and several other similar ones, which
all work fine):

Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("qryInspAccess_Current")
qdf.Parameters("[CardID]") = Me.InspCardID
Set rst = qdf.OpenRecordset(, dbOpenForwardOnly)
Set rstSub = dbs.OpenRecordset("RWOP_DAT_InspAccess", dbOpenDynaset,
dbAppendOnly)
Do Until rst.EOF
rstSub.AddNew
rstSub!AccessID = rst!AccessItemID
rstSub!InspectionID = Me.InspID
rstSub.Update
rst.MoveNext
Loop

The problem is that the .AddNew statement generates error 3022 (changes
not successful, duplicate values ...). The problem is that the new record
in rstSub is being generated using an existing number for the
InspAccessID, an autonumber field in RWOP_DAT_InspAccess.

I get the same problem if I open the RWOP_DAT_InspAccess query and try to
add a new record in the query - it shows an existing autonumber field, and
fails with the same error message. I get the same problem if I try to add
a new record directly in the linked table which is the basis of the RWOP
query. And finally, I get the same problem if I try to add a new record
directly into the table in the back-end .mbd file. I've run a
compact/repair on that file, but it's had no effect. How can I fix this?
And, to prevent possible future recurrence of this, what could have caused
it?

TIA,

Rob

 

[Rob Parker]

Thanks for the comments, David.

I'll run the DDL query against my other table where I've seen strange
behaviour.

I'm still concerned as to why this happened. In my situation, the last two
conditions in the KB article were certainly NOT met; the only one I know was
met was the compact operation, the second one (that the autonumber was
incorrectly seeded) was true - that's why I got the errors - but my main
concern is how to avoid it in future.

Surely it's not the case that you cannot compact a back-end .mdb file (with
a later version of Jet than is reported to cause the problem) without this
occurring?

Rob

You get this problem when you append to an autonumber
field, and are using Jet SP8+.

Evidently there is some workaround in A2007: I didn't
follow that.

It is 'normal' for this to happen if you are using
Jet SP8+

You have already been bitten again, with the other
table, you just haven't gotten sick yet. In the
normal course of events, your autonumbers will advance
until they clash.


I think the 4 conditions in the 'cause' section of
the kb article are all requirements.

There are other ways to get similar problems, but
they all relate to earlier versions of Jet 4.0.
This behaviour is predicable and repeatable: it
took them 8 tries to get even this good, and there
is no suggestion that they are going to tempt fate
by trying to fix it some more.

I found a cure in the KB article 884185, at
http://support.microsoft.com/kb/884185/en-us

However, I'm a little puzzled as to why this occurred, since the only
condition I know which matched the Cause section there was that the
database had been compacted. Is it true that compacting a back-end
database may cause this problem?

Possibly related: I've also noticed that append operations carried out
by code such as:
strSQL = "INSERT INTO ..."
DoCmd.RunSQL strSQL
have been appending records using autonumbers within the current set
(those which were initially used for records which have subsequently been
deleted). This is happening in a different table than the one I referred
to in my original post. Is this normal, or am I about to be bitten
again?

FWIW, I'm using Jet 4.00.8618.0 (from Windows XP SP2). This is a later
version than the one referred to in the KB article.

Again, any comments welcome,

Rob

I have the following section of code (and several other similar ones,
which all work fine):

Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("qryInspAccess_Current")
qdf.Parameters("[CardID]") = Me.InspCardID
Set rst = qdf.OpenRecordset(, dbOpenForwardOnly)
Set rstSub = dbs.OpenRecordset("RWOP_DAT_InspAccess", dbOpenDynaset,
dbAppendOnly)
Do Until rst.EOF
rstSub.AddNew
rstSub!AccessID = rst!AccessItemID
rstSub!InspectionID = Me.InspID
rstSub.Update
rst.MoveNext
Loop

The problem is that the .AddNew statement generates error 3022 (changes
not successful, duplicate values ...). The problem is that the new
record in rstSub is being generated using an existing number for the
InspAccessID, an autonumber field in RWOP_DAT_InspAccess.

I get the same problem if I open the RWOP_DAT_InspAccess query and try
to add a new record in the query - it shows an existing autonumber
field, and fails with the same error message. I get the same problem if
I try to add a new record directly in the linked table which is the
basis of the RWOP query. And finally, I get the same problem if I try
to add a new record directly into the table in the back-end .mbd file.
I've run a compact/repair on that file, but it's had no effect. How can
I fix this? And, to prevent possible future recurrence of this, what
could have caused it?

TIA,

Rob

 

[Paul Shapiro]

I have only seen this happen when you insert data and specify a value for
the autonumber field. The autonumber is then reset to be the next-higher
integer. Compacting used to fix this, but no longer does. Allen Browne has a
discussion and code on his website to loop through the tables re-setting the
next autonumber to be 1 higher than the max current data value. Or if you
know what value to use, you can insert a record with a suitable autonumber
value and then delete that record. The next new record will be 1 higher than
that.
Paul Shapiro

Thanks for the comments, David.

I'll run the DDL query against my other table where I've seen strange
behaviour.

I'm still concerned as to why this happened. In my situation, the last
two conditions in the KB article were certainly NOT met; the only one I
know was met was the compact operation, the second one (that the
autonumber was incorrectly seeded) was true - that's why I got the
errors - but my main concern is how to avoid it in future.

Surely it's not the case that you cannot compact a back-end .mdb file
(with a later version of Jet than is reported to cause the problem)
without this occurring?

Rob

You get this problem when you append to an autonumber
field, and are using Jet SP8+.

Evidently there is some workaround in A2007: I didn't
follow that.

It is 'normal' for this to happen if you are using
Jet SP8+

You have already been bitten again, with the other
table, you just haven't gotten sick yet. In the
normal course of events, your autonumbers will advance
until they clash.


I think the 4 conditions in the 'cause' section of
the kb article are all requirements.

There are other ways to get similar problems, but
they all relate to earlier versions of Jet 4.0.
This behaviour is predicable and repeatable: it
took them 8 tries to get even this good, and there
is no suggestion that they are going to tempt fate
by trying to fix it some more.

I found a cure in the KB article 884185, at
http://support.microsoft.com/kb/884185/en-us

However, I'm a little puzzled as to why this occurred, since the only
condition I know which matched the Cause section there was that the
database had been compacted. Is it true that compacting a back-end
database may cause this problem?

Possibly related: I've also noticed that append operations carried out
by code such as:
strSQL = "INSERT INTO ..."
DoCmd.RunSQL strSQL
have been appending records using autonumbers within the current set
(those which were initially used for records which have subsequently
been deleted). This is happening in a different table than the one I
referred to in my original post. Is this normal, or am I about to be
bitten again?

FWIW, I'm using Jet 4.00.8618.0 (from Windows XP SP2). This is a later
version than the one referred to in the KB article.

Again, any comments welcome,

Rob


message I have the following section of code (and several other similar ones,
which all work fine):

Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("qryInspAccess_Current")
qdf.Parameters("[CardID]") = Me.InspCardID
Set rst = qdf.OpenRecordset(, dbOpenForwardOnly)
Set rstSub = dbs.OpenRecordset("RWOP_DAT_InspAccess", dbOpenDynaset,
dbAppendOnly)
Do Until rst.EOF
rstSub.AddNew
rstSub!AccessID = rst!AccessItemID
rstSub!InspectionID = Me.InspID
rstSub.Update
rst.MoveNext
Loop

The problem is that the .AddNew statement generates error 3022 (changes
not successful, duplicate values ...). The problem is that the new
record in rstSub is being generated using an existing number for the
InspAccessID, an autonumber field in RWOP_DAT_InspAccess.

I get the same problem if I open the RWOP_DAT_InspAccess query and try
to add a new record in the query - it shows an existing autonumber
field, and fails with the same error message. I get the same problem
if I try to add a new record directly in the linked table which is the
basis of the RWOP query. And finally, I get the same problem if I try
to add a new record directly into the table in the back-end .mbd file.
I've run a compact/repair on that file, but it's had no effect. How
can I fix this? And, to prevent possible future recurrence of this,
what could have caused it?

 

[Rob Parker]

Thanks for that, Paul.

I think you've nailed the cause for me. I did a manual fix for some
accidentally deleted data a couple of days ago, which included restoring
some old records, complete with their autonumbers, via an insert query; did
it that way since the old number was still present as foreign key in other
tables. Didn't realize that would reseed the autonumber.

And I can now rest easy regarding compacting the back-end file.

Rob

I have only seen this happen when you insert data and specify a value for
the autonumber field. The autonumber is then reset to be the next-higher
integer. Compacting used to fix this, but no longer does. Allen Browne has
a discussion and code on his website to loop through the tables re-setting
the next autonumber to be 1 higher than the max current data value. Or if
you know what value to use, you can insert a record with a suitable
autonumber value and then delete that record. The next new record will be 1
higher than that.
Paul Shapiro

Thanks for the comments, David.

I'll run the DDL query against my other table where I've seen strange
behaviour.

I'm still concerned as to why this happened. In my situation, the last
two conditions in the KB article were certainly NOT met; the only one I
know was met was the compact operation, the second one (that the
autonumber was incorrectly seeded) was true - that's why I got the
errors - but my main concern is how to avoid it in future.

Surely it's not the case that you cannot compact a back-end .mdb file
(with a later version of Jet than is reported to cause the problem)
without this occurring?

Rob

You get this problem when you append to an autonumber
field, and are using Jet SP8+.

Evidently there is some workaround in A2007: I didn't
follow that.

It is 'normal' for this to happen if you are using
Jet SP8+

You have already been bitten again, with the other
table, you just haven't gotten sick yet. In the
normal course of events, your autonumbers will advance
until they clash.


I think the 4 conditions in the 'cause' section of
the kb article are all requirements.

There are other ways to get similar problems, but
they all relate to earlier versions of Jet 4.0.
This behaviour is predicable and repeatable: it
took them 8 tries to get even this good, and there
is no suggestion that they are going to tempt fate
by trying to fix it some more.

Rob Parker wrote:
I found a cure in the KB article 884185, at
http://support.microsoft.com/kb/884185/en-us

However, I'm a little puzzled as to why this occurred, since the only
condition I know which matched the Cause section there was that the
database had been compacted. Is it true that compacting a back-end
database may cause this problem?

Possibly related: I've also noticed that append operations carried out
by code such as:
strSQL = "INSERT INTO ..."
DoCmd.RunSQL strSQL
have been appending records using autonumbers within the current set
(those which were initially used for records which have subsequently
been deleted). This is happening in a different table than the one I
referred to in my original post. Is this normal, or am I about to be
bitten again?

FWIW, I'm using Jet 4.00.8618.0 (from Windows XP SP2). This is a later
version than the one referred to in the KB article.

Again, any comments welcome,

Rob


message I have the following section of code (and several other similar ones,
which all work fine):

Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("qryInspAccess_Current")
qdf.Parameters("[CardID]") = Me.InspCardID
Set rst = qdf.OpenRecordset(, dbOpenForwardOnly)
Set rstSub = dbs.OpenRecordset("RWOP_DAT_InspAccess", dbOpenDynaset,
dbAppendOnly)
Do Until rst.EOF
rstSub.AddNew
rstSub!AccessID = rst!AccessItemID
rstSub!InspectionID = Me.InspID
rstSub.Update
rst.MoveNext
Loop

The problem is that the .AddNew statement generates error 3022
(changes not successful, duplicate values ...). The problem is that
the new record in rstSub is being generated using an existing number
for the InspAccessID, an autonumber field in RWOP_DAT_InspAccess.

I get the same problem if I open the RWOP_DAT_InspAccess query and try
to add a new record in the query - it shows an existing autonumber
field, and fails with the same error message. I get the same problem
if I try to add a new record directly in the linked table which is the
basis of the RWOP query. And finally, I get the same problem if I try
to add a new record directly into the table in the back-end .mbd file.
I've run a compact/repair on that file, but it's had no effect. How
can I fix this? And, to prevent possible future recurrence of this,
what could have caused it?

 

[DAVID]

Surely it's not the case that you cannot compact a back-end

Only a problem if you have an older version of Jet.
You should make sure all users have SP8.

With SP8, the problem only happens if you insert a value
into an autonumber field. If you have not inserted a value
into an autonumber field, you need to check the Jet version
for all users.

(david)

Thanks for the comments, David.

I'll run the DDL query against my other table where I've seen strange
behaviour.

I'm still concerned as to why this happened. In my situation, the last two
conditions in the KB article were certainly NOT met; the only one I know was
met was the compact operation, the second one (that the autonumber was
incorrectly seeded) was true - that's why I got the errors - but my main
concern is how to avoid it in future.

Surely it's not the case that you cannot compact a back-end .mdb file (with
a later version of Jet than is reported to cause the problem) without this
occurring?

Rob

You get this problem when you append to an autonumber
field, and are using Jet SP8+.

Evidently there is some workaround in A2007: I didn't
follow that.

It is 'normal' for this to happen if you are using
Jet SP8+

You have already been bitten again, with the other
table, you just haven't gotten sick yet. In the
normal course of events, your autonumbers will advance
until they clash.


I think the 4 conditions in the 'cause' section of
the kb article are all requirements.

There are other ways to get similar problems, but
they all relate to earlier versions of Jet 4.0.
This behaviour is predicable and repeatable: it
took them 8 tries to get even this good, and there
is no suggestion that they are going to tempt fate
by trying to fix it some more.

I found a cure in the KB article 884185, at
http://support.microsoft.com/kb/884185/en-us

However, I'm a little puzzled as to why this occurred, since the only
condition I know which matched the Cause section there was that the
database had been compacted. Is it true that compacting a back-end
database may cause this problem?

Possibly related: I've also noticed that append operations carried out
by code such as:
strSQL = "INSERT INTO ..."
DoCmd.RunSQL strSQL
have been appending records using autonumbers within the current set
(those which were initially used for records which have subsequently been
deleted). This is happening in a different table than the one I referred
to in my original post. Is this normal, or am I about to be bitten
again?

FWIW, I'm using Jet 4.00.8618.0 (from Windows XP SP2). This is a later
version than the one referred to in the KB article.

Again, any comments welcome,

Rob


message I have the following section of code (and several other similar ones,
which all work fine):

Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("qryInspAccess_Current")
qdf.Parameters("[CardID]") = Me.InspCardID
Set rst = qdf.OpenRecordset(, dbOpenForwardOnly)
Set rstSub = dbs.OpenRecordset("RWOP_DAT_InspAccess", dbOpenDynaset,
dbAppendOnly)
Do Until rst.EOF
rstSub.AddNew
rstSub!AccessID = rst!AccessItemID
rstSub!InspectionID = Me.InspID
rstSub.Update
rst.MoveNext
Loop

The problem is that the .AddNew statement generates error 3022 (changes
not successful, duplicate values ...). The problem is that the new
record in rstSub is being generated using an existing number for the
InspAccessID, an autonumber field in RWOP_DAT_InspAccess.

I get the same problem if I open the RWOP_DAT_InspAccess query and try
to add a new record in the query - it shows an existing autonumber
field, and fails with the same error message. I get the same problem if
I try to add a new record directly in the linked table which is the
basis of the RWOP query. And finally, I get the same problem if I try
to add a new record directly into the table in the back-end .mbd file.
I've run a compact/repair on that file, but it's had no effect. How can
I fix this? And, to prevent possible future recurrence of this, what
could have caused it?

TIA,

Rob