Adjusting AutoNumber

Hello Experts,

This is a contination of http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27300989.html#36514583 , where one of my tables got corrupt, and cannot copy "Structure and Data", as it gives me Record is deleted, but rather i need to copy structure only, then open the original table select all records and paste them into y new empty table.

For the record deleted, this workarround works, but now the problem is my IDs are all different, where in my ids are 1 through 8776, where in my old table the ids are 3613 through 13416.

I understand why in the new numbers are 1 through 8776, but just to explain the numbers in my old table...  IDs 1 through 3612was 2010 data and was backed up and removed.  1,037 records wer started, but cancelled, and Acccess obviously destroys that number,

My question is, how can I copy all records to my new table, while preserving the current ID#s, and still continuing Auto# after pasting?  I really want to avoid adding a new field, as i;ll need to change many lines of code.

The only way I can think of is writing a one-time routine that resembles the below psedo code, but I'm not sure if it will work for this number of records and i'm worndering if thers an easioer way?
rsOld.Open SELECT * order by id
rsOld.MoveLast
maxID = rsOld!id
rsOld.MoveFirst

for newRec = 1 to maxID
  lbl.Caption = Updating newRec of maxID <-- so i know it isnt frozen
  rsNew.AddNew
  If newRec <> rsOld.id
    Cancel rsNew.AddNew to loose # <-i'm not sure the code for this? If there's no code, i can just put an arbitery field in some field, then delete those records
  Else
    rsNew.Field1 = rsOld.Field1
    rsNew.Field2 = rsOld.Field2
    ...
    ...
    rsNew.Update
    rs.Old.MoveNext
  End If
Next

Open in new window

APD TorontoAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
Well ... This executed from the Immediate Window

?mResetAutoNumber(3613 ,1)

will start at 3613 and increment by 1.  Any additional manipulation beyond this would probably have to be manual.  Again, be sure to keep a copy of the table ....

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Make a BACKUP first ... then you can use this code to reseed your AN ... to start from 3613 and increment by one.


Public Function mResetAutoNumber(lStartVal As Long, lIncrement As Long) As String
   'Example: Set the Starting Value and Increment for a table named Table1ANTest and an AutoNumber field called AutoNum
    Dim sSQL As String
    sSQL = "ALTER TABLE [Table1ANTest] ALTER COLUMN [AutoNum] COUNTER (" & lStartVal & ", " & lIncrement & ");"
    CurrentDb.Execute sSQL
    mResetAutoNumber = "Auto Number has been re-numbered"
End Function

Then from the VBA Immediate Window, call the Function like this:

?mResetAutoNumber(3613 ,1)


mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
OH ...  IMPORTANT ... change the object names to match your (Table name and Auto Counter field name)



    sSQL = "ALTER TABLE [Table1ANTest] ALTER COLUMN [AutoNum] COUNTER (" & lStartVal & ", " & lIncrement & ");"
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Put that Function above in a regular VBA module - name the module differently than the Function name.

Be sure your table is not open when running the code.  And you will run this against the *new* table with that has the auto numbers starting from one (1).

mx
0
 
APD TorontoAuthor Commented:
mx, I think your function will start rec 1 at 3616, which is ok, but 3613+8766=12388, where my last id is should be 13416
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"mx, I think your function will start rec 1 at 3616"
It will start 3616 ....

ok ... what number do you want to start from?

mx
0
 
APD TorontoAuthor Commented:
if you refer to my original post,

first # = 3613
last # = 13416
total recs = 8776

As you see, these don't add up, where 1027 were deleted
0
 
APD TorontoAuthor Commented:
yes, i can use your code to start at 3613, but afterwards, do you think my idea in psedo will work or will it freeze?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I'm not sure.  I would suggest running my code ... then try that ... just be sure you have backups along the way.  Sorry, I'm slammed at the moment.

mx
0
 
APD TorontoAuthor Commented:
thanks.
0
 
dqmqCommented:
>My question is, how can I copy all records to my new table, while preserving the current ID#s, and still continuing Auto# after pasting?  I really want to avoid adding a new field, as i;ll need to change many lines of code.

Sometimes I HATE autonumbers and you've stumbled onto one reason.  Revise that, I almost always hate autonumbers.  Fortunately, I think I have a trick that should work.

1.  Create an empty table with the desired structure including the autonumber column.

2.  Make an exact copy of the empty table, except change the autonumber column to a long integer.

3.  Load data into the second table, putting the old values you are trying to preserve into the long integer field with the same name as the autonumber column.

4.  Now load the first table as follows:
           Insert into Table1 select * from Table2

5.  The above statement should populate the first table, including the autonumber column and start incrementing from the max value.  


Good luck









0
 
APD TorontoAuthor Commented:
dgmg,

are you saying creating  an empty table, changing the Auto# to long, loading the data, thn changing it back to Auto#?  If so, i tried, the last step isn't permitted.

I'lll try my code and see.
0
 
dqmqCommented:
No, that will not work--you cannot change the long back to auto after loading the table.

You need to use two tables.  Populate the one with a long using the values you want to preserve.

Then import to the other empty table (having the autonumber column).  For some reason, when you use the INSERT INTO table SELECT FROM table  syntax, Access accepts user-supplied values in the autonumber column.   And transparently continues with auto-incrementing, thereafter.





0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Auto Numbers - A Beautiful Thing.

mx
0
 
dqmqCommented:
As was the prom queen--'till I got to know her better.  Don't get me started.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Don't get me started either.  I known Ms Auto Number for almost 19 years.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.