Re: Creating an autonumber ID field in a make table query?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.

"Jeff Boyce" <nonsense@xxxxxxxxxxxx> wrote in message news:eZhDb1zbIHA.1208@xxxxxxxxxxxxxxxxxxxxxxx
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

"mcl" <nospam@xxxxxxxxxx> wrote in message news:87D28C22-CBA2-492B-A488-7F5BC2DFCB51@xxxxxxxxxxxxxxxx
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?

"Jeff Boyce" <nonsense@xxxxxxxxxxxx> wrote in message news:exEhbulbIHA.3696@xxxxxxxxxxxxxxxxxxxxxxx
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

"mcl" <nospam@xxxxxxxxxx> wrote in message news:CBDA8023-E018-4EE7-A4A5-F129D89D591D@xxxxxxxxxxxxxxxx
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.

"Jeff Boyce" <nonsense@xxxxxxxxxxxx> wrote in message news:e0%237EsdbIHA.4180@xxxxxxxxxxxxxxxxxxxxxxx
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

"mcl" <nospam@xxxxxxxxxx> wrote in message news:B0560E74-CBF5-40A4-A95F-E75B50CAFEBB@xxxxxxxxxxxxxxxx
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.









.

Relevant Pages

  • Re: Creating an autonumber ID field in a make table query?
    ... It worked but the autonumber ID field doesn't reset to 1. ... "Jeff Boyce" wrote in message ... I link access to our oracle database through ODBC and download the data for a particular station and then crunch the data. ... I use a pass through query as a front end to a "make table query". ...
    (microsoft.public.access.queries)
  • Re: Is my query at fault for a slow form?
    ... "Jeff Boyce" wrote: ... Disclaimer: This author may have received products and services mentioned ... The query takes about 10 seconds to run so may play a small role in the ...
    (microsoft.public.access.forms)
  • Re: Multiple requests for parameter value
    ... Can you give me an example of a "flattening" query? ... "Jeff Boyce" wrote: ... Microsoft Office/Access MVP ...
    (microsoft.public.access.reports)
  • Re: Problem with relationship??
    ... Have the SQL (or the query) refer to the form on which the user selects the ... "Jeff Boyce" wrote: ... Product] it will return ALL the products I have that require seriel ... Then you design a report, based on the data the query returns. ...
    (microsoft.public.access.gettingstarted)
  • Re: Renaming field names of Linked Table in query
    ... this friendly name table and have those names appear when I run a query. ... "Jeff Boyce" wrote: ... Your forms can display whatever labels you want. ... in the query I could manually rename GLACT by ...
    (microsoft.public.access.queries)