Auto Number Problem

[Tony Toews [MVP]]

Access Data Projects never had this problem

we have seed and increment

and identity insert statements

seriously you guys are f***ing idiots for still using MDB for anything

Note that this person is really A a r o n K e m p f and that he is not an employee
of Microsoft.

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

 

[Todos Menos [MSFT]]

Note that Tony is a MDB _WUSS_ and doesn't deserve to be working with
databases

anyone, anywhere-- using MDB for anything-- should be FIRED and then
SPIT UPON



MDB isn't reliable enough for real world usage, let alone newbies


if you're new to Access and you want a BETTER PRODUCT then grow into
Access Data Projects

 

[Guest]

Why do we pay for then? Do we pay for dreams? :O

I really like that phrase and thought.

(david)

 

[Linda]

This is an additional comment. I created a table with an auto number.

I appended several of the record to another table.

Then I appended those records back to the original table and Access allowed duplicate autonumbers.

What's up with that?

It did not prevent the append. I did not create a primary key on the field. That would have prevented it from being appended, but I thought it would create another number.



EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Linda]

That's what I ended up doing. When a person reinstates a record I do not include the previous autonumber, but allow the original table to generate another number instead.

That seems to be the best work around.

I allow the user to see the autonumber because I am using it to identify records in a bank statement file. Bank data that we receive is notoriously un-normalized.

Then they use the autonumber when they find the deposit that posted to our General Ledger. It's being used as a crossreference.

Linda

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[John W. Vinson]

This is an additional comment. I created a table with an auto number.

I appended several of the record to another table.

Then I appended those records back to the original table and Access allowed duplicate autonumbers.

What's up with that?

It did not prevent the append. I did not create a primary key on the field. That would have prevented it from being appended, but I thought it would create another number.

An Append query is the *only* way to put data into an Autonumber field (and
it's often useful, say for merging tables). "This behavior is by design" is
what the Microsofties would say.

If you want to have appended records assigned new autonumbers, just don't
include the autonumber field in the Append query; all the fields you do
include will get appended, but the autonumber (not mentioned in the query at
all) will increment as normal.

John W. Vinson [MVP]

 

[John W. Vinson]

That's what I ended up doing. When a person reinstates a record I do not include the previous autonumber, but allow the original table to generate another number instead.

That seems to be the best work around.

I allow the user to see the autonumber because I am using it to identify records in a bank statement file. Bank data that we receive is notoriously un-normalized.

Then they use the autonumber when they find the deposit that posted to our General Ledger. It's being used as a crossreference.

Just beware: an autonumber makes each record unique, even if it is in fact a
duplicate. You'll need some sort of controls to make sure that you don't enter
the same deposit or withdrawal twice. Not trivial, of course, since it's quite
possible that somebody withdrew $60 from the ATM in the morning, and then $60
more in the afternoon; if you're just recording a date, they'd look identical
but would in fact be different transactions!

John W. Vinson [MVP]

 

[Guest]

The autonumber property, by itself, is just a way of
automatically generating a default value for a field.

(david)

This is an additional comment. I created a table with an auto number.

I appended several of the record to another table.

Then I appended those records back to the original table and Access allowed duplicate autonumbers.

What's up with that?

It did not prevent the append. I did not create a primary key on the

field. That would have prevented it from being appended, but I thought it
would create another number.

 

[Todos Menos [MSFT]]

in Access Data Projects you have the ability to control how you're
inserted autonumber fields.

you can either allow it or disallow it

set identity_insert tablename on


this type of behaivor isn't possible in MDB-- yet another reason not
to use MDB

 

[Tony Toews [MVP]]

in Access Data Projects you have the ability to control how you're
inserted autonumber fields.

you can either allow it or disallow it

set identity_insert tablename on


this type of behaivor isn't possible in MDB-- yet another reason not
to use MDB

Note that this person is really A a r o n K e m p f and that he is not an employee
of Microsoft. His answer was also totally irrelevant to your question.

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

 

[Todos Menos [MSFT]]

my answer was 100% helpful and 100% what needed to be said

you can use SET IDENTITY_INSERT TABLENAME ON and then you can
overwrite autonumbers; or you can turn it off and it won't allow it

either way-- MDB is for chipmunks