Auto number ID in Access database
Auto number ID in Access database
Auto number ID in Access database
Auto number ID in Access database
Auto number ID in Access database
Auto number ID in Access database Auto number ID in Access database Auto number ID in Access database Auto number ID in Access database Auto number ID in Access database Auto number ID in Access database Auto number ID in Access database Auto number ID in Access database
Auto number ID in Access database Auto number ID in Access database
Auto number ID in Access database
Go Back  Xtreme Visual Basic Talk > > > Auto number ID in Access database


Reply
 
Thread Tools Display Modes
  #1  
Old 10-30-2017, 12:06 PM
Daigon Ali Daigon Ali is offline
Regular
 
Join Date: Aug 2014
Location: London
Posts: 59
Default Auto number ID in Access database


Hi I have a table in an Access database with about 100000 records.
The table has an ID field which is an autonumber. This ID is not used in any relationships with other tables is used to find the most recent entry for a given value in one of the other fields.

The problem is that now I need to add some records in the middle of the table. Is there anyway to renumber the records below so that the ID's remain sequential?
Reply With Quote
  #2  
Old 10-31-2017, 10:22 AM
dilettante's Avatar
dilettanteAuto number ID in Access database dilettante is offline
Underclocked lifestyle

Forum Leader
* Guru *
 
Join Date: Feb 2005
Location: Michigan, USA
Posts: 4,524
Default

Not really, aside from brute force changing each row beyond your insertion point. Messy because RDBMS table rows do not really have an order until you sort them.

What you are doing isn't what IDENTITY data type is for.
Reply With Quote
  #3  
Old 10-31-2017, 10:50 AM
Daigon Ali Daigon Ali is offline
Regular
 
Join Date: Aug 2014
Location: London
Posts: 59
Default

Unfortunately this is something I've inherited.
There is a date field, but if entries occur on the same date the ID appears to be the the only way the actual sequence of input to the table is recorded.

I guess I could export and re-order the data and recreate the table.
Reply With Quote
  #4  
Old 10-31-2017, 08:47 PM
Kluz's Avatar
KluzAuto number ID in Access database Kluz is offline
Sapience.Aquire

Super Moderator
* Expert *
 
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,445
Default

You could also rename the table, select into a new table of the original name then drop the original/renamed table.
__________________
No the other right mouse click
Reply With Quote
Reply

Tags
access, add, database, records, table, auto


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Auto number ID in Access database
Auto number ID in Access database
Auto number ID in Access database Auto number ID in Access database
Auto number ID in Access database
Auto number ID in Access database
Auto number ID in Access database Auto number ID in Access database Auto number ID in Access database Auto number ID in Access database Auto number ID in Access database Auto number ID in Access database Auto number ID in Access database
Auto number ID in Access database
Auto number ID in Access database
 
Auto number ID in Access database
Auto number ID in Access database
 
-->