Bob,

Thank you for your help.

There is only one 'no duplicates, autonumbder' field in the table.

I can insert the records into a new table but this table is relational to another that looks to this table for data through the PKID field number. If I change those numbers, how do I re-establish the links to the other table?

I did what was suggested here: http://support.microsoft.com/kb/304561/EN-US/ but the primary key got put into a number field and apparently in order to get another autonumber field, I have to add a new field and can't use the old one.

I haven't tried using insert but I'm thinking the same thing will happen since Access won't let me turn a field with data in it into an autonumber field.

Penn

Hold on!!! I just ran the insert and the autonumbers are retained and I can add new records!! Hallelujah!! I think this will do it. Thank you very, very much!

Penn

LOL And there was much rejoicing...:)

Glad you got it going, Penn.

Bob

BTW, I'm using ACC03, DAO (*.mdb) and this also works to retrieve the ID number. You have to be sure to set the variable (NewID) before updating.

  Dim rst as DAO.ReocrdSet
  Dim NewID as long
  Set rst = CurrentDb.OpenRecordset("Table2")
  With rst
    .AddNew
    !Field1 = "NewField1Text"
    NewID = !ID
    .Update
    .Close
  End With
  Set rst = Nothing

Hi Penn,

The identical code works in ADO, too. Just had @@IDENTITY in mind from some Access "stored procedure" (using the phrase very loosely) experiments I've been playing with for a while.

Bob

Penn,
So do I understand using the SQL Insert allowed your autonumber PK to migrate into the new table in an autonumber field?

(I gotta little project ahead of me...... One of my main tables went belly up today).

Loralee

Loralee,

I believe the short answer to your question is, "Yes".

The new table used the same autonumbers as the original one except where there were errors in the records. I lost at least one record which I just heard about yesterday, actually. I don't think many others were lost, if any, because I would have heard about it.

The advantage to the insert was to respect the original autonumbers because they were used to link to another table.

Does that help and am I making sense to you? If not, just let me know.

Penn

Loralee,

Just posted a response but I don't know where it went. Apparently not here, at least, but this may show up twice. Anyway, I'll try again...

I believe the short answer to your question is, "Yes".

Using the insert, the PK autonumber field in the new table respected (i.e. had the same numbers) as the old table with the exception of damaged records. I just found out yesterday, actually, that one record had been lost. I don't think there were many others, if any, because I think I would have heard about it. But I have no real way of figuring it out.

The advantage to keeping the same PK autonumbers was that they are used as link numbers to another table.

If I'm not making myself clear or if that doesn't anwer your question, please let me know.

Penn

Hi Loralee,

Hopefully the third time's the charm. I've posted this response twice before and it hasn't shown up yet.

I believe the short answer to your question is "Yes".

The advantage of insert is that the new table keeps the same PK autonumber numbers as the old table with the exception of any damaged records. I just found out yesterday, actually, that one of the records had been deleted. I doubt there are any more because I think I would have heard.

Keeping the same PK autonumbers was important to me because those numbers are used as link identifiers for another table.

If this isn't clear or if you have more questions, you might try my email ([email protected] (take out _remove) since this forum doesn't seem to be working. I've sent a message to tech support but haven't gotten any response yet.

Penn