Continuing Autonumber from where I left off

[Jonathan H.]

Using Access 2003. I have donor ID numbers in a field set by Autonumber
(incremental). Perhaps this isn't the best way to do it but it worked fine
for 10 years until I made a bad import which I easily corrected except the
autonumber is now using 8 digit numbers instead of the 5 digit number where I
had left off.

I looked through previous postings and they said Autonumber could be reset
by compacting the database, but the data in the table must be deleted first.
I need to keep my data, and I can't change previous IDs because donors know
them and reference them. I'd rather not put the IDs into a different field
because I have many queries and reports which refer to that field. Is there a
way I can have the Autonumber continue from the 5 digit number where it was
before my bad import? I tried deleting the few records I had entered that had
the 8 digit numbers assigned to them, and then compacting, but that didn't
work, as I suspected.

 

[Pat Hartman]

You would need to delete any records that have the high value autonumbers.
1. turn off the name auto correct feature if it isn't already off since this
will interfere with renaming.
2. remove any RI from the table you are rebuilding.
3. rename the table
4. create a new table with the identical structure.
5. create an append query to copy the rows from the renamed table to the new
table.
6. reestablish RI
7. turn on name auto correct if you like to live on the edge.

Another option is to search the kb for code to seed/reseed the autonumber.
This will still require that you get rid of the high numbered values.

 

[Jeff Boyce]

Jonathan

Pat has provided a way for you to do what you're asking about.

Now, despite your constraints, you need to understand that the Access
Autonumber data type is NOT guaranteed to be sequential. It's intended use
is as a unique row identifier, and it is probably not fit for human
consumption.

An alternate approach to your situation would be to use a new field into
which you copy (i.e., append query) the values in the Autonumber field (so
that current users can still refer to this number). But then you'd modify
your sequential numbering process to work, as a home-grown procedure, to
ensure that the new field (NOT an autonumber) is properly sequential.

Yes, you'd have to make changes to some of your queries, to have them point
at the new SeqNumber field. But you (and your users) will find that
sticking with the Autonumber will end up with more maintenance needs in the
future...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jamie Collins]

you need to understand that the Access
Autonumber data type is NOT guaranteed to be sequential. It's intended use
is as a unique row identifier

Read what Pat Hartman wrote in this thread: "reseed the autonumber
[but] This will still require that you get rid of the high numbered
values." Why is it necessary to rid of the high numbered values?
Because autonumber functionality (Jet rather than Access, BTW) does
not test whether auto-generated values are unique: if those high
numbered values weren't removed then they would at some point be
duplicated.

A quick example (ANSI-92 Query Mode syntax):

CREATE TABLE TestSeed (
ID INTEGER IDENTITY (1, 1),
data_col VARCHAR(5)
)
;
INSERT INTO TestSeed (data_col) VALUES ('One')
;
INSERT INTO TestSeed (data_col) VALUES ('Two')
;
INSERT INTO TestSeed (data_col) VALUES ('Three')
;
ALTER TABLE TestSeed ALTER
COLUMN ID INTEGER IDENTITY (1, 1)
;
INSERT INTO TestSeed (data_col) VALUES ('Four')
;
INSERT INTO TestSeed (data_col) VALUES ('Five')
;
INSERT INTO TestSeed (data_col) VALUES ('Six')
;
SELECT ID, data_col
FROM TestSeed
;

The values in the ID column have been duplicated because the
autonumber has been reseeded back to 1.

Here's the VBA:

Sub AutoDup()
' Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
..Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
Dim SQL As String
SQL = _
"CREATE TABLE TestSeed (" & vbCr & " ID" & _
" INTEGER IDENTITY (1, 1), " & vbCr & " " & _
" data_col VARCHAR(5)" & vbCr & ")"
..Execute SQL
SQL = _
"INSERT INTO TestSeed (data_col)" & _
" VALUES ('One')"
..Execute SQL
SQL = _
"INSERT INTO TestSeed (data_col)" & _
" VALUES ('Two')"
..Execute SQL
SQL = _
"INSERT INTO TestSeed (data_col)" & _
" VALUES ('Three')"
..Execute SQL
SQL = _
"ALTER TABLE TestSeed ALTER " & vbCr & " " & _
" COLUMN ID INTEGER IDENTITY (1," & _
" 1)"
..Execute SQL
SQL = _
"INSERT INTO TestSeed (data_col)" & _
" VALUES ('Four')"
..Execute SQL
SQL = _
"INSERT INTO TestSeed (data_col)" & _
" VALUES ('Five')"
..Execute SQL
SQL = _
"INSERT INTO TestSeed (data_col)" & _
" VALUES ('Six')"
..Execute SQL
SQL = _
"SELECT ID, data_col " & vbCr & "FROM TestSeed;"
Dim rs
Set rs = .Execute(SQL)
MsgBox rs.GetString
End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--

 

