Make table queries and primary keys

  • Thread starter Thread starter Iain
  • Start date Start date
I

Iain

Hi all,

I would like to create a make table query and specify a
primary key in the process.

This is the SQL statment for my make table query:

SELECT [Original].[Date], [Original].[Split], [Original].
[Apples], [Original].[Bananas], [Original].[Pears],
[Original].[Grapes] INTO Made
FROM Original;

I would like the field [Original].[Date] to become the
primary key in the new table.

Is their anyway of doing this?

Thanks
Iain
 
Hi,


You can ALTER TABLE once it is created, to add a constraint to it.


Vanderghast, Access MVP
 
Thanks for the info Michel,

How would I be able to do this?

Thanks again,
Iain

-----Original Message-----
Hi,


You can ALTER TABLE once it is created, to add a constraint to it.


Vanderghast, Access MVP


Hi all,

I would like to create a make table query and specify a
primary key in the process.

This is the SQL statment for my make table query:

SELECT [Original].[Date], [Original].[Split], [Original].
[Apples], [Original].[Bananas], [Original].[Pears],
[Original].[Grapes] INTO Made
FROM Original;

I would like the field [Original].[Date] to become the
primary key in the new table.

Is their anyway of doing this?

Thanks
Iain
Click to expand...


.
Click to expand...
 
Hi,


CurrentProject.Connection.Execute "ALTER TABLE toto ADD CONSTRAINT
PrimaryKey PRIMARY KEY (ListOfFieldNames) ; "




PrimaryKey is the name of the constraint, you can choose the one you like.

The list of field names can be of just one field, if the primary key is not
composite.


It probably work with CurrentDb.Execute too, instead of
CurrentProject.Connection.Execute (I didn't try recently with CurrentDb). If
so, I suggest you use the optional flag dbFailOnError.

You can remove the constraint with:


CurrentProject.Connection.Execute "ALTER TABLE toto DROP CONSTRAINT
constraintNameHere ; "



Hoping it may help,
Vanderghast, Access MVP




Iain said:
Thanks for the info Michel,

How would I be able to do this?

Thanks again,
Iain

-----Original Message-----
Hi,


You can ALTER TABLE once it is created, to add a constraint to it.


Vanderghast, Access MVP


Hi all,

I would like to create a make table query and specify a
primary key in the process.

This is the SQL statment for my make table query:

SELECT [Original].[Date], [Original].[Split], [Original].
[Apples], [Original].[Bananas], [Original].[Pears],
[Original].[Grapes] INTO Made
FROM Original;

I would like the field [Original].[Date] to become the
primary key in the new table.

Is their anyway of doing this?

Thanks
Iain
Click to expand...


.
Click to expand...
Click to expand...
 
I would like to create a make table query and specify a
primary key in the process.
Click to expand...

You can't, as Michel correctly notes.

I must ask - why the maketable query at all? I find that they are
*very* rarely of real value; you can base a Report, an export, a Form,
even another Query on a select query, without the overhead and
bloating caused by running a MakeTable query. Why is this make-table
operation needed in the first place?
 
Back
Top