Code to compact database and reset autonumber to 1 1

[hnunez4399]

Is there a line of code that can be ran thru a macro, to reset my autonumber field back to 1? I know I can compact the database using the menu options but I wanted to automate this to run after a specific macro so a user does not have to do this manually.

Thanks in advanced.

 

[ddiamond]

A database can compact other closed databases, but it cannot compact itself using VBA code. One option would be to shell to a visual basic program that will wait a few seconds while displaying something to the user, then compact the database, then reopen the database. The initial few second pause give the database a chance to close itself. To compact the database from visual basic,
x=shell(MSAccessPath & " " & DatabaseName & " /Compact")

 

[ddiamond]

Do you want to reset the autonumber on a blank table? If so, the easiest way to do this is to drop the table and then recreate it using DDL queries.

 

[hnunez4399]

I really don't know. I import a file from Excel which has a unique auto number. I then need to break out the file into 3 parts. WIth eacn new file starting the auto number at 0 again.... don't know that makes sense.

 

[LNBruno]

Sounds like you might be better off creating your own autonum for the access table(s) and leave the Excel "autonum" (which really isn't if every file starts with zero) as a cross-reference to the external key?

< M!ke >

 

[ddiamond]

For your question about compacting the database,
see thread705-1238693.

 

[PHV]

Create an action query with the following SQL code:
ALTER TABLE yourTable ALTER COLUMN yourAutonumber COUNTER(0,1);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ddiamond]

PHV,

That's a good tip.