Convert table with autonumber key ?!?

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

Guest

I am rewriting a system in which the main data table has an autonumber key. There are lots of large gaps in the autonumber sequences.
I need to somehow create a new table (preferably with an autonumber key) and retain all the old autonumbers for the existing records.
The new table will have many changes to columns, some will be renamed, some deleted and some added.

Hope someone can help. All I can think of is abandoning the autonumber key and writing a function to do the autonumbering. Anyone have an example? This is a multi-user application.
 
I am rewriting a system in which the main data table has an autonumber key. There are lots of large gaps in the autonumber sequences.
I need to somehow create a new table (preferably with an autonumber key) and retain all the old autonumbers for the existing records.
The new table will have many changes to columns, some will be renamed, some deleted and some added.

Hope someone can help. All I can think of is abandoning the autonumber key and writing a function to do the autonumbering. Anyone have an example? This is a multi-user application.
Click to expand...

Autonumbers ALWAYS have gaps; this should be expected and ignored
(i.e. users should never have occasion to see the value of an
autonumber field, so they won't worry).

You can run an Append query, appending existing data into a table; if
you append data from an existing Autonumber or Long Integer field into
an Autonumber field in the new table, the value will be preserved. So
create your new table empty, with the corrected field types.
 
In this system, the autonumber field is the Ticket Number. It is the main way of identifying a ticket and users have to be aware of it. It is on almost every screen in the system and on a lot of reports

Is there any other way to easily come up with a unique number

- David
 
Is there any other way to easily come up with a unique number?
Click to expand...

Yes - you can use the data entry Form's BeforeInsert event with code
like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtID = NZ(DMax("[ID]", "tablename')) + 1
End Sub

to increment the new record's ID. This might run into problems if it's
a multiuser system in which several people might be competing for the
next number; there's some more elaborate code to store the next ID in
a table, open the table exclusively, increment and re-stash the value,
and return it to the form; the code I use is copyrighted though so I
can't post it (Access Developer's Handbook).
 
Back
Top