Re-set autonumber in a table (primary key)

I have built a database and tested it thoroughly. I deleted the test records 
but now my first record has an automunber or 15 and this is winding me up. 
Can I re-set the autonumber back to 0?
0
Utf
1/11/2008 7:25:01 PM
access 16762 articles. 3 followers. Follow

10 Replies
930 Views

Similar Articles

[PageSpeed] 50

Try a compact and repair on the DB (front- or back-end) having that table.

"Biffo" wrote:

> I have built a database and tested it thoroughly. I deleted the test records 
> but now my first record has an automunber or 15 and this is winding me up. 
> Can I re-set the autonumber back to 0?
0
Utf
1/11/2008 7:29:01 PM
Delete the field.  Save the table.  Insert a new autonumber field

-- 
Wayne
Manchester, England.



"Biffo" wrote:

> I have built a database and tested it thoroughly. I deleted the test records 
> but now my first record has an automunber or 15 and this is winding me up. 
> Can I re-set the autonumber back to 0?
0
Utf
1/11/2008 7:30:02 PM
> Delete the field.  Save the table.  Insert a new autonumber field

....or you can simply execute something like this:
DoCmd.RunSQL "alter table Table1 alter column ID counter(1,1)"

For example counter(3, 10) means:
3 - initial value of counter
10 - step value.

K.P. MVP, Poland
www.access.vis.pl


0
Krzysztof
1/11/2008 8:25:45 PM
Why not not just
Delete the field.  Save the table.  Insert a new autonumber field
Much better for someone who has no records in a table that has just been 
created.

Mind you I never really bother what an autonumber field has in it.  As long 
as it's unique - thas all I'm bothered about.

You could also compact the DB

You could also ....etc etc

But still think it's better when 1st createing a table to ...
Delete the field.  Save the table.  Insert a new autonumber field






-- 
Wayne
Manchester, England.



"Krzysztof Pozorek [MVP]" wrote:

> > Delete the field.  Save the table.  Insert a new autonumber field
> 
> ....or you can simply execute something like this:
> DoCmd.RunSQL "alter table Table1 alter column ID counter(1,1)"
> 
> For example counter(3, 10) means:
> 3 - initial value of counter
> 10 - step value.
> 
> K.P. MVP, Poland
> www.access.vis.pl
> 
> 
> 
0
Utf
1/11/2008 9:35:00 PM
(...)
> Why not not just
> Delete the field.  Save the table.  Insert a new autonumber field
> Much better for someone who has no records in a table that has just been
> created.
>
> Mind you I never really bother what an autonumber field has in it.  As 
> long
> as it's unique - thas all I'm bothered about.
>
> You could also compact the DB
>
> You could also ....etc etc
>
> But still think it's better when 1st createing a table to ...
> Delete the field.  Save the table.  Insert a new autonumber field

Your advice is good and I agree with this, what you wrote.
Although, what it is better, it depends on what You need at that moment.
The "alter table... " gives the easy possibility of steering the counter 
with code. So it is less well-known than compact or recreate the autonumber 
field. Therefore I mentioned about it.

Kris, Poland
www.access.vis.pl 


0
Krzysztof
1/11/2008 11:05:49 PM
Wayne,

I prefer the advice given by Bob, to simply Compact the database.  Your 
idea is more difficult, but still fine, but could get further 
complicated if this table has Relationships defined based on this 
Autonumber field, etc.

-- 
Steve Schapel, Microsoft Access MVP

Wayne-I-M wrote:
> Why not not just
> Delete the field.  Save the table.  Insert a new autonumber field
> Much better for someone who has no records in a table that has just been 
> created.
> 
> Mind you I never really bother what an autonumber field has in it.  As long 
> as it's unique - thas all I'm bothered about.
> 
> You could also compact the DB
> 
> You could also ....etc etc
> 
> But still think it's better when 1st createing a table to ...
> Delete the field.  Save the table.  Insert a new autonumber field
0
Steve
1/12/2008 12:10:10 AM
Hi Steve

