Drop Primary Key in MS Access in Query

I have a primary key column with autonumber. (MS Access 2003)
I would like to change this to a random number field primary key (remove all current ID's and replace with random)

However I cannot get the "ALTER TABLE myTable DROP PRIMARY KEY" to work, it returns with syntax error.

I would though the step would be:
1/ Drop the primary key column ID
2/ Add new column with
that would give me the random ID column, but first I need to remove the existing ID which I have problem with.

Please help.
Who is Participating?
therealmongooseConnect With a Mentor Commented:
ok - here's the code
Option Compare Database
Option Explicit
'in .net, this will require a reference to the library
Sub AlterKey(strDatabasePath, strTable As String, strOldPK As String, strNewPk As String)
    Dim dbs As Database
    Dim tbd As TableDef
    Dim fld As Field
    Dim idxfld As Field
    Dim idx As Index
    Dim rst As Recordset
    Dim x As Long
    Dim varNewIDs As Variant
    'set references to database, table and new field
    Set dbs = OpenDatabase(strDatabasePath)
    Set tbd = dbs.TableDefs(strTable)
    Set fld = tbd.CreateField(strNewPk, dbLong)
    'Add the new field to the table object
    tbd.Fields.Append fld
    'Delete the existing primary key index
    tbd.Indexes.Delete ("PrimaryKey")
    'Delete the existing PK field
    tbd.Fields.Delete (strOldPK)
    'populate new field with random unique numbers
    'start by counting number of records in table
    Set rst = dbs.OpenRecordset(strTable)
    Do While Not rst.EOF
        x = x + 1
    'generate x number of unique random numbers
    varNewIDs = fncGenerateRandoms(x)
    'write the new random numbers to the new field
    x = 0
    Do While Not rst.EOF
        rst!NewIDField = varNewIDs(x)
        x = x + 1
    'Close table object
    Set rst = Nothing
    'Creat new PK index for new field
    Set idx = tbd.CreateIndex("PrimaryKey")
    Set idxfld = idx.CreateField(strNewPk)
    idx.Unique = True
    idx.Primary = True
    'Append new index to tabledef
    idx.Fields.Append idxfld
    tbd.Indexes.Append idx
    Set dbs = Nothing
    MsgBox "Done"
End Sub
Function fncGenerateRandoms(x As Long) As Variant
'creates an array of unique random numbers
    Dim varResult() As Variant
    Dim lngCount As Long
    Dim lngTemp As Long
    Dim lngRandUpper As Long
    Dim booUnique As Boolean
    lngRandUpper = x * 500
    ReDim varResult(x)
    For lngCount = 0 To x
        booUnique = False
        Do While booUnique = False
            'Generate a random number
            lngTemp = CLng((lngRandUpper * Rnd) + 1)
            'Check it is unique to this list
            booUnique = fncIsUnique(lngTemp, varResult)
        'Debug.Print "x = " & x & " : Assign " & lngTemp
        varResult(lngCount) = lngTemp
    Next lngCount
    fncGenerateRandoms = varResult
End Function
Function fncIsUnique(lngValue As Long, varArray As Variant) As Boolean
    'iterates through all values in the array and checks if
    'number already exists in list
    Dim varitem As Variant
    Dim booUnique As Boolean
    booUnique = True
    For Each varitem In varArray
        If varitem = lngValue Then
            booUnique = False
            Exit For
        End If
    Next varitem
    fncIsUnique = booUnique
End Function

Open in new window

you can change this in design view of the table - remove the primary key icon from the pk field and change the field type from autonumber to number.

I would question the use of random numbers as ok id's, but I'm sure there must be a good reason for this!!
stainesAuthor Commented:
thanks for reply.

I know I can simply open the file to change it, but theres alot of files need to be changed.

Therefore I would like to run this in code. I am using vb.net.
So a query solution is what I am looking for.

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

There are some sql commands that don't work terribly well with access sql - for example the drop table causes a lock on the database and therefore not recommended for use. Not sure if altering pk using access sql would do the same, you may be able to do it in vba and call the routine from .net...
stainesAuthor Commented:
no one will be using the database so it doesnt matter if it is lock.

All I want to find out is the correct syntax in MS Access that can:
1) Drop the primary key field column autonumber
2) Add a primary key with random numbers.

The syntax ALTER TABLE myTable DROP PRIMARY KEY, does not work on access.??
stainesAuthor Commented:
anyone please advice. Thanks.
Not sure this is possible in jet sql - as previously stated it doen't really support table alterations....

Is this a one off and does it have to be written in .net - would a vba script that will run in your database suffice? Also is the reindexing a one off, or does it need to be setup to run again and manage the pk's of your table/s?

stainesAuthor Commented:
thanks for reply.

It would have to be in vb.net because the rest of it was in the same program too.
There must be a way in code to drop the primary key column in MS Access, what about a script I can run to drop the column first, then in vb.net I can create what I want.

If so the script would be sitting somewhere and get fires by the .net program running in a loop thru the files. Any ideas?
stainesAuthor Commented:
anyone please?
stainesAuthor Commented:
stainesAuthor Commented:
stainesAuthor Commented:
Thanks for reply therealmongoose.

I have added the reference to DAO 3.6 dll i found in program files\common\microsoft shared.
and added import DAO at the top, but cannot resolve the problem at line:"Option Compare Database"

The word Database is unknow.
I guess this also led to  OpenDatabase unknown at line "dbs = OpenDatabase(strDatabasePath)" and some others.

Do you know what I am missing?
Many Thanks
sorry - delete the option compare database statement - this is access vba code....

Let me have a look at the code in .net as I have not tested it...

I will get to you!
hmmm - I've had a look at runnind this in vb . net, unfortunately the functionality of the dao object is slightly different in .net, I'm sure there's a way of making it run, but haven't found it yet. Are you able to use the code in access instead? Whats the overall purpose of this routine?
stainesAuthor Commented:
basically i have over 100+ .mdb files, where the update I release now will require the old databases to be changed, and chainging the primary key is part of the update that I need to make.

I've just reread your original post and the solution provided fulfils the requirements of your original request as there was no mention of .net. You should be able to perform changes to tables using access using the code provided. I'd suggest if you need a .net solution that you raise a new question....

Kind regards
stainesAuthor Commented:
If I dont use vb.net, what can I use to run this code above outside Access?
I just dont want to open the access one by one and run it, it is very time consuming.

What will I need to run vba code?
You can run this from one access database - in the call you need to specify the database path - just change this and it will work....

Sub AlterKey(strDatabasePath, strTable As String, strOldPK As String, strNewPk As String)


AlterKey("c:\temp\mydb1.mdb , "tablename", "oldPKField", "NewPKFieldname")
AlterKey("c:\temp\mydb2.mdb , "tablename", "oldPKField", "NewPKFieldname")
AlterKey("c:\temp\mydb3.mdb , "tablename", "oldPKField", "NewPKFieldname")


stainesAuthor Commented:
really...thats not a solution :(
I cant go in and type all hundreds database name...
stainesAuthor Commented:
thanks anyway
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.