Remove primary key setting on backend table

[atlantianferret]

How do you remove the primary key setting on a field in a backend access table via VBA?

I have functions that can add, change, or remove fields, but nothing that adjusts the primary key.

 

[nIGHTmAYOR]

i wouldnt waste my time nore invest in a vb script to ulter table structures nore fronts nore back since usualy this happens every blue moon or you are doing something wrong
ever tried path through queries ?

you create a query then through (Query >> SQL Specific >> Path Through Query) you aquire an sql prompet that is in direct link to your back end , all it needs is entering a connection string parameter in (View >> Properties) and administer your back end the way you want

 

[LPurvis]

Is this a Server (i.e. ODBC accessible) database platform that's hosting your tables?
If not then your need isn't for a passthrough (though if it were then certainly it's a good option).

As far as removing a Primary Key in general - that's just removing that index (which happens to be unique and Primary).
e.g.
OpenDatabase("C:\Path\YourBE.MDB").Execute "DROP INDEX [PrimaryKey] ON tblTableName"

Depending upon what your MDB, Table and PK index are called.
"PrimaryKey" is the name Access will create in your tables if done so through the UI.

Question: Why do you want to delete a primary key?
To replace it with another one? If no why? If not then - why? lol You always want a PK in your table.

 

[atlantianferret]

I was having an issue with a table that has two primary keys to form a unique id. All my reports that used that table stopped working and the queries would show the column of one but no data in it.?? I found out the index was bad on that table the day I posted this.

I was going to delete the keys so the reports would work until I found out what was wrong. One of my users was having similar issues, so I had to distribute a fix quickly. However, I found it was just mine was corrupt some how and my user's problem was that she was not following directions.

Thanks for the replies, I will add this to my function collection.

 

[LPurvis]

>> "an issue with a table that has two primary keys to form a unique id"
You mean one primary key - consisting of two fields. ;-)

A PEBKAC is ever present issue and often something that can be overlooked when you're expecting something else.