Yes you are right.  I assumed it was a stand along table.  -  Is it too late 
to make a new year's resolution - don't make assumptions.  :-)


-- 
Wayne
Manchester, England.



"Steve Schapel" wrote:

> Wayne,
> 
> I prefer the advice given by Bob, to simply Compact the database.  Your 
> idea is more difficult, but still fine, but could get further 
> complicated if this table has Relationships defined based on this 
> Autonumber field, etc.
> 
> -- 
> Steve Schapel, Microsoft Access MVP
> 
> Wayne-I-M wrote:
> > Why not not just
> > Delete the field.  Save the table.  Insert a new autonumber field
> > Much better for someone who has no records in a table that has just been 
> > created.
> > 
> > Mind you I never really bother what an autonumber field has in it.  As long 
> > as it's unique - thas all I'm bothered about.
> > 
> > You could also compact the DB
> > 
> > You could also ....etc etc
> > 
> > But still think it's better when 1st createing a table to ...
> > Delete the field.  Save the table.  Insert a new autonumber field
> 
0
Utf
1/12/2008 2:16:01 PM
Hi Krzysztof

You are correct if there are relationships.  You method would be better as 
mine would cause problems.  I assumed it was a new stand alone table.


-- 
Wayne
Manchester, England.



"Krzysztof Pozorek [MVP]" wrote:

> (...)
> > Why not not just
> > Delete the field.  Save the table.  Insert a new autonumber field
> > Much better for someone who has no records in a table that has just been
> > created.
> >
> > Mind you I never really bother what an autonumber field has in it.  As 
> > long
> > as it's unique - thas all I'm bothered about.
> >
> > You could also compact the DB
> >
> > You could also ....etc etc
> >
> > But still think it's better when 1st createing a table to ...
> > Delete the field.  Save the table.  Insert a new autonumber field
> 
> Your advice is good and I agree with this, what you wrote.
> Although, what it is better, it depends on what You need at that moment.
> The "alter table... " gives the easy possibility of steering the counter 
> with code. So it is less well-known than compact or recreate the autonumber 
> field. Therefore I mentioned about it.
> 
> Kris, Poland
> www.access.vis.pl 
> 
> 
> 
0
Utf
1/12/2008 2:17:01 PM
Hi Wayne,

On the other hand, I am only right if one makes the assumption ;-) that 
they are using Access 2003 or earlier.  It appears that compacting the 
database does not re-set the Autonumber seed in Access 2007!

-- 
Steve Schapel, Microsoft Access MVP

Wayne-I-M wrote:
> Hi Steve
> 
> Yes you are right.  I assumed it was a stand along table.  -  Is it too late 
> to make a new year's resolution - don't make assumptions.  :-)
> 
> 
0
Steve
1/13/2008 2:07:19 AM
Oooopos!!  Correction - sorry!! That is not correct.  I was testing on a 
remote server, which was somehow messing things up.  When doing it 
locally, it behaves as in earlier versions - I should have paused before 
posting!

-- 
Steve Schapel, Microsoft Access MVP

Steve Schapel wrote:
> Hi Wayne,
> 
> On the other hand, I am only right if one makes the assumption ;-) that 
> they are using Access 2003 or earlier.  It appears that compacting the 
> database does not re-set the Autonumber seed in Access 2007!
> 
0
Steve
1/13/2008 2:17:38 AM
Reply:

Similar Artilces:

Insert key
How can I re-enable the function of the Insert key so it will overwrite if I want it to do so? If you're in Word 2007, click the Microsoft Office Button , and then at the bottom of the dialog, click Word Options. In the left pane, click Advanced. Under Editing options, select the "Use the Insert Key to control overtype mode" check box. In 2003, it was under Tools - Options, but I can't remember which tab it was on there. I hope this helps. "David" wrote: > How can I re-enable the function of the Insert key so it will overwrite if I > w...

