AUTONUMBER reset

[Scott Burke]

I wont to reset the autonumber field.
I know of the method that has you create a table .....
create an append query .......
append the record....
delete that record....

There has to be a better way to do this.
This is what I wont to do.
1) import a file into the table ACS_IMPORT
1) The autonumber does its job. 1 - X
2) This field is used to keep the records in the right order.
2) copy the records from ACS_IMPORT to another table.
3) delete all records in ACS_IMPORT.
1) reset the autonumber field.

then do it all again next week.
is there a way to change the Autonumber.start count number thru VBA?

Scott Burke

 

[Douglas J. Steele]

If the value of the Autonumber field matters to you, then you probably
shouldn't be using an Autonumber field.

Realistically, Autonumber fields exist for one purpose only: to provide a
(practically guaranteed) unique value that can be used as a primary key. The
value of that key shouldn't matter: in fact, normally you don't even show
the value of the field to the users.

 

[Scott Burke]

True and that is how I am using it. Technicly I dont have to reset the
number for my logic to work. Small numbers are easyier for the average
person. (ME)

It would be nice to be able to reset the autonumber after I delete all the
records.
Yes, I will delete all the records. The ACS_IMPORT is only a tempary table.

I understand why I cant do this if I don't delete all the records!

The Append query can change the value so VBA should be able to as well.

Here is my overall thought:

function Reset_countme( "Table_Name","Field_Name", New_Value)
Dim TMPSQL as string

TMPSQL = "Delete * from " & table_name
docmd.runsql tmpsql

<< do what I need to change the start number>>

end Function

Like I said, My logic will work regardless. I just wont to keep the
numbers low.
I am having a hard time figuring out how to do it.

Scott Burke

 

[Bob Bonta]

When you import the records, what are you using the autonumber field for?

 

[Scott Burke]

In this case the order of the records is important. A change of address
requires two records. The first one is the org address. The Second record
hold the change.

The people who are sending the record dont own the software. It cost them (
a lot) to have changes made. Because of that they wont ask for the change
untill they have a lot of changes to make.

The records dont have any identifiers that I can use to keep things in order.
The autonumber field helps me keep things in order. It just that we get
2000 to 4000 record at a time! You can imagine how big those number get and
I am Dyslexic. It just easyier for me to use smaller numbers.

Scott Burke

 

[Jason Lepack]

I created this procedure for resetting autonumbers.

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

 

[Scott Burke]

Jason.. THANK YOU!!!!!

That is exactly what I was trying to do.
I was so sure the answear was in the file definitions I did not even
consider looking any where else.

I thank everyone for there time.

Scott Burke

 

[Mirek]

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(1000, 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


Scott Burke je napisao/la: