ACCESS: Can I make Autonumber field start with 582 rather than 1.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am designing a system for entering tickets. I want it to be auto number.
Problem is the company is at ticket # 3516, and I wish to start the
autonumber field at that point.
 
Hi Bob,

You will need to enter 3515 records into your table, delete all bar record
number 3515, compact and repair, then delete 3515. The next record (as long
as you don't do another compact/repair) will be 3516.

If you don't know how to enter 3515 records, you could use a for loop in a
code module to execute an insert sql statement.

Hope this helps.

Damian.
 
I created this procedure for resetting autonumbers.

I would like to mention however that autonumbers are not necessarily
always consecutive. If you start to add a record and cancel it then
the next record you attempt to add will have the next autonumber in
the sequence.

You need to add the Reference:
"Microsoft ADO Ext. 2.8 for DDL and Security"

' *******************************************************************
' resestSeed - resets an autonumber field
' *******************************************************************
' tableName - the table that contains the autonumber
' fieldName - the field that is the autonumber
' seedValue - where you want the new autonumber to start
' *******************************************************************
Public Sub resetSeed(tableName As String, fieldName As String,
seedValue As Integer)
Dim cat As New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
cat.Tables(tableName).Columns(fieldName).Properties("Seed") =
seedValue
End Sub

Cheers,
Jason Lepack
 
you can also set the Autonumber with an Append query. just append the number
3515 (and dummy data into any other required fields in the table) into the
table, then delete it. note that if you compact the database *before*
entering a "real" record, the Autonumber will be reset back to one again.

as Jason mentioned, the Autonumber is not a reliable source for consecutive
numbers, and should not be used for the purpose you intend. recommend you
generate the ticket number programmatically instead.

hth
 
autonumbers are not necessarily
always consecutive. If you start to add a record and cancel it then
the next record you attempt to add will have the next autonumber in
the sequence.
Click to expand...

Good point. Also note you can explicitly INSERT values into an
autonumber column that may affect the sequence.
You need to add the Reference:
"MicrosoftADOExt. 2.8 for DDL and Security"
Click to expand...

You can alternatively use ADODB (to which there's a reference by
default <g>) and SQL DDL e.g. (aircode):

CurrentProject.Connection.Execute _
"CREATE TABLE Test (" & _
" ID INTEGER IDENTITY(3516, 1) NOT NULL UNIQUE," & _
" data_col INTEGER);"

Jamie.

--
 
Damian S said:
Hi Bob,

You will need to enter 3515 records into your table, delete all bar record
number 3515, compact and repair, then delete 3515. The next record (as long
as you don't do another compact/repair) will be 3516.

If you don't know how to enter 3515 records, you could use a for loop in a
code module to execute an insert sql statement.

Hope this helps.

Damian.
Click to expand...

Thank you very much......
 
Jason Lepack said:
I created this procedure for resetting autonumbers.

I would like to mention however that autonumbers are not necessarily
always consecutive. If you start to add a record and cancel it then
the next record you attempt to add will have the next autonumber in
the sequence.

You need to add the Reference:
"Microsoft ADO Ext. 2.8 for DDL and Security"

' *******************************************************************
' resestSeed - resets an autonumber field
' *******************************************************************
' tableName - the table that contains the autonumber
' fieldName - the field that is the autonumber
' seedValue - where you want the new autonumber to start
' *******************************************************************
Public Sub resetSeed(tableName As String, fieldName As String,
seedValue As Integer)
Dim cat As New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
cat.Tables(tableName).Columns(fieldName).Properties("Seed") =
seedValue
End Sub

Cheers,
Jason Lepack
Click to expand...
Thank you very much...
 
Jamie Collins said:
Good point. Also note you can explicitly INSERT values into an
autonumber column that may affect the sequence.


You can alternatively use ADODB (to which there's a reference by
default <g>) and SQL DDL e.g. (aircode):

CurrentProject.Connection.Execute _
"CREATE TABLE Test (" & _
" ID INTEGER IDENTITY(3516, 1) NOT NULL UNIQUE," & _
" data_col INTEGER);"

Jamie.
Click to expand...
Thank you very much...
 
tina said:
you can also set the Autonumber with an Append query. just append the number
3515 (and dummy data into any other required fields in the table) into the
table, then delete it. note that if you compact the database *before*
entering a "real" record, the Autonumber will be reset back to one again.

as Jason mentioned, the Autonumber is not a reliable source for consecutive
numbers, and should not be used for the purpose you intend. recommend you
generate the ticket number programmatically instead.

hth
Click to expand...
Thank you very much...
 
BruceM said:
This link shows one way of creating a sequential number, which is probably
what you want.
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb
Click to expand...

"Form1 illustrates how to use the DMax function to simulate the action of
the Autonumber field."

That doesn't sound correct. If you explicitly insert the maximum INTEGER
value into an autonumber column it doesn't interrupt the generated sequence
but would cause a DMAX+1 algorithm to error, so that can't be the correct
algorithm for autonumber.

As a hint of how autonumber actually works in Jet, plus why an autonumber
column should perhaps always be constrained as UNIQUE, try the following VBA
code which uses large (and significant) seed values:

Sub autonum_test()
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
.Execute _
"CREATE TABLE Test1 (" & _
" ID1 INTEGER IDENTITY(1, 1073741824)" & _
" NOT NULL, insert_sequence" & _
" INTEGER);"
.Execute _
"CREATE TABLE Test2 (" & _
" ID2 INTEGER IDENTITY(1, 1073741825)" & _
" NOT NULL, insert_sequence" & _
" INTEGER);"
.Execute _
"CREATE TABLE Digits (nbr INTEGER);"
.Execute _
"INSERT INTO Digits VALUES (0);"
.Execute _
"INSERT INTO Digits (nbr) SELECT DT1.nbr" & _
" FROM (SELECT 1 AS nbr FROM Digits UNION" & _
" ALL SELECT 2 FROM Digits UNION ALL SELECT" & _
" 3 FROM Digits UNION ALL SELECT 4 FROM Digits" & _
" UNION ALL SELECT 5 FROM Digits UNION ALL" & _
" SELECT 6 FROM Digits UNION ALL SELECT 7" & _
" FROM Digits UNION ALL SELECT 8 FROM Digits" & _
" UNION ALL SELECT 9 FROM Digits ) AS DT1;"
.Execute _
"INSERT INTO Test1 (insert_sequence) SELECT" & _
" nbr FROM Digits;"
.Execute _
"INSERT INTO Test2 (insert_sequence) SELECT" & _
" nbr FROM Digits;"
Dim rs
Set rs = .Execute( _
"SELECT DISTINCT 'insert_sequence'," & _
" 'autonumber_repeats', 'autonumber_wraps' FROM" & _
" Test1 AS T1" & vbCr & "UNION ALL SELECT" & _
" T1.insert_sequence & CHR(9)," & _
" T1.ID1 & STRING(IIF(LEN(CSTR(T1.ID1))" & _
" < 6, 2, 1), CHR(9)), T2.ID2 FROM" & _
" Test1 AS T1, Test2 AS T2 WHERE" & _
" T1.insert_sequence= T2.insert_sequence;")

MsgBox rs.GetString
End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--
 
did you look at the example database? it doesn't use an Autonumber field,
but rather a Long Integer field. the point of the code, and the example, is
to assign a sequential number programmatically so the user doesn't have to
do it manually.

hth
 
did you database? it doesn't use an Autonumber field,
but rather a Long Integer field. the point of the code, and the example, is
to assign a sequential number programmatically so the user doesn't have to
do it manually.
Click to expand...

Did you read what I wrote? Here's a précis: MAX + INCREMENT isn't a
reliable algorithm for generating Sequence numbers.

I did indeed look at the example application. Not at the code but I
did try out the application after I'd inserted a very high value into
the 'long integer' column it did indeed return 'error' rather than a
sequential number. Note the OP used the term 'autonumber' in the
subject of this thread so it is reasonable to point out that the
algorithm used in the suggested example application differs from that
of Access/Jet's autonumber functionality.

Did you try my code? If you did, you should have observed how
autonumber works i.e. when MAX + INCREMENT goes out of the value range
of 'long integer', rather than error it 'wraps'.

A better approach in SQL is to have a ready-rolled table of
incrementing integers, a standard trick in SQL (do a google search for
"Sequence table"). It is more reliable to have a permanent auxiliary
table rather than generate sequence numbers on the fly e.g. outer JOIN
the Sequence table and find the minimum unused value.

Jamie

--
 
BobW je napisao/la:
I am designing a system for entering tickets. I want it to be auto number.
Problem is the company is at ticket # 3516, and I wish to start the
autonumber field at that point.
Click to expand...



SET AUTONUMBER START VALUE:

0) Close all tables that you are working on

1) Press Ctrl-G

2) In Immediate Window enter command:

CurrentProject.Connection.Execute "ALTER TABLE [Table1] ALTER
COLUMN [ColumnID] IDENTITY(3516, 1)"

3) Press Enter

4) If everything goes fine no message will be shown (like nothing've
happened)

5) To test it insert new row in altered table and watch for autonumber
column value
 
Back
Top