Programatically Create Primary Key

[Melissa]

I use a make table query to create a table named MyTable in an external
database at C:\MyFolder\MyDatabase.mdb. MyTable has a field named MyFieldID.
How do I programatically make MyFieldID the primary key? I am using AccessXP
in 2000 mode.

Thanks!

 

[Allen Browne]

The primary key is an index. You could OpenDatabase directly on
C:\MyFolder\MyDatabase.mdb, CreateIndex(), CreateField() in the index,
append the field to the index's Fields collection, and append the index to
the table's Indexes collection.

If you are doing this repeatedly, it would be simpler to leave the table
intact and just delete any existing data it has, and then use an Append
query instead of a Make Table query to add the data.

 

[Melissa]

Thanks, Allen, for the quick response!

I found this code in the Help file --
Set idx = tdf.CreateIndex("PrimaryKey")
Set fldIndex = idx.CreateField("ContactID", dbLong)
' Append index fields.
idx.Fields.Append fldIndex
' Set Primary property.
idx.Primary = True
CreateField does as the name implies, it creates a new field. The field I
want to make the primary key already exists. How do I modify the above code
for an existing field?

Thanks!

 

[Melissa]

Allen,

The table is in a temp database. I delete the database, create a new
database and create the table with the maketable query. I do this because
keeping the table intact and deleting and appending data causes bloat. The
maketable query does not mark the primary key field as the primary key
however.

Thanks!

 

[Allen Browne]

Okay, Melissa. If that is your choice, it looks like you will need to invest
some time learning how to use the various functions in the first paragraph
of my previous post.

 

[John Vinson]

I found this code in the Help file --
Set idx = tdf.CreateIndex("PrimaryKey")
Set fldIndex = idx.CreateField("ContactID", dbLong)
' Append index fields.
idx.Fields.Append fldIndex
' Set Primary property.
idx.Primary = True
CreateField does as the name implies, it creates a new field. The field I
want to make the primary key already exists. How do I modify the above code
for an existing field?

Read the online help for CreateField.

It creates a field IN THE INDEX - not in the table; this is how the
index "knows" what fields it's indexing.

John W. Vinson[MVP]

 

[Tom Wickerath]

Hi Melissa,

Perhaps it would be easier to keep a template copy of the temporary database
available, which already has the structure defined in the tables. You can
programmatically delete (kill) the temp.mdb when needed. Then copy temp.bak
(the template database) to temp.mdb and you have a fresh temporary database
ready to go.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Allen,

The table is in a temp database. I delete the database, create a new
database and create the table with the maketable query. I do this because
keeping the table intact and deleting and appending data causes bloat. The
maketable query does not mark the primary key field as the primary key
however.

Thanks!

 

['69 Camaro]

Hello, Steve Santus.

Weren't you the "Access Resource"? So, now it's "Melissa, the Access
Resource"? I would think an Access resource would use simple SQL to add a
primary key constraint to a newly created table in another database file,
such as:

ALTER TABLE [;DATABASE=C:\MyFolder\MyDatabase.mdb].MyTable
ADD CONSTRAINT PrimaryKey Primary Key (MyFieldID);

Or perhaps I'm overestimating the skill level of an Access resource in
Pennsylvania? If so, please add this code to your toolbox of examples of
code that works so that some day, if you continue to add to the advice
you've been given over the years, you'll have enough tools in the PC
Datasheet toolbox where, even when you post under a fake name, your title is
true enough on the rest of the planet.

HTH.
Gunny