?
Solved

Drop Primary Key in MS Access in Query

Posted on 2008-06-10
20
Medium Priority
?
1,592 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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
 
LVL 10

Accepted Solution

by:
therealmongoose earned 200 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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 …
Suggested Courses

762 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