Append database primary key to start at 1

[Guest]

I unfortunately did not do a compact & repair after I deleted data from my
database. So when I started adding more data it started at the previous
data's ID last number instead of starting at 1. How do I fix this so that
the primary key ID number starts at 1. I already entered 150 records before
I realized that the ID numbers were wrong.

Thanks
Pat

 

[Guest]

Pat:

1. From the database window copy the table to the clipboard.
2. Paste it back as a new table under a different name, electing to paste
'structure only.
3. Create an append query which appends all fields from the old table to
the new one APART FROM the autonumber primary key field.

The new table should now have all the old one's data with primary key values
starting from 1. You can then delete the old table and rename the new one to
the old one's name.

OK, so that's how to do it, but why should you? Autonumbers are to
guarantee uniqueness not sequence and the values are arbitrary, having no
intrinsic meaning, so where they start from and any gaps in the sequence are
immaterial. Also, if there other tables which reference the primary key of
table in relationships the values of the foreign keys fields in the
referencing tables would no longer map to the correct rows in the new table.
If there is a real need for sequential numbering then you should generate the
numbers in a straightforward long integer number field. I've posted a demo
of how to do this at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps


The demo also prevents any conflicts in a multi-user environment on a
network, where two users might be adding records simultaneously and thus
would otherwise generate the same number for different records, violating the
key. It does this by means of the commonly used technique of storing the
last number in a separate database in a shared folder on the server, normally
the same folder as the back end data file of the application. This is opened
exclusively in code to get the next number, thus preventing any conflicts.

The starting point for the next number can be reset at any number greater
than the highest number already in the table.

Note that this method of generating a 'pseudo autonumber' is designed for
situations where records are entered one at a time via a form, not for bulk
inserts via an 'append' query. For bulk inserts you'd need to add one record
at a time in a loop, calling the code to generate the next number each time.

Ken Sheridan
Stafford, England

 

[Guest]

I copied the data from the one table to a new table excluding the ID number.
But I didn't see where to copy "structure only" either in copy or paste.
When I pasted the data to the new table, the ID number field doesn't show up
in the data view but it is there in the design view. I tried to do an append
query to a new table and also to the old table but in the ID number field it
shows the numbers as 9 digits long.

I enter data for work, Waukegan Police Dept. that is in access. I enter the
data and then I go through different steps to send the data to IDOT. After
it is sent to IDOT, I then delete all the data in my database and am supposed
to do a compact and repair so that the new data will start at 1. This is
just what I was told to do. What I do is change the data to an excel file
and then import it into another database we have that holds all the data we
have ever sent to IDOT for the racial profiling.

Thanks
Pat

 

[Guest]

Ken,

I tried to watch the demo but after downloading the file it came up with 3
Access databases but no way to watch the demo. I tried opening each file but
couldn't figure out how to watch the demo.

Thanks so much for your help.

 

[Guest]

Pat:

As far as the demo is concerned you can just put the three files into the
same folder on your local drive. The files are the front end, the back end
data file and the file which holds the last number. You open the
CounterDemoReset.mdb file to use it. It will first prompt you to refresh the
links to the back end file and then you can try it out by adding some
records. Then you can delete them and rest the 'counter' to 1 (or any other
start number for that matter).

As regards copying your data to a new table the 'structure only' option is
when you paste the new table in. The steps are:

1. In the database window select the original table.
2. Right click and select Copy.
3. Right click again in a blank area of the database window away from any
table name, and select Paste.
4. This brings up a dialogue in which you enter a name for the new copy of
the table and select the 'structure only' option. This makes an empty copy
of the table, which you then fill with the data from the old table, apart
from the autonumber field, with an 'append' query.
5. Once you've confirmed that the new table holds the correct data,
numbered as you wish, you can delete the original table and then rename the
new table with the old table's name.

Ken Sheridan
Stafford, England

 

[Guest]

Thanks so much for your help. It is all done and complete.

Pat