primary key in maketable query (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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:

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

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

    ' 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

Reply | Quote

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

Reply | Quote

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

regards,
otk

Reply | Quote