Setting the X axis scale/font
Hi All..... I don't do much charting, and I'm trying to set the scale on my X axis. What I would like, is similar to a ruler, with Large font at the 1 marks, medium font at the 1/2 marks, and small font or just tick marks at the .1 marks. I don't seem to be able to find where/how to make those adjustments. I can't even get the "minor" scale increments to work, such as 1 for major and .2 for minor.......the best I've been able to do is to set both major and minor to .2 and then I get them all, but all at the same font size.......my X range is 0-4, and I...

Reporting security settings
Hi all, Is there a way, via GP or SQL, to report on user access at the menu level. In other words what a user has access to in the menu / submenu dropdowns. We're working with GP 8.0 and the only report I can get, including exporting XML out of Advanced Security, is too detailed. There must be some reporting 3rd party product or transact SQL script or table joins I can use to get this information. The user reporting straight of GP isn't too flexible. Thanks, -- Jim Bourque Fastpath has a product that should work for what you need. www.gofastpath.com Mark On Dec 14, 10...

Re-arranging table using pivot-table?
I have a worksheet with data organized somewhat like this: New York New York New York Albany Florida Miami Florida Orlando Florida Tampa Florida Jacksonville California Los Angeles California San Diego I need to convert this to a list of states and each city in the columns to the right of their corresponding state. Something like this: New York New York Albany Florida Miami Orlando Tampa Jacksonville California Los Angeles San Diego Any help is greatly appreci...

how can i set my yahoo acount on outlook?
deepak nayak <deepak nayak@discussions.microsoft.com> wrote: <nothing> If it's a free yahoo.com mail address, then you can't without a tool like http://ypopsemail.com/ . If it's a paid account, consult Yahoo's Help pages or the Help pages of the ISP who supplied you with the account. -- Brian Tillman [MVP-Outlook] ...

setting text result set more then 8192
Hello there I am building system which take procedures from one place, displaying the script of store procedure and then i taking it and run it in a different server. But if the procedure script is more then 8192 it cutts it in the result. Is there a way not to cut it? On Thu, 8 Jul 2010 17:24:54 +0300, "Roy Goldhammer" <royg@yahoo.com> wrote: >Hello there > >I am building system which take procedures from one place, displaying the >script of store procedure and then i taking it and run it in a different >server. > >But if th...

Re-install Outlook 2002
I am trying to re-install Outlook 2002 for my palm pilot after a crash. The installation will run until I get the message - "another version is already installed and must be uninstalled". The previous version was corrupt and I was unable to uninstall it. Now when I go into the control panel to add/remove the uninstall is no longer in the listed. Help! ...

IE Settings for MS CRM.
I am not able to access CRM through IE. I changed my computer. I was earlier set with a short cut to the CRM application. I am trying to access the same using IE. I enter my credentials. I get the screen of 'Attempting to open Microsoft CRM. If the application does not load within a few seconds, click HERE. If you are asked if you want to close the currently open window, click yes.' and automatically IE is closed before any thing comes up. 1. I have unchecked my pop-up blocker. 2. I have added the site as a trusted site. Are there any other IE settings that need to me made? ...

Pivot Table Calculated Item #3
I am querying an external database to retrun a pivot table, then i am adding a calculated field that calculates Regular hours * Bill rate. to come up with total revenue. When it runs, the calculated amounts are 2, 3, 4 times more that what it should be. any ideas how i can get this calculation to return the right amounts. thanks Jerry H5 erry Excel stores hours as fractions of a day, so multiply Hours * 24 * Bill Rate Regards Roger Govier jerry wrote: > I am querying an external database to retrun a pivot table, then i am adding > a calculated field that calculates Regular...