[Pat Hartman]

The reseed is an artificial override and Jet expects you to know what you
are doing or you shouldn't be using it. Ant that is why it wasn't my first
recommendation even though it is procedurally simpler. Here is a link to
the reseed code - http://support.microsoft.com/kb/287756 - but it doesn't
give you the appropriate warning about not setting the seed lower than the
highest value of any existing record.

you need to understand that the Access
Autonumber data type is NOT guaranteed to be sequential. It's intended
use
is as a unique row identifier

Read what Pat Hartman wrote in this thread: "reseed the autonumber
[but] This will still require that you get rid of the high numbered
values." Why is it necessary to rid of the high numbered values?
Because autonumber functionality (Jet rather than Access, BTW) does
not test whether auto-generated values are unique: if those high
numbered values weren't removed then they would at some point be
duplicated.

A quick example (ANSI-92 Query Mode syntax):

CREATE TABLE TestSeed (
ID INTEGER IDENTITY (1, 1),
data_col VARCHAR(5)
)
;
INSERT INTO TestSeed (data_col) VALUES ('One')
;
INSERT INTO TestSeed (data_col) VALUES ('Two')
;
INSERT INTO TestSeed (data_col) VALUES ('Three')
;
ALTER TABLE TestSeed ALTER
COLUMN ID INTEGER IDENTITY (1, 1)
;
INSERT INTO TestSeed (data_col) VALUES ('Four')
;
INSERT INTO TestSeed (data_col) VALUES ('Five')
;
INSERT INTO TestSeed (data_col) VALUES ('Six')
;
SELECT ID, data_col
FROM TestSeed
;

The values in the ID column have been duplicated because the
autonumber has been reseeded back to 1.

Here's the VBA:

Sub AutoDup()
' Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
Dim SQL As String
SQL = _
"CREATE TABLE TestSeed (" & vbCr & " ID" & _
" INTEGER IDENTITY (1, 1), " & vbCr & " " & _
" data_col VARCHAR(5)" & vbCr & ")"
.Execute SQL
SQL = _
"INSERT INTO TestSeed (data_col)" & _
" VALUES ('One')"
.Execute SQL
SQL = _
"INSERT INTO TestSeed (data_col)" & _
" VALUES ('Two')"
.Execute SQL
SQL = _
"INSERT INTO TestSeed (data_col)" & _
" VALUES ('Three')"
.Execute SQL
SQL = _
"ALTER TABLE TestSeed ALTER " & vbCr & " " & _
" COLUMN ID INTEGER IDENTITY (1," & _
" 1)"
.Execute SQL
SQL = _
"INSERT INTO TestSeed (data_col)" & _
" VALUES ('Four')"
.Execute SQL
SQL = _
"INSERT INTO TestSeed (data_col)" & _
" VALUES ('Five')"
.Execute SQL
SQL = _
"INSERT INTO TestSeed (data_col)" & _
" VALUES ('Six')"
.Execute SQL
SQL = _
"SELECT ID, data_col " & vbCr & "FROM TestSeed;"
Dim rs
Set rs = .Execute(SQL)
MsgBox rs.GetString
End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

 

[Jamie Collins]

The reseed is an artificial override

Why do you say 'artificial'?

Jet expects you to know what you
are doing or you shouldn't be using it.

Anthropomorphism <g>!

