Solved

Adjusting AutoNumber

Posted on 2011-09-11
16
242 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
 

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 - Access MVP) earned 500 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

929 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now