Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    primary key in maketable query (Access 2000)

    PrimaryID index on CustomerID


    I need some help in order to put the PrimaryID key in the new table,made with the MakeTable query.I have a table Customers and i make a new table Customers1. However the CustomerID field in the new Customers1 table is without the Autonumber, and is lascking the PrimaryID fields.

    My command for the MakeTable query is the following:

    SELECT customers.Customerid, customers.CompanyName INTO Customers1
    FROM customers;

    How can i put the PrimaryID key in the new Customers1 table?

    I will be grateful for any reply

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: primary key in maketable query (Access 2000)

    One option is to open the table you just created in Design View, Insert a new field, name it PrimaryID (or whatever), with an AutoNumber data type. Then right click on this row of the design grid and select "Primary Key".

    If your "Customers" table has a AutoNumber PrimaryID field already, include that in your Make Table query, too. Then all you'll have to do is identify it as the Primary Key in the new table (as described above).

    I don't know of identifying the Primary Key as part of the Make Table Query. Anyone?

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: primary key in maketable query (Access 2000)

    Nope. You can only do that if you create the table from the user interface or from code. Make Tables are for convenience, and they don't allow you to fine tune many of the features of a table, including the indexes.
    Charlotte

  4. #4
    Lounger
    Join Date
    Oct 2001
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: primary key in maketable query (Access 2000)

    charlotte,
    Are you able to illustrate using code how you make an existing field (e.g. name is "fieldname") in an existing table (e.g. name is "Tablename") the Primary Key?

    I have been searching for this for a while...

    Any guidance really appreciated.

    regards,
    otk

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: primary key in maketable query (Access 2000)

    You create and append an index to the table's indexes and give it a name of "Primary Key". Do you want to do it in DAO or ADO?

    Here's the ADO version:

    <pre>Sub ADOX_Create_PrimaryKey()
    'This sample demonstrates how to create an index or primary key on a pre-existing table.
    Dim cat As New ADOX.Catalog
    Dim conn As New ADODB.Connection
    Dim idx As New ADOX.Index
    Dim tbl As New ADOX.Table

    Set conn = CurrentProject.Connection
    Set cat.ActiveConnection = conn
    Set tbl = cat.Tables("Table1")

    'Create an IndexPrimary Key on the table 'table1'
    With idx
    .Name = "PrimaryKey"
    .Columns.Append "ID"
    .Columns("ID").SortOrder = adSortDescending
    .PrimaryKey = True
    End With

    tbl.Indexes.Append idx

    End Sub</pre>


    Here's a snippet from a DAO routine that illustrates creating a primary key in DAO:

    <pre> ' create a temporary tabledef with a single record
    Set tdf = dbs.CreateTableDef(strTempNm)

    'append each field to the tabledef
    tdf.Fields.Append tdf.CreateField("FieldUsed", dbBoolean)
    tdf.Fields("FieldUsed").DefaultValue = False
    tdf.Fields.Append tdf.CreateField("FieldName", dbText, 50)
    tdf.Fields.Append tdf.CreateField("MapExpression", dbText, 250)
    tdf.Fields.Append tdf.CreateField("MapTo", dbText, 50)

    'add the tabledef to the database's tabledefs collection
    dbs.tabledefs.Append tdf
    ....

    'here's the part that creates the primary key
    Set tdf = dbs.tabledefs(strTempNm)

    'create an index named "Primary Key"
    Set indx = tdf.CreateIndex("PrimaryKey")

    'set its PrimaryKey property to True
    indx.Primary = True
    indx.Required = True
    indx.Unique = True

    'Add the field or fields to the PK index
    indx.Fields.Append indx.CreateField("FieldName")

    'add the PK index to the tabledef's Indexes collection
    tdf.Indexes.Append indx</pre>

    Charlotte

  6. #6
    Lounger
    Join Date
    Oct 2001
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: primary key in maketable query (Access 2000)

    Charlotte,

    Many thanks. I am using 97 which is DAO so I will have a whirl and see if I can integrate your valuable contribution.

    regards,
    otk

  7. #7
    Lounger
    Join Date
    Oct 2001
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: primary key in maketable query (Access 2000)

    Many thanks Charlotte, I have reviewed your feedback and using ADO I have got it going.

    regards,
    otk

Posting Permissions

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