In case you missed my point: just as there is nothing inherent in
autonumber functionality that guarantees sequential values (especially
if you've chosen to use the random flavour <g>), there is nothing
inherent in autonumber functionality that guarantees unique values.

Here's another KB article on the subject of reseeding autonumbers,
this one using SQL DDL:

ACC2000: Jet IDENTITY Datatype Seed and Increment Reset to 1
http://support.microsoft.com/kb/202117

Jamie.

--

 

[Pat Hartman]

I don't think the OP was worried about "sequential" numbers, he just didn't
want numbers that were not so large. I am not arguing with you. I was
simply pointing out that the help entry for seeding the autonumber didn't
offer any warnings and it should have so I complained about it to MS again-
rate those help entries folks - that is the only way they will get better.

And I don't care what you think, Jet is ALIVE!!!! <g>

 

[Jamie Collins]

I don't think the OP was worried about "sequential" numbers

Perhaps not but Jeff Boyce Microsoft Office/Access MVP was. He also
suggested that autonumbers are intended to be unique (paraphrasing),
which was the point I was addressing.

I am not arguing with you.

I didn't think you were but thanks

I complained about it to MS again-
rate those help entries folks - that is the only way they will get better.

I'm cynical about the process myself. I've given feedback on the same
articles literally for years and they haven't changed. That's why I
raise the subject with any MVP willing to listen <g>.

Perhaps we can use people power to some effect, though. Someone with
greater influence around here than me could mount a campaign to get a
certain page corrected. FWIW my choice would be the following page,
which contains a number of omissions and oddities which EITHER
(probably) amount to bizarre technical authorship and/or appallingly
bad editorship OR (less likely but more intriguing) provide insight to
features which exist in the engine but which have not been exposed
(DAO, DDL, OLE DB provider setting, registry key) either by accident
or design:

CREATE TABLE Statement
http://office.microsoft.com/en-gb/access/HA012314411033.aspx

1) "When a TEMPORARY table is created it is visible only within the
session in which it was created. It is automatically deleted when the
session is terminated. Temporary tables can be accessed by more than
one user."

That's quite a lot of detail for something that does not exist!
Creating and dropping permanent tables causes file bloat, so if there
is indeed TEMPORARY table functionality in the engine, can the Access
team please fix the DDL so that we can utilise it.

2) "You can use NOT NULL on a single field or within a named
CONSTRAINT clause that applies to either a single field or to a
multiple-field named CONSTRAINT."

I think the sentence should read simply, "You can use NOT NULL on a
single field." In Jet (and all other SQL syntaxes in my experience,
including Standard SQL) a NOT NULL clause is not a CONSTRAINT, a NOT
NULL clause cannot be named and a multiple-field NOT NULL clause is
not supported.

3) "CONSTRAINT multifieldindex [, ...]"

No mention of CHECK constraints. There is no separate page in the Help
for CHECK constraints therefore the CREATE TABLE page would be the
most appropriate place for it, as is the case with NOT NULL clauses
which do appear here. The above syntax definition and other parts of
the article are also misleading IMO because they equate CONSTRAINT
with INDEX, whereas a CHECK constraint is not implemented as an index.

4) No mention of DEFAULT clauses, again analogous to NOT NULL clauses
which do appear here.

5) "If NOT NULL is specified for a field, then new records are
required to have valid data in that field."

Minor quibble here but surely "valid data" is ensured by all integrity
constraints e.g. validation rules etc?

Obviously, this is just one page as an example. If you are looking for
an ally in your campaign for better documentation, count me in

Jamie.

--

 

[Jamie Collins]

I complained about it to MS again-
rate those help entries folks - that is the only way they will get better.

And I don't care what you think, Jet is ALIVE!!!! <g>

Was that last comment aimed at MS?

Windows DAC/MDAC Deprecated Components
http://msdn2.microsoft.com/en-us/library/ms692882.aspx

"Microsoft has deprecated the Microsoft Jet Engine, and plans no new
releases or service packs for this component."

Jamie.

--

 

[Pat Hartman]

How astute

Was that last comment aimed at MS?

Windows DAC/MDAC Deprecated Components
http://msdn2.microsoft.com/en-us/library/ms692882.aspx

"Microsoft has deprecated the Microsoft Jet Engine, and plans no new
releases or service packs for this component."

Jamie.