Repair Table using Autonumber as key

[Guest]

We have a table that uses autonumber as the key. Somehow a record is missing
.. I can get the missing record in an backup. However I cant insert the
record. I also tried to change the properties of the field to a number. I
can then insert record but cant change the field back to autonumber. Any
suggestions. The data in this table is linked to another table( the data is
now an orphan) and the data will be lost.

 

[Arvin Meyer]

The easiest way is to simply add the record, take whatever number Access
returns, then find the orphaned record in the other table and update the
foreign key.

Failing that, you can kill the autonumber property of the PK field (make
sure you work on a copy), add a new autonumber column, add a new column to
the foreign table, link to that other table using the old autonumber, then
update the new second column with an update query.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Luiz Cláudio C. V. Rocha [MVP]]

Hi,

if you cannot have lost numbers, autonumber is not the best data type for
you. You should use Long Integer and a VBA routine to generate the next
number whenever you insert a new record.

If you want to work with an autonumber field, don't change it to Long
integer, otherwise it may be hard to convert it back to autonumber.

My first stab to recover the record would be an Append Query. It usually
works fine.

There is also a harder way:

1) Insert this routine in a standard module:

Function ChangeSeed(strTbl As String, strCol As String, lngSeed As Long) As
Boolean
'You must pass the following variables to this function.
'strTbl = Table containing autonumber field
'strCol = Name of the autonumber field
'lngSeed = Long integer value you want to use for next AutoNumber.

Dim cnn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim col As ADOX.Column

'Set connection and catalog to current database.
Set cnn = CurrentProject.Connection
cat.ActiveConnection = cnn

Set col = cat.Tables(strTbl).Columns(strCol)

col.Properties("Seed") = lngSeed
cat.Tables(strTbl).Columns.Refresh
If col.Properties("seed") = lngSeed Then
ChangeSeed = True
Else
ChangeSeed = False
End If
Set col = Nothing
Set cat = Nothing
Set cnn = Nothing

End Function

2) Run this code, passing your missing record's ID as lngSeed parameter.

3) Insert your missing record.

4) Run the code again, passing the next ID as lngSeed parameter.

Good luck!

 

[Guest]

Thanks Arvin

I choose option A. The record is out off sequence but all the data is there
and it fine. I wish I knew how I lost the record in the first place?

 

[Joseph Meehan]

We have a table that uses autonumber as the key. Somehow a record is
missing . I can get the missing record in an backup. However I cant
insert the record. I also tried to change the properties of the
field to a number. I can then insert record but cant change the
field back to autonumber. Any suggestions. The data in this table
is linked to another table( the data is now an orphan) and the data
will be lost.

Sorry to hear about your problem. I am glad you were able to use a work
around.

I suggest you may not want to use Autonumber for that use. Autonumbers are
designed to provide unique numbers. It in not designed to provide numbers
in order and for a number of reasons may not do so. As a result using them
in any application where the user sees the numbers is likely to end up with
confusion.

There are other ways of providing the numbers you want depending on the
particual application.