asked on

Drop Primary Key in MS Access in Query

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.

therealmongoose

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!!

staines

ASKER

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

therealmongoose

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...

staines

ASKER

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.??

staines

ASKER

anyone please advice. Thanks.

therealmongoose

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?

staines

ASKER

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?

staines

ASKER

anyone please?

staines

ASKER

help?

staines

ASKER

please?

ASKER CERTIFIED SOLUTION

therealmongoose

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

staines

ASKER

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

therealmongoose

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!

therealmongoose

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?

staines

ASKER

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.

therealmongoose

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

staines

ASKER

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?

therealmongoose

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....

staines

ASKER

really...thats not a solution :(
I cant go in and type all hundreds database name...

staines

ASKER

thanks anyway