Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Thanked 0 Times in 0 Posts

    multiple-field primary keys with code (Access 2000)

    I have a table that relates two other tables in a many-to-many relationship. This is the The Order Details table
    in the Northwind sample database relating the Orders and Products tables. Its primary key consists of two fields:
    OrderID and ProductID. I have sometimes to put these pirmary keys with code, but i cant do that.

    Ususally for the orders table i use the following code

    CurrentDb.Execute "CREATE INDEX PrimaryKey ON OrdersTemp (orderid) WITH PRIMARY"
    And it works fine.

    I cannot apply this code however for the table order details like that:

    CurrentDb.Execute "CREATE INDEX PrimaryKey ON [order details] (orderid) WITH PRIMARY"
    CurrentDb.Execute "CREATE INDEX PrimaryKey ON [order details] (productid) WITH PRIMARY"

    I receice an error 3022 saying that the changes are not successful because they would create duplicate values
    in the index,primary key or relationship.
    I think i cannot find a way to write multiple key primary codes.
    Can i receive some advice is it possible to assign multple keys with the CurrentDb.execute method?
    Of course i have no problems in doing it manually,but my question is can i do it by code?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: multiple-field primary keys with code (Access 2000)

    You're trying to create the primary key twice, once for each field. That is not possible.

    Instead, create it once with both fields:

    CurrentDb.Execute "CREATE INDEX PrimaryKey ON [Order Details] (OrderID, ProductID) WITH PRIMARY"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts