[AccessD] Autonumber Corruption ...Help!!!!

Okay, the easiest way to update would be an append.  However, I am a code
head.  So I was hit with this problem about 6 months ago.  They wanted to
're-number' an Autonumber field, and the developer had switched to Numbers,
and made the change, and wanted to put the 'new' numbers into the table.  So
I whipped up a little code for this.  It uses two recordsets.  rsNew and
rsOld.  rsNew needs to open a blank copy of the table with the Number field,
but with an AutoNumber field instead.  rsOld opens the number field table.
If the records are sorted by that Number, use a SQL statement instead of
opening the table directly.

Last but not least, there are two lines that say
rsNew.Fields(1).value="Test".  Fields(1) needs to point to a text
field...any text field will do, so just replace the '1' in both places with
the first text field in the table.  (remember 0 is the first field...)

Here's the code:

Dim rsNew As Recordset
Dim rsOld As Recordset
Dim intCurrentKey As Long
Dim i As Long
Set rsNew = CurrentDb.OpenRecordset("Problems", dbOpenTable)
Set rsOld = CurrentDb.OpenRecordset("Problems-old3", dbOpenTable)
rsOld.MoveFirst
Do Until rsOld.EOF = True
    intCurrentKey = rsOld.Fields(0).Value
    rsNew.AddNew
    rsNew.Fields(1).Value = "Test"
    rsNew.Update
    rsNew.MoveLast
    Do Until rsNew.Fields(0).Value = intCurrentKey
        rsNew.Delete
        rsNew.AddNew
        rsNew.Fields(1).Value = "Test"
        rsNew.Update
        rsNew.MoveLast
    Loop
    rsNew.Edit
    For i = 1 To rsOld.Fields.Count - 1
        rsNew.Fields(i).Value = rsOld.Fields(i).Value
    Next i
    rsNew.Update
    rsOld.MoveNext
Loop
MsgBox "Done"

-----Original Message-----
From: William Hindman [mailto:dejpolsys at hotmail.com]
Sent: Wednesday, January 29, 2003 11:56 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Autonumber Corruption ...Help!!!!


...I did something really stupid ...not enough sleep I guess :(

...tblCompany.CompID is an Autonumber PK in a be mdb

...one of clients computers disconnected suddenly and corrupted the mdb

...an entire record in tblCompany was corrupted and repair did not fix it 
...so I went to an archive from December and copied the record and pasted it

into the current table ...all fine except that the AN PK had now changed and

would not let me replace it with the original :(

...so I got stupid and went into design mode and changed the type from AN to

a long ...fixed the PK ...then tried to change the data type back ...and of 
course it won't let me :(((((

...the company owner has last night's server backup tape but is in Miami and

no one can reach him :(((((

...is there any way on god's green earth I can repair this?

William ...doofus extraordinaire :((((






_________________________________________________________________
Add photos to your messages with MSN 8. Get 2 months FREE*.  
http://join.msn.com/?page=features/featuredemail

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd