Re-using deleted primark key (1 Viewer)

GK in the UK

Registered User.
Local time
Today, 16:39
Joined
Dec 20, 2017
Messages
274
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.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:39
Joined
May 21, 2018
Messages
8,463
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.
 

June7

AWF VIP
Local time
Today, 08:39
Joined
Mar 9, 2014
Messages
5,423
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?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:39
Joined
Feb 19, 2002
Messages
42,971
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:39
Joined
Oct 29, 2018
Messages
21,358
Hi.

Can I re-use the PK for a record in a local table before a c & r is performed?
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.



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)
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:

...it will just ease my coding...
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:
...plus since the PK is always indexed it seems like an efficient way to do it.
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...
 

GK in the UK

Registered User.
Local time
Today, 16:39
Joined
Dec 20, 2017
Messages
274
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?
 

June7

AWF VIP
Local time
Today, 08:39
Joined
Mar 9, 2014
Messages
5,423
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.
 
Last edited:

GK in the UK

Registered User.
Local time
Today, 16:39
Joined
Dec 20, 2017
Messages
274
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
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:39
Joined
Feb 28, 2001
Messages
26,999
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.)
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 03:39
Joined
Jan 20, 2009
Messages
12,849
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.

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.
 

June7

AWF VIP
Local time
Today, 08:39
Joined
Mar 9, 2014
Messages
5,423
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.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 03:39
Joined
Jan 20, 2009
Messages
12,849
OR: running C&R after INSERT will reset the autonumber seed.

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.
 

June7

AWF VIP
Local time
Today, 08:39
Joined
Mar 9, 2014
Messages
5,423
Works in Access 2010. Can't answer for later versions.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:39
Joined
Feb 28, 2001
Messages
26,999
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.
 

Users who are viewing this thread

Top Bottom