Remove primary key after import

[Michael Abrams]

Acc2013

Is there a line (or 2) of code that I can add to my command button - after a line that imports a table from another database that will remove the Primary Key from a field named RECNUM?

Code: Select all

DoCmd.TransferDatabase acImport, "Microsoft Access", _
   "R:\XYZ HMOs 2017\HMONAME 2017\" & strMonth & "\XYZ\" & strMonth & ".ACCDB", acTable, _
   "CURRENT MONTH MEMBER COUNT", "CURRENT MONTH MEMBER COUNT XYZ", False

Michael

[HansV]

Let's say the primary key is imaginatively named PrimaryKey.
You can use

Code: Select all

CurrentDb.Execute "ALTER TABLE [CURRENT MONTH MEMBER COUNT XYZ] DROP CONSTRAINT PrimaryKey", dbFailOnError

[Michael Abrams]

My code (RECNUM is the field name for the Primary Key):

Code: Select all

CurrentDb.Execute "ALTER TABLE [CURRENT MONTH MEMBER COUNT XYZ] DROP CONSTRAINT RECNUM", dbFailOnError

Receive error 3755 - CHECK constraint RECNUM does not exist.

CURRENT MONTH MEMBER COUNT XYZ table has a Primary Key = RECNUM

[HansV]

The primary key is on the field RECNUM, but RECNUM is not necessarily the index name of the primary key. You can view the index name in the Indexes window:

S1565.png

In this screenshot, the primary key is on the field HolidayDate, but its index name is PrimaryKey. The latter is the name you should use in the code.

[Michael Abrams]

Ouch - I have been working with Access for xx years and forgot all about that. It works fine.

I need to append this into another table, so I will work on that today.

Thank you HansV.

Michael