You seem to be on the right track there.
Not sure where I'm being unclear? A little history:
I was able to write my query to SELECT the "SQLUser_MEMBER_ACCT" table and its three columns in question. THIS part worked perfectly.
The end result was the CREATE TABLE: "f_members".
Up to this point, everything works great.
However, the newly created table "f_members" needs a primary key to be searchable (it's going to be part of an Infopath repository).
Herein lies the problem; defining a primary key on the "f_members" table only works once; as the query updates the table (daily), it will overwrite the primary key specification, rendering the table useless. The query should RETAIN the primary key definition on the "acn" column - something that out-of-the-box MS Access cannot do. You can only define primary keys from the table design mode.
dev00790, your interpretation was correct but the syntax still fails MS Access' compiler. It expects a full stop after the first semicolon. MS Access does not support nested SQL statements. Is is possible at all to have these commands in ONE argument?
I sincerely appreciate your time and look forward to your response.
I'm not totally sure what you are trying to do, but I get the jist of it.
CREATE TABLE f_members2 AS
( acn Integer NOT NULL, acn_Status Varchar(255) NOT NULL,
PRIMARY INDEX (acn)
I presumed you wanted to: select three fields from SQLUser_MEMBER_ACCT table, then CREATE an empty table with columns defined, and setting a primary key on the table)
If this isn't what you need please explain clearly.
Edited by groovicus, 09 November 2011 - 03:11 PM.
Really no need to quote everything.