Hi there,
I am currently trying to delete an existing Primary key within a Table of a DB and assign a new one using VB (ADOX) in MS Access.

My code is working for the part to create a Primary key (through test), but I cannot figure out how to delete the existing PK with VB Code.

I know how to do this in the design view but I want to be able to do this on the GUI side (VB Code, if possible?).

Please reply if you can help, any help and advice will mostly be appreciated. Thank you all.

Derek

Egads, why are you deleting a PK?

Anywho, I would just create a new table and then pass the data from the old table, minus the PK, and it will reassign a new PK, or you can leave it out in the Append query and then assign it with an Update query.

Again, why remove a PK? Aren't there associations that will be lost?

HTH

mmcdonal

Hi Derek,

When you give a field "primary key" status in an Access db, Access creates an index on that field and gives the index the default name "PrimayKey". To "delete the primary key" you need to delete the index created by Access. Easiest way to do that is with SQL. The following assumes that you have already removed any foreign key constraints (i.e., relationships).

Public Sub DeletePrimaryKey()

    Dim SQL As String

    SQL = "ALTER TABLE TableName DROP CONSTRAINT PrimaryKey"

    DoCmd.RunSQL SQL

End Sub

HTH,

Bob

Oh, I thought you were trying to replace the existing PK, I didn't understand you just wanted to delete it.

mmcdonal

Hi there
Thanks for the help and advice.
i have managed to do what i wanted thanks very much and will share it here when I get the chance to post it up...
i know its abit wierd what Im trying to achieve...but the current Database is on a SQL server and live with clients trafficing data.
for some reason the PK is abit bodged abit. Its a long story sorry. But Its just something Ive been asked to do. Thanks very much for your time and help.

Derek

PS hope all have had a happy xmas and a happy new year

In response to my last email, here is the code that I used to remove the PK and assign the a new PK. The old PK was UserId but went out of sync for some reason and was no longer unique, so I assigned to PK the the UserName which is Unique. Everything works well now. Thank you to those who took time to help.

Derek



For Each tbl In oCat.Tables
        If Not InStr(1, LCase(tbl.Name), "sys") > 0 Then
            If tbl.Name = "Users" Then
                tbl.Keys.Delete 0
                tbl.Indexes.Delete 0
            End If
        End If
Next

'======================================

objTable.Name = "Users"

objKey.Name = "PrimaryKey"

objKey.Type = adKeyPrimary
objKey.Columns.Append ("UserName")
oCat.Tables("Users").Keys.Append objKey

I have one very important question.

How do you guarantee that a user name is unique? Names such as "John Smith" or "Mary Jones" are notoriously non-unique.

Rand

Well this is not for me to worry as clients usernames are checked againgst the DB when creating a new account. But I would suggest that the string would be checked using somehting like this:

DO UNTIL <end of recordset>
   IF <username_entered> = <record_username> THEN
      prompt <username_entered> already in-use
   ELSE
      WRITE <username_entered> to DB (ie create a new record)
      EXIT LOOP
   END IF
NEXT record

it would force people to use other names such as JSmith, JohnSmith, JS, John.Smith, JSmith1 etc etc

Hope this helps

Derek