Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 14th, 2005, 12:02 PM
Authorized User
 
Join Date: Dec 2005
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default Access ADOX - delete PK

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

 
Old December 15th, 2005, 10:02 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old December 15th, 2005, 06:26 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

 
Old December 16th, 2005, 08:26 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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

mmcdonal
 
Old January 3rd, 2006, 01:28 PM
Authorized User
 
Join Date: Dec 2005
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old January 3rd, 2006, 01:48 PM
Authorized User
 
Join Date: Dec 2005
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old January 3rd, 2006, 02:24 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old January 4th, 2006, 03:32 AM
Authorized User
 
Join Date: Dec 2005
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with delete queries on Access Morgz1 VS.NET 2002/2003 0 April 10th, 2006 10:45 AM
Access ADOX - delete PK Derek_05 Access 0 December 14th, 2005 10:56 AM
ADOX Access column add error kf9thdon@aol.com Access VBA 4 February 26th, 2005 04:05 PM
ADOX MSDASQL Access Properties Collection is EMPTY Xentrax Access 0 May 27th, 2004 10:36 AM
PK problem reindeerw Access 10 April 15th, 2004 01:26 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.