Solved

Adjusting AutoNumber

Posted on 2011-09-11
16
240 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

759 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

19 Experts available now in Live!

Get 1:1 Help Now