Leaving "Holes" in a Table

[Ken Ray]

> On Wednesday, July 16, 2003, at 02:18 PM, Ken Ray wrote:
> 
> > When I delete record ID 507 from a table of 1000 records, 
> and then add
> > a new
> > record to the table, it gets #507, not #1001 as I would expect. Is 
> > there any
> > way to make this happen where #507 remains a "hole", and a 
> new record 
> > #1000
> > is added?
> 
> You can do this yourself. Add a VBoolean field IsDeleted, and simply 
> set the flag to mark records as deleted (also deleting VBlob 
> fields to 
> free up that space).

Thanks, but I already have a boolean "DeleteFlag" field; the problem is
that this flag is intended to allow the system administrator to remove
records marked for deletion this way from the database. Normally this
wouldn't be an issue, but I have in my interface that ability to "link"
one person to another through a join table which holds the RecID of each
person. If person "A" (RecID: 100) is joined to person "B" (RecID: 200),
and then someone marks person "B" for deletion, the sysAdmin actually
deletes the person, and then a new record is created, it takes up RecID
#200 and my join is messed up.

It looks like I'll either (a) never delete any records, or (b) manage my
own unique IDs so that new records will get *my* ID instead of
Valentina's.

Ken Ray
Sons of Thunder Software
Email: kray at sonsothunder.com
Web Site: http://www.sonsothunder.com/