Moving primary key from one field to another 
Author Message
 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  
 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  
 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  
 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  
 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  
 
 [ 5 post ] 

 Relevant Pages 

1. Joined Primary Key in Foxpro (Make 1 Primary Key from 2 fields)

2. ADO how to determine if a access database field is a primary key field

3. How to move cursor on my desired position specifying primary key

4. Enabling Tab Key to move from field to field

5. Using ENTER key to move from field to field

6. How can I known the field(s) is in the primary key

7. Delete primary key field from tabledef

8. Using VBA to Make a field a primary key

9. Multiple Field-Primary Keys

10. Primary Key Field In VB

11. DataGrid Binding -- Unwanted Primary Key Field

12. Use of Find to search a Multi-Field Primary Key

 

 
Powered by phpBB® Forum Software