Re-using deleted primark key

Can I re-use the PK for a record in a local table before a c & r is performed?

Lots of questions and answers on variations of this question but they seem to be focussed on inserting records into an AutoNumber sequence which isn't what I want.

I know that we don't normally use the PK for user-visible app functionality. In this scenario, I simply want a list of record ID numbers for some process to be performed. It occurred to me that I can simply insert a record into the local 'selections' table using the same PK as the main table. So it's a one-column table with a 1 to 1 relationship.

User may check a selection (insert record using PK eg 123), uncheck the selection (delete record 123), re-check same record (Access attempts to insert record using PK 123). User doesn't know or care that it's record 123 it will just ease my coding plus since the PK is always indexed it seems like an efficient way to do it. Obviously the PK for the selections table wouldn't be AutoNumber it would be what I code it to be (PK of main table)

I don't want to write a tag to the main table, 2 users could be viewing an overlapping sub set of records and want their own unique selection list.

I do not really understand the logic, or why you would need to do this, but there is no reason you could not reuse a PK.

Assume you are generating you own PKs (not autonumber). If you establish your relationship to enforce referential integrity and cascade deletes, then if you delete PK ABC it will delete all chidl records with ABC. You could then reuse ABC, instead of generating a new PK ABD.

If this is not autonumber field and not attempting to duplicate a number, then exactly what is the issue?

If there will be multiple users doing this selection, then need this 'temp' selections table in the frontend so users will not conflict. Is that what you meant by 'local' table?

I reuse PK's in one and ONLY one instance. When I have a variable crosstab report. I always want the generated IDs to be 1-n (with n being a max of 15) because that's how the columns are mapped and the code in the report looks up value 1 to determine what header to display for that column and value 2 to determine what header to display for column 2, etc.

In NO OTHER circumstance would I recommend reusing a primary key.

Hi.

GK in the UK said:

Can I re-use the PK for a record in a local table before a c & r is performed?

Click to expand...

Yes, you can, but why? Also, it doesn't matter if you do it before or after a C&R, because you can "reuse" it any time you want anyway.

GK in the UK said:

I know that we don't normally use the PK for user-visible app functionality. In this scenario, I simply want a list of record ID numbers for some process to be performed. It occurred to me that I can simply insert a record into the local 'selections' table using the same PK as the main table. So it's a one-column table with a 1 to 1 relationship.

User may check a selection (insert record using PK eg 123), uncheck the selection (delete record 123), re-check same record (Access attempts to insert record using PK 123). User doesn't know or care that it's record 123 it will just ease my coding plus since the PK is always indexed it seems like an efficient way to do it. Obviously the PK for the selections table wouldn't be AutoNumber it would be what I code it to be (PK of main table)

Click to expand...

Regarding the bolded parts above. it seems the user "will know" which PK they are selecting, so they must care about it somehow. And as you said in the beginning of the above quoted post, this is not something we normally do.


Also, you said this:

GK in the UK said:

...it will just ease my coding...

Click to expand...

You may actually be doing it harder than you need to, if you're having to think of using or doing something that is not what one would normally do.


And this part:

GK in the UK said:

...plus since the PK is always indexed it seems like an efficient way to do it.

Click to expand...

Being indexed means the order of the data is set to ease with searching. This also means, I think, having a gap wouldn't matter. So, I am not sure what advantage you were thinking you were going to get by reclaiming deleted PK values.


Just my 2 cents...

June7: Yes the 'selections' table will be in the front end so yes I've called it local (sorry wrong terminology). The issue? There may not be one. User checks (selects) a record in the be table, which happens to have an ID of 123. It's presented in a datasheet format with a checkbox. So I insert a record into the fe table and assign it a PK of 123 and all is good. When I want to process the selected records, I say (in SQL which I'm not yet proficient in) if there exists a record in tblSelections which has the same ID as the record on the main table, then the main table record is considered 'selected'.

But (potential issue) user unchecks the selection. The record in tblSelections which has the same ID (123) as the selected record in the main table is immediately deleted. So the 'selection' has gone.
User then immediately re-checks the same record. The app then attempts to insert a record into the fe table, which has an Id of 123. Which we deleted just a moment ago. Will Access allow it?

Sorry folks I have something which seems to work in my mind but I can't relate it or once again I seem to be asking for something that's a bit odd.
I want to present a list of records in a datasheet format and there is a check box against each record and the user checks the records for processing.
The user may repeatedly check and uncheck the same record as users do.
Each user may make their own unique selection and the records offered up for selection may be the same ones so I can't write a flag to the record - they will conflict.

I can do it like this (suggestions for better methods are welcome)

tblSelections (in the fe)

ID as AutoNumber (PK)
SelectedRecordID as Long (the record ID of the record that the user selected for processing)

But it seemed to me that I could use ID and not bother with the field SelectedRecordID. Why don't I just write a single record to tblSelections, which I code to have the same ID as the ID of the selected record. By definition they must be unique.

How do you experienced Access pros do this kind of thing?

Yes, Access will allow it.

It's the check field in BE table that has me concerned. This is where user conflicts can occur.

I've never had to do anything like this, although read and participated in numerous discussions on this topic. My users select criteria (such as a zip code value) for filtering records, not arbitrarily select random records.

A common approach is to have users do a preliminary sort on some criteria (perhaps UserID if this is a value in records), write records (perhaps just record ID values) to FE temp table and there do arbitrary selection of records. INNER JOIN FE table to BE table to retrieve desired records.

Thanks, June7 (and all contributors). The check box is really just a flag to tell the app to write the record to tblSelections. To be honest I'm not sure how I'll deal with that yet because it doesn't need to exist as a field in the record in the be table.

I think it can be done by creating a SELECT which joins the two tables and will show the check box as checked if there is a matching ID in the two tables. I suppose I will have to requery the select in the AfterUpdate event of the check box.

So we're saying that the deleted record which had an ID of 123, won't cause Access to throw an error if I then try to insert a new record with an ID of 123.

Thanks all

As long as the field was not a true "Autonumber" field then you can re-use the PK. I.e. if it is generated in some way other than "Autonumber" then yes it is possible.

If the field was autonumbered though, you have a problem in that Access won't let you put a value into an autonumber field EVEN IF IT WAS THE ORIGINAL VALUE YOU JUST DELETED.

There is nothing technically wrong with taking an existing record with an autonumbered field and having a flag in it that says "eligible for re-use." In which case you just overwrite the fields (except the autonumber.)

The_Doc_Man said:

If the field was autonumbered though, you have a problem in that Access won't let you put a value into an autonumber field EVEN IF IT WAS THE ORIGINAL VALUE YOU JUST DELETED.

Click to expand...

An arbitrary number can be put into an autonumber field using an insert query to add a new record.

The problem then is that an incrementing autonumber continues from the number in the inserted record so you have to delete and reinsert the last record to get back to the usual sequence.

OR: running C&R after INSERT will reset the autonumber seed. However, entering value into autonumber field is not really what OP is asking about.

June7 said:

OR: running C&R after INSERT will reset the autonumber seed.

Click to expand...

That used to be the case but several years ago Microsoft issued advice that it was no longer effective. I haven't tested lately to see if that situation has changed.

Works in Access 2010. Can't answer for later versions.

Darn... and here I believed MS when some time ago they said you couldn't do that to an autonumbered field. Next thing you know they will make the 64-bit version break the 2 GB barrier.