|
Subject:
|
Access ADOX - delete PK
|
|
Posted By:
|
Derek_05
|
Post Date:
|
12/14/2005 11:02:28 AM
|
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
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
12/15/2005 9:02:09 AM
|
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
|
|
Reply By:
|
Bob Bedell
|
Reply Date:
|
12/15/2005 5:26:00 PM
|
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
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
12/16/2005 7:26:47 AM
|
Oh, I thought you were trying to replace the existing PK, I didn't understand you just wanted to delete it.
mmcdonal
|
|
Reply By:
|
Derek_05
|
Reply Date:
|
1/3/2006 12:28:45 PM
|
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
|
|
Reply By:
|
Derek_05
|
Reply Date:
|
1/3/2006 12:48:48 PM
|
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
|
|
Reply By:
|
rgerald
|
Reply Date:
|
1/3/2006 1:24:27 PM
|
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
|
|
Reply By:
|
Derek_05
|
Reply Date:
|
1/4/2006 2:32:52 AM
|
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
|
