crap! cant change existing field to autoNumbr. ideas?

[mcamp]

I have a client that has been entering in the primary key (childid) of a table manually (numeric). its a text field type, and now they want to change it to an autonumber for future entries. obviously the correct way of assigning ID numbers, however;

the table is dense, and I cannot change an existing field to an autonumber. so I need to be able to maintain the existing ID numbers, which are not necessarily all in sequence, and have them exist within an autonumber field.

any ideas?

I was thinking about copying the structure of the table (call new one tbl2), make the child id autonumber. then sorting the existing table (tbl1) by ID#, query in the first record to create the starting point for the autonumber into tbl2, then manually copy over everything (other than the child id) from tbl1 over to tbl2 and let the auto number do its thing. then do a comparison against the backup table to find errors, and manually do them.

any1 else have any other, better ideas??
thanks

 

[Montez659]

This suggestion is what seems easy, but not sure I have all the info. Why not just create a new field named childAutoID and set that to the PK and autonumber and remove the PK from the childid field? You retain all info, and shouldn't be too hard to add a way to search by childid.

But like I said, I feel like I am missing some information here.

 

[mcamp]

there are multiple relationships which include childID unfortunantly. I dont think I can change the PK without tracing all the relationships and deleting them first...which still may be easier in the end.

might work!

 

[xenou]

I was under the impression you can insert an number into an autonumber field if it is a valid number that doesn't duplicate a key. (E.g, copy the table data first to a second table, change the data type, then insert the data back into it).

This sounds funny though:

its a text field type, and now they want to change it to an autonumber for future entries.

Its a text field? What kind of text? I would think this might be a problem for relationships - I'm not sure what will happen if its currently text-text and now you'll make it number-text.

 

[boblarson]

This is how I would approach it.

1. Create the new autonumber field in the table.

2. Create a new Long Integer field in each table which needs the foreign key.

3. Run an Update query for each table to update the new Long Integer field with the Autonumber field from the parent table but with the tables linked using the old keys so that the records match up.

4. Go change the relationships so that the new PK and FK fields are linked.

5. Once that has been done for each child table, I would leave the old keys in place for a few months just to be able to back track if something started showing up incorrectly. After a sufficient amount of time, and things are working smoothly, delete the old fields. You wouldn't have to but I would eventually just to clean it up.

 

[mcamp]

Boom! thanks for the idea's guys, I got it! I realized, because of the situation with the client and multiple id numbers that autonumber wouldnt even work in the long run because they have pre-existing paper back forms with these ID#'s for families, and individual children per family.

If a record was ever deleted by accident, or if a child became un-enrolled but siblings with corresponding family ID's stayed enrolled, or if any record needed to be recreated then they would be screwed with these autonumber fields


So instead I kept all of the existing relationships and just created a "done adding" button for when new ID's are created. Much less headache, leaves the ID#'s editable while still indexible, and allows me to submit the multiple unique ID's in multiple tables that I will be adding onto this SQL statement as I go through.

thanks for the brain storming people!

Private Sub cmdDoneAdding_Click()
'DoCmd.Save

Dim MySql As String
Dim Newfamid As String
Dim Newchildid As String
Dim LastIdFam As Long
Dim LastIdChild As Long
Dim LastNameValue As String
Dim FirstNameValue As String
Dim NewCaseName As String

NewCaseName = Me.Case_Name
LastNameValue = "(Enter Last Name)"
FirstNameValue = "(Enter First Name)"

LastIdFam = CurrentDb.OpenRecordset("SELECT Max([Family ID]) FROM tblFamilyData;")(0)
LastIdChild = CurrentDb.OpenRecordset("SELECT Max([Child ID]) FROM tblChildData;")(0)

'MsgBox " Max ids: " & LastIdFam & " : " & LastIdChild
Newfamid = LastIdFam + 1
Newchildid = LastIdChild + 1
'MsgBox " New ids: " & Newfamid & " : " & Newchildid
txtFamilyId.Value = Newfamid

'DoCmd.Save
'DoCmd.Close




MySql = "INSERT INTO tblChildData " & _
"( [Family ID], [Child ID], [Case Name], [First Name], [Last Name])" & _
" VALUES ('" & Newfamid & "','" & Newchildid & "','" & NewCaseName & "', '" & FirstNameValue & "', '" & LastNameValue & "');"

'MsgBox MySql

x = MsgBox("Are you sure you want to add " & "'" & NewCaseName & "'" & " as a New Case?", vbInformation + vbYesNo, "Childrens Crisis Center")
If x = vbNo Then
Exit Sub
Else
DoCmd.RunSQL MySql
End If


End Sub

keywords: vba, id numbers without autonumber, update autonumber, insert values in multiple tables, multiple id numbers without autonumber, multiple different id numbers, multiple relationships

 

[boblarson]

Just an FYI for you -

There is no reason why you couldn't use an autonumber for the PK and FK's. I know you gave a reason you THOUGHT would preclude you from doing so, but in reality the PK and FK is only for the SYSTEM. You can use whatever numbering you wanted for use for VISIBLE USE but still let the system maintain the keys (using surrogates).

 

[mcamp]

yea ur definitely right. just a pain is the a** to do when the database is already pretty large and well established with relationships. (atleast for a do-it-yourselfer like me the problem led me to write that code anyways though so im happy. kill two birds with one stone

thnx for the help though, u guys r great!