SQL Insert, Autonumber, and End of Table ???

[petebeatty]

I have created a SQL string the properly inserts a record in the table.
However, the insert does not occur at the end of the table. Instead
it inserts a record after the last record that I viewed. This would be
OK, except it assigns a autonumber to be one greater than the last
viewed record. This causes a duplicate autonumber. I know I can
change the autonumber index (Primary Key) to not allow duplicates.

How can I force the insert to use the highest autonumber + 1? I would
think that this should be handled automatically.

[Br]

pe********@comcast.net wrote:

I have created a SQL string the properly inserts a record in the
table. However, the insert does not occur at the end of the table.
Instead it inserts a record after the last record that I viewed.
This would be OK, except it assigns a autonumber to be one greater
than the last viewed record. This causes a duplicate autonumber. I
know I can change the autonumber index (Primary Key) to not allow
duplicates.

How can I force the insert to use the highest autonumber + 1? I would
think that this should be handled automatically.

Without seeing your code/script or knowing the sequence of events it's hard
to know what the issue is.

You talk about autonumber... is it actually an autonumber field or are you
manually creating your own? There is no way an real autonumber type field
will create a duplicate.

Either make is a real autonumner field, or insert Max([MyField]) + 1 as the
new value.
--
regards,

Br@dley

[petebeatty]

This is the true autonumber field created by access. I have not changed
it.

Does the "Insert Into" Command append the record at the bottom of the
file, or does it insert anywhere in the file?

[petebeatty]

This is the true autonumber field created by access. I have not changed
it.

Does the "Insert Into" Command append the record at the bottom of the
file, or does it insert anywhere in the file?

[Br]

pe********@comcast.net wrote:

This is the true autonumber field created by access. I have not
changed it.

Does the "Insert Into" Command append the record at the bottom of the
file, or does it insert anywhere in the file?

Tried the help file?

"If you append records to a table with an AutoNumber field and you want to
renumber the appended records, do not include the AutoNumber field in your
query. Do include the AutoNumber field in the query if you want to retain
the original values from the field."

--
regards,

Br@dley

[petebeatty]

I started with the help file. I understand the help data. However,
the issue still exists.
the sql is "Insert into table ( name, address, city ) values ( "XXX",
"YYY", "ZZZ" ).

This command does not use the AUTONUMBER field, called (RcdNumber). It
does not append with a new higher autonumber value. It does replicate
and existing number.

[Terry Kreft]

There's something wrong with your db try compact and repair and see if that
fixes it.

--

Terry Kreft
<pe********@comcast.net> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...

I started with the help file. I understand the help data. However,
the issue still exists.
the sql is "Insert into table ( name, address, city ) values ( "XXX",
"YYY", "ZZZ" ).

This command does not use the AUTONUMBER field, called (RcdNumber). It
does not append with a new higher autonumber value. It does replicate
and existing number.

[Tom van Stiphout]

On Fri, 3 Mar 2006 12:40:34 +1100, "Br@dley" <[e-mail]> wrote:

And put that primary key back! Your "business rule" obviously is that
you don't want duplicates. So let the db engine enforce that rule. If
you ever get a "unique index violation" error, 99.9999% chance it's
your programming error, and an opportunity to improve your code.

-Tom.

pe********@comcast.net wrote:

I have created a SQL string the properly inserts a record in the
table. However, the insert does not occur at the end of the table.
Instead it inserts a record after the last record that I viewed.
This would be OK, except it assigns a autonumber to be one greater
than the last viewed record. This causes a duplicate autonumber. I
know I can change the autonumber index (Primary Key) to not allow
duplicates.

How can I force the insert to use the highest autonumber + 1? I would
think that this should be handled automatically.

Without seeing your code/script or knowing the sequence of events it's hard
to know what the issue is.

You talk about autonumber... is it actually an autonumber field or are you
manually creating your own? There is no way an real autonumber type field
will create a duplicate.

Either make is a real autonumner field, or insert Max([MyField]) + 1 as the
new value.

[petebeatty]

Thanks for all the input. The key as marked as no duplicate. However,
I deleted the key and the field. COmpressed and reapired, rebuilt the
field and index, did another compress and rebuilt. It all works
correctly . It was apparently a database corruption error.