Compacting and Repairing Access

[Guest]

Is thea workaround to comapcting a repairing a database so that you do not
lose a Auto number field. My database appends excel data to a temporary data
table - which in turn gives each entry a unique auto-number. After which a
macro runs to clear the entries ready for the next 'upload'What i find is
that after a while the data tables become bloated and i need to repair and
compact. After this operation the auto numbering sequence in the temp table
reverts back to 1.

 

[Arvin Meyer]

You can append a dummy record with the autonumber set to 1 less than the
next number you want.:

Insert Into [MyTable] ( AutoNumberField) Select 16099 As Expr1
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Joseph Meehan]

Is thea workaround to comapcting a repairing a database so that you
do not lose a Auto number field. My database appends excel data to a
temporary data table - which in turn gives each entry a unique
auto-number. After which a macro runs to clear the entries ready for
the next 'upload'What i find is that after a while the data tables
become bloated and i need to repair and compact. After this
operation the auto numbering sequence in the temp table reverts back
to 1.

I suggest you may not want to use Autonumber for that use. Autonumbers are
designed to provide unique numbers. It in not designed to provide numbers
in order and for a number of reasons may not do so. As a result using them
in any application where the user sees the numbers is likely to end up with
confusion.

There are other ways of providing the numbers you want depending on the
particual application.

 

[Larry Daugherty]

It sounds like things are working just as they're designed to do.

HTH