Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Adjusting AutoNumber

Posted on 2011-09-11
16
Medium Priority
?
254 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:APD_Toronto
  • 8
  • 5
  • 3
16 Comments
 
LVL 75
ID: 36519366
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
 
LVL 75
ID: 36519368
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
 
LVL 75
ID: 36519374
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:APD_Toronto
ID: 36519392
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
 
LVL 75
ID: 36519445
"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
 

Author Comment

by:APD_Toronto
ID: 36519526
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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 36519539
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
 

Author Comment

by:APD_Toronto
ID: 36519555
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
 
LVL 75
ID: 36519565
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
 

Author Comment

by:APD_Toronto
ID: 36519594
thanks.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36520773
>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
 

Author Comment

by:APD_Toronto
ID: 36523176
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
 
LVL 42

Expert Comment

by:dqmq
ID: 36523816
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
 
LVL 75
ID: 36524296
Auto Numbers - A Beautiful Thing.

mx
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36524556
As was the prom queen--'till I got to know her better.  Don't get me started.
0
 
LVL 75
ID: 36525826
Don't get me started either.  I known Ms Auto Number for almost 19 years.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question