Adding non existing autonumber field during a make table query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am linking to a data file, and want to automatically add a AUTONUMBER after
a make table query takes the linked file and makes a new table from it. I
will later append to this file...
 
Hi.
want to automatically add a AUTONUMBER after
a make table query takes the linked file and makes a new table from it.
Click to expand...

After the Make Table query is run, run another query to add the AutoNumber
field to the new table:

ALTER TABLE tblMyTable
ADD COLUMN ID COUNTER (1, 1);

.. . . where tblMyTable is the name of the table, and ID is the name of the
AutoNumber column.

If you want this new column to be the primary key, then use the following
SQL statement in the new query instead:

ALTER TABLE tblMyTable
ADD COLUMN ID COUNTER (1, 1)
CONSTRAINT ID PRIMARY KEY;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Another approach would be to not use a Make Table.

Instead, create the table definition for what you'll be using, and include
an Autonumber field. Then, append records to and/or delete records from
this table, as needed.

This also gives you a way to import 'raw' data, and use queries to parse it
into relational data before storing in your permanent table(s).

Regards

Jeff Boyce
<Office/Access MVP>
 
That worked like a charm. A simple way to save me from having to 1st execute
a query that deletes the record in a resultant make table query, then a
second query that has me append records to the resultant table.
Thanks so much!
 
Back
Top