Make Table Query and AutoNumbers

[Nicholajlg]

Hi, I can't find an answer that is quite right for this but there must
be a way to do it. Very simply, I have a make table query that is run
monthly. I want the resulting table to have an autonumber field -
which later on becomes part of a key for the line item. In the
meantime though, I can't make the autonumber field any way but
manually.

This field should be created automatically when I run the macro that
runs all of my queries. Barring a delete and then append query, how do
I do it?

Thanks!

 

[Rick Brandt]

Hi, I can't find an answer that is quite right for this but there must
be a way to do it. Very simply, I have a make table query that is run
monthly. I want the resulting table to have an autonumber field -
which later on becomes part of a key for the line item. In the
meantime though, I can't make the autonumber field any way but
manually.

This field should be created automatically when I run the macro that
runs all of my queries. Barring a delete and then append query, how do
I do it?

Thanks!

A delete, then an append query is about it I'm afraid. AutoNumbers aren't
the only data type that might not come out the way you want with a MakeTable
query. They generally should not be used in a production application, but
only for one-off development stuff.

 

[Nicholajlg]

What is the danger in using autonumbers in production?

 

[Rick Brandt]

What is the danger in using autonumbers in production?

Not AutoNumbers, MakeTable queries.

 

[RoyVidar]

Hi, I can't find an answer that is quite right for this but there
must be a way to do it. Very simply, I have a make table query that
is run monthly. I want the resulting table to have an autonumber
field - which later on becomes part of a key for the line item. In
the meantime though, I can't make the autonumber field any way but
manually.

This field should be created automatically when I run the macro that
runs all of my queries. Barring a delete and then append query, how
do I do it?

Thanks!

The counter keyword. Have a look in this reference for more info
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp
(watch for linebreaks)

CREATE TABLE MyTable (id COUNTER PRIMARY KEY,
MyNextField TEXT(25) NOT NULL UNIQUE, ...)"

Though, I agree there is seldom need to create tables on the fly.

 

[Nicholajlg]

OK I'll bite, what's the danger in using MakeTable queries?

 

[Rick Brandt]

OK I'll bite, what's the danger in using MakeTable queries?
Rick Brandt wrote:

I did not use the word "Danger". I just said that they are not a good idea in a
fully developed app. Why create and destroy something over and over in a manner
where you have little control over what you end up with when you can make the
table exactly as you want it (once) and then simply populate it and empty it
instead? The latter is more reliable, less work, and causes less bloat on the
file. It also has fewer security implications in an app with User Level
Security in place.

Even in places where I use temp table objects I do so by building them in
separate files using DAO/VBA, not by using MakeTable queries. That way I can
discard the entire file when finished and not bloat the main file.