Re-Enable CRM User
Hello Everyone, I am trying to enable a disabled CRM user and I received the following error message. Any thoughts or suggestions would be greatly appreciated. [COMException (0x80040204)] Microsoft.Crm.Platform.ComProxy.CBizUserClass.Enable(CUserAuth& Caller, String UserId) +0 Microsoft.Crm.Application.Platform.SystemUser.Activate(String id) +38 Microsoft.Crm.Web.BusinessManagement.SystemUserDetailPage.changeState(Object sender, DataEventArgs e) +127 Microsoft.Crm.Application.Forms.DataEventProcessor.Raise(FormEventId eventId, FormState state, User user, String objectId,...

Problems setting up SSL on Exchange 2003 Front End server
Hi, I've had a 2-node 2003 cluster running for a while now. I'm just in the process of setting up a FE server for this. I've installed it and got it running, and all looks ok, but now I've come to setting up SSL I'm running into problems. I have installed a an Enterprise Root CA on a server in the forest root. The Exchange servers are all running in the sub-domain of this. I've followed the instructions found on this website: http://tinyurl.com/4xa74 All seems to go well up until step 14 of the section called "Creating the Certificate Request". I get ...

Pivot Table #2
I have a database with type and age ranges in columns. Six age ranges. One type column. The data (dollars), by type and age range are below each age range column. I want a summary report that sums the dollars by type and age combined. My preference is to have the sum of age ranges in the rows, the type in the columns with the dollar sum of each type and age range in the data fields. I hope to have the sum of the types for all ages on the right and the sum of the ages for all types at the bottom. It seems simple enough but, I don't seem to be able to make the table work? If ...

RE:Excel will not open from a web link
When opening a link on a web page that has an Excel Spreadsheet embedded in it, the spreadsheet will not open up Excel but opens within another browser as an Excel file. Programming on webpage is correct, opens up on other machines just fine. ...

Thanks Ken Wright but one more question RE saving data from circular references
Thanks heaps for that macro Ken Wright . I have run it and it works. However it loops forever. Can I set it up so that it could save dat from a certain number of iterations? Thanks again Michae -- Message posted from http://www.ExcelForum.com Hi Michael, If you stayed within your thread and did not change the subject Ken would see your reply as a reply to his reply and would see it a lot faster than when you start a new thread. Ken would probably see your post highlighted in RED. Also someone other than Ken might have been able to help you. (not me I avoid anything that hints of circul...

Colour Banding in pivot tables
Hi all, Is it possible to do colour banding in pivot tables? I have searched all avaible AutoFormat and I could find none, nor could I do it using conditional formatting. Thanks a lot. Frederick Chow Hong Kong. Frederick, My Excel add-in "Shade Data Rows" seems to work on Pivot tables. I just tried it out on a pivot table as have never had the need. It shades groups of like valued rows or by every nth row. Does the selection width or the entire sheet width. Choice of colors and an option to skip hidden rows. Comes with a one page Word.doc install/use file. Free upon direc...

Re: exmerge error
I am getting the following error when trying to use exmerg can anyone please help *************************************************************** Microsoft Exchange Mailbox Merge Program, v4.00.02 Start Logging:February 27, 2004 20:04:3 *************************************************************** [20:04:31] Logging Level: Non [20:04:31] Reading settings from file 'C:\Program Files\Exchsrvr\BIN\EXMERGE.INI' [20:04:32] Error 8007203a opening an LDAP connection. ('LDAP://EXCHANGE/rootDSE') (CADRoutines::GetNamingContextData [20:04:32] Accessing Domain Controller 'PROFI...

Ron Bovey re: "Trim Selection" in ExcelUtilities
Cells in my Excel spreadsheet appear to be empty. However, when I use Edit/Go To/Special/Blanks, I get "No cells were found". In the past, Ron Bovey suggested going to the WWW.APPROS.COM website, download ExcelUtilities and then use the "Trim Selection" utility to remove the characters that are making the cells not empty. Since then, I've gone to the WWW.APPROS.COM website and downloaded the ASAP Utilities. However, I can seem to find the "Trim Selection" nor any utility that will do the same thing. Help! Try going to Rob (not Ron) Bovey's site: ...

How do I get invisible lines & borders to re-appear?
In Publisher 2003, table grid lines, text box borders and autoshape borders have become invisible on-screen, although they do print out. Any ideas on how to remedy this? Recently installed Adobe PDF Maker which seems to have precipitated this, although it could be entirely co-incidental. Try updating your video drivers. -- JoAnn Paules MVP Microsoft [Publisher] "Ackerman & Co" <Ackerman & Co@discussions.microsoft.com> wrote in message news:0FFE40C2-D6A8-43AE-89E4-061EBB6501DB@microsoft.com... > In Publisher 2003, table grid lines, text box borders and auto...

re re re ????
nefertiti ??? ...

How do I set up another pst file with password ie Have a general .
I am running Outlook 2000, I wish to have a general folder for all to access, and then my own personal folder, passworded....Is it possible??? Please help!!! Thanks What I would do is the following: Go to Tools > Services > Add > Personal Folders. Then select location for the PST on your hard disk and set a password for it. This procedure should be followed, if the other users are using the same Windows logon to access this mailbox. Alternatively you can choose to Delegate access to only one folder to the users you need. To do this go to Tools > Options > Delegates to give...

tables without having MS Access installed
Hi everyone, I have a VB application that accesses (read and write) some data in a MS Access (mdb) database. Now, I want to distribute this application and the database and some of my users might not have MS Access installed. Is there some way to read and write to the tables without having MS Office installed. I do not need any access to forms, reports etc. but only the tables. I know Access uses some JET database engine and I was wondering if just installing that would suffice. Thanks for any help you can give me. Anja If your VB app does all the data manipulation, there's no need t...

table error problem
Hello there I have procedure who create temp table #tmp, store a lot of data inside it for moving it to another database. on any 5 rerun of the procedure in job i'm getting an error like: Attempt to fetch logical page (1:73868) in database 2 failed. It belongs to allocation unit 72057594108903424 not to 72057594124763136 I have recreated all the used tables. and it still happen. What can cause it? Roy Goldhammer (royg@yahoo.com) writes: > I have procedure who create temp table #tmp, store a lot of data inside it > for moving it to another database. > ...

Pivot Table Sum
I have created a PivotTable using data in another spreadsheet. For some reason when I try to sum the data in the Data Field, all the values turn to zeros. But if I leave it as a count the information is correct. Is it because the data in the original cells are formulas? Any help or suggestions would be greatly appreciated. Thanks -- RedFive One way to test your theory is to take the data source & change them to values instead of formulas. I've seen this occur when trying to aggregate blank data. -- http://www.ExcelHelp.us ed@ExcelHelp.us 888-MY-ETHER ext. 01781474 &q...

ERROR: attempting to set fiscal settings.
Hi all, A desperate post here in an attempt to find a solution as the last thread ground to a halt with no resolution. Three clients now, two SBS 03 and the other Pro 03 installs. Both fresh, not upgrades. ERROR: "An error has occurred. For more information, contact your system administrator." Anyone seen/solved this??? Thanks. Regards, Nathan Nathan, I too have this problem. It has existed since the first clean install of the system. Our Microsoft CRM Partner doesn't have a solution. Regards, Mark On Mon, 1 May 2006 23:44:01 -0700, Nathan Warner <NathanWarner@di...

Need product key for Office XP Developer
Please take pity on the absent minded. Back in the heyday of Office XP, I never had the need to set up the developer tools for it. I was a subscriber to MSDN Universal so have all of the DVDs. I just never jumped through their hoops of going on the MSDN site to lookup the product and get the key. I didn't need it then. Now it's out of support and the keys for obsolete products are no longer available from MS. My email address is readily decodable. I'll be most grateful. Thanks -- -Larry- -- To anyone who may have been digging in their archives on my behalf; Thanks, but ...