Solved

Adjusting AutoNumber

Posted on 2011-09-11
16
250 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 

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 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
 
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

RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

622 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