Creating an autonumber ID field in a make table query?

M

mcl

Some time ago I asked this question and was told it couldn't be done.
Well, I'm asking it again. I know there is no existing MS Access function to
do this (using access 2003).
Is it possible to create a function to do it?
If yes please be specific. I don't know vba. About all I know about
"modules" is that it's one of the "Objects" choices in the upper left.
 
J

Jeff Boyce

An Access "autonumber" field is just that ... Access automatically
creates/assigns row identifiers.

Instead of trying to get Access to do something it doesn't understand, could
you use an Append query instead? That way, you could tell Access that it
has an Autonumber field, and you could append records to the OTHER fields.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
M

mcl

Not sure that would work. I'm using access as an analysis tool. I would have
to have it delete all records and then fill it in again. I have to think
about that. I'm a meteorologist. I link access to our oracle database
through ODBC and download the data for a particular station (all stations
have a station number) and then crunch the data. There can be as many as
500,000 for a particular station going back to the early 1900's but most
have maybe 100k-200k. I have tools to look for bad data, analyse what's
there, etc, etc. I use a pass through query as a front end to a "make table
query". Using a pass through makes things run a lot faster.
Is there some fast way in a macro to delete all records in a table. Right
now I manually edit the the station number in the pass through and then run
a macro which runs the make table query with the pass through that feeds it.
That way I can shut off all the warnings it gives. Right now I have to add
the ID field afterwards. If there are too many records I have to use an
statement in immediate mode to increase the number of locks? (if I got the
name right). It's a pain. I have other questions to improve this thing but
first things first.
If I could in a macro, delete all records, run an "append" query instead
with the make table as a front end that would do the trick.
 
J

John W. Vinson

If I could in a macro, delete all records, run an "append" query instead
with the make table as a front end that would do the trick.

DoCmd.RunSQL "DELETE * FROM tablename"

You'll want to compact (but you will need to compact if you delete and
recreate the table too).
 
J

Jeff Boyce

That is exactly what I was considering.

It may not work in your situation, but I frequently use a "tmp" table to
hold intermediate work products, then delete all the rows before re-use.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
M

mcl

It worked but the autonumber ID field doesn't reset to 1. It picks up where
it left off. It works OK but I would like to be able to reset it. Don't
suppose I can?
 
J

Jeff Boyce

Why would you care?

That is, are you trying to use Autonumber values as "meaningful"? Probably
don't want to do that, as they are not intended for that purpose.

That said, if you absolutely, positively must reset autonumbers, do a search
on "reset autonumber"...

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
M

mcl

I handled things a little dif than what was suggested. I created a query to
delete all the records then ran it then the other queries in a macro.
But if I just deleted all records, then did a compact & repair it reset it
to 1. That will work OK. It needs to be done compacted occassionally.

I'll be asking more questions. BTW, I'm accessing this newsgroup from home.
We can't connect from work.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top