Solved

Drop Primary Key in MS Access in Query

Posted on 2008-06-10
20
1,539 Views
Last Modified: 2010-05-18
Hi,
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
"ALTER TABLE myTable ADD ID int IDENTITY, CONSTRAINT [tPkey] PRIMARY KEY(ID)"
that would give me the random ID column, but first I need to remove the existing ID which I have problem with.

Please help.
0
Comment
Question by:staines
  • 12
  • 8
20 Comments
 
LVL 10

Expert Comment

by:therealmongoose
ID: 21751589
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!!
0
 

Author Comment

by:staines
ID: 21756000
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.

Thanks
0
 
LVL 10

Expert Comment

by:therealmongoose
ID: 21757511
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...
0
 

Author Comment

by:staines
ID: 21758609
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.??
0
 

Author Comment

by:staines
ID: 21767955
anyone please advice. Thanks.
0
 
LVL 10

Expert Comment

by:therealmongoose
ID: 21771359
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?

0
 

Author Comment

by:staines
ID: 21790205
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?
0
 

Author Comment

by:staines
ID: 21811938
anyone please?
0
 

Author Comment

by:staines
ID: 21830583
help?
0
 

Author Comment

by:staines
ID: 21906911
please?
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 10

Accepted Solution

by:
therealmongoose earned 50 total points
ID: 21914732
ok - here's the code
Option Compare Database

Option Explicit
 
 

'in .net, this will require a reference to the library

'c:\windows\System32\dao360.dll

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)

    rst.MoveFirst

    

    Do While Not rst.EOF

    

        rst.MoveNext

        x = x + 1

        

    Loop

    

    'generate x number of unique random numbers

    varNewIDs = fncGenerateRandoms(x)

      

    'write the new random numbers to the new field

      

    rst.MoveFirst

    x = 0

    

    Do While Not rst.EOF

    

        rst.Edit

        rst!NewIDField = varNewIDs(x)

        rst.Update

        x = x + 1

        rst.MoveNext

        

    Loop

    

    'Close table object

    rst.Close

    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

    

    dbs.Close

    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)

    

    Randomize

    

    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)

            

        Loop

        '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

0
 

Author Comment

by:staines
ID: 21918083
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
       
0
 
LVL 10

Expert Comment

by:therealmongoose
ID: 21918514
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!
0
 
LVL 10

Expert Comment

by:therealmongoose
ID: 21924492
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?
0
 

Author Comment

by:staines
ID: 21927890
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.

Thanks.
0
 
LVL 10

Expert Comment

by:therealmongoose
ID: 21931662
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
0
 

Author Comment

by:staines
ID: 21931691
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?
0
 
LVL 10

Expert Comment

by:therealmongoose
ID: 21934264
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)

e.g

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

etc....

0
 

Author Comment

by:staines
ID: 21939287
really...thats not a solution :(
I cant go in and type all hundreds database name...
0
 

Author Comment

by:staines
ID: 21952345
thanks anyway
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Read about achieving the basic levels of HRIS security in the workplace.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now