VB & Database, Moving primary key from one field to another

Moving primary key from one field to another

Author	Message
Draze #1 / 5	Moving primary key from one field to another It's an Access2000 and VB6, how can I move primary key from one field and put it onto another. Target field is a newly created field with autonumber so it is OK to be a PK. Thanks, Drazen, ============================================= Eat the candy to see my address better !!! Candy spam protection :) =============================================
Sat, 07 Feb 2004 14:09:34 GMT

Chris Kusmier #2 / 5	Moving primary key from one field to another If I'm not mistaken you can't put anything into an autonumber field. Quote: > It's an Access2000 and VB6, how can I move primary key from one field > and put it onto another. Target field is a newly created field with > autonumber so it is OK to be a PK. > Thanks, > Drazen, > ============================================= > Eat the candy to see my address better !!! > Candy spam protection :) > =============================================
Sun, 08 Feb 2004 02:07:18 GMT

Draze #3 / 5	Moving primary key from one field to another X-Ftn-To: Chris Kusmierz You're right, you can't, but I am not trying to. I have a table with primary key ID autonumber field, after deleting some records IDs become discontinued and I need to export the data into another software that requires all IDs to be sequentially starting with 1. So, by using ALTER TABLE I can create a new autonumber field and then delete the old ID field, this way I get what I need. The problem is I have to move the PK from the old field onto new one before deleting the old field and that's what I don't know how. Quote: >If I'm not mistaken you can't put anything into an autonumber field. >> It's an Access2000 and VB6, how can I move primary key from one field >> and put it onto another. Target field is a newly created field with >> autonumber so it is OK to be a PK. >> Thanks, >> Drazen, >> ============================================= >> Eat the candy to see my address better !!! >> Candy spam protection :) >> ============================================= Thanks, Drazen, ============================================= Eat the candy to see my address better !!! Candy spam protection :) =============================================
Sun, 08 Feb 2004 13:55:56 GMT

Dewayne Christense #4 / 5	Moving primary key from one field to another Quote: > >> It's an Access2000 and VB6, how can I move primary key from one field > >> and put it onto another. Target field is a newly created field with > >> autonumber so it is OK to be a PK. With both ADO and DAO, you should be able to do a DROP INDEX followed by a CREATE INDEX. With DAO, you can drop the old PK with db.TableDefs("MyTable").Indexes.Delete "PrimaryKey" and create a new one with Dim ix As Index Set ix = db.TableDefs("MyTable").CreateIndex("PrimaryKey") With ix .Primary = True .Unique = True .Required = True .Fields.Append .CreateField("NewKeyField") End With db.TableDefs("MyTable").Indexes.Append ix ADOX probably has equivalents.
Mon, 09 Feb 2004 02:59:18 GMT

Draze #5 / 5	Moving primary key from one field to another X-Ftn-To: Dewayne Christensen Thanks, Quote: >> >> It's an Access2000 and VB6, how can I move primary key from one field >> >> and put it onto another. Target field is a newly created field with >> >> autonumber so it is OK to be a PK. >With both ADO and DAO, you should be able to do a DROP INDEX followed by a >CREATE INDEX. >With DAO, you can drop the old PK with > db.TableDefs("MyTable").Indexes.Delete "PrimaryKey" >and create a new one with > Dim ix As Index > Set ix = db.TableDefs("MyTable").CreateIndex("PrimaryKey") > With ix > .Primary = True > .Unique = True > .Required = True > .Fields.Append .CreateField("NewKeyField") > End With > db.TableDefs("MyTable").Indexes.Append ix >ADOX probably has equivalents. Thanks, Drazen, ============================================= Eat the candy to see my address better !!! Candy spam protection :) =============================================
Mon, 09 Feb 2004 18:50:37 GMT

Page 1 of 1

[ 5 post ]