Xtreme Visual Basic Talk

Xtreme Visual Basic Talk (http://www.xtremevbtalk.com/)
-   Database and Reporting (http://www.xtremevbtalk.com/database-and-reporting/)
-   -   Auto number ID in Access database (http://www.xtremevbtalk.com/database-and-reporting/328825-auto-id-access-database.html)

Daigon Ali 10-30-2017 11:06 AM

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?

dilettante 10-31-2017 09:22 AM

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.

Daigon Ali 10-31-2017 09:50 AM

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.

Kluz 10-31-2017 07:47 PM

You could also rename the table, select into a new table of the original name then drop the original/renamed table.

All times are GMT -6. The time now is 12:17 AM.

Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2018, vBulletin Solutions, Inc.
Search Engine Optimisation provided by DragonByte SEO v2.0.15 (Lite) - vBulletin Mods & Addons Copyright © 2018 DragonByte Technologies Ltd.
All site content is protected by the Digital Millenium Act of 1998. Copyright©2001-2011 MAS Media Inc. and Extreme Visual Basic Forum. All rights reserved.
You may not copy or reproduce any portion of this site without written consent.