Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / July 2006

Tip: Looking for answers? Try searching our database.

Clear Database and Reset the AutoNumber

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
jillian.calderon@gmail.com - 21 Jul 2006 16:41 GMT
Good Morning Everyone,

I'm making a database and I'm playing with it using test data. When
I want to put the actual data in, how do I reset the autonumber back to
1? How do I tell the database to completely delete the fake records so
the autonumber doesn't start at, o, 50?

Thanks!
Jillian
Douglas J. Steele - 21 Jul 2006 23:06 GMT
Once you've deleted all the data, compact the database.

Having said that, though, why do you care? Autonumbers exist for one
purpose: to provide a (practically guaranteed) unique value that can be used
as a primary key. That purpose is met just as easily by 50, 51, 54 as by 1,
2, 3. Meaning should never be assigned to the value of autonumber fields. In
fact, usually you don't even show their values to the user.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Good Morning Everyone,
>
[quoted text clipped - 5 lines]
> Thanks!
> Jillian
René François - 31 Jul 2006 11:25 GMT
Douglas is right (as usual, of course!). However, if you really want to
reset the autonumber to 1 here's a way to do it:
1) Edit the table definition and change the autonumber field from Autonumber
to Numeric (Long Integer) and save the table.
2) Open the table (as I can undurstand it should be empty as you deleted all
the test data).
3) Compact the database and reopen it.
4) Edit the table definition again and revert the previously modified field
to Autonumber.

Once more, you should not assing any particular meaning to an autonumber
field. If you need a continuous numbering of records you should implement it
by yourself through a function.Try something like this:

Function GetNextNumber as Long

   dim lngReturnValue as long
   '
   ' Number is the name of the column containing the sequancial numbers
   ' MyTable is the name of the table you're working with
   '
   lngReturnValue = (DMax("Number", "Mytable") + 1)
   GetNextNumber=lngReturnValue

End Function

> Good Morning Everyone,
>
[quoted text clipped - 5 lines]
> Thanks!
> Jillian
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.