I need to insert a record into an Access database and get a handle on the primary key value for that record at the same time. The insertion part is easy, but how do I return the primary key value?
The primary key, which is an AutoNumber field, is the only unique value so I can’t do another SQL statement based on the inserted data, it will bring up multiple records.
Thanks in advance for the help.
Have you tried to get the max on that field before and after the insert? This would at minimum get you the approximate location.
It sounds like you have a bad table design if it has an arbitrary/sequence number for a primary key and no other unique item(s). It defeats the purpose of a relational database for a row to have no unique properties that make it different from all the other rows. C.B.'s suggestion will work but you have bigger problems.
Contact me away from the SDMB if I can help with this.
One (non-elegant) way to do it would to put some kind of suffix on the data (such as “-updated” or “999999”) that you insert so that you can identify it in the second query. Then simply run a third query to get rid of the suffix. What the suffix is depends on what type of field you are inserting into. Again, this is not very elegant, but if you need a one-shot deal, then this is a quick fix.
If you are inserting a whole record, just insert a Yes/No field and flag it as an insert, then delete per above procedure.
mavpace, I sent you an email which has a little more detail about what I am doing.
It sounds like your last suggestion may be the easist.
Winky, I got your email so I see what you’re trying to do. I still think that having a real property as the key is a better way to go. I’m an Oracle geek and we have some more flexibility in dealing with generated sequences for keys.
I think that adding the user’s name in combination with a time stamp, maybe rounded off to the minute, for a unique identifier in addition to your numeric key. SDMB doesn’t allow a single user to post twice in less than 60 seconds so this might work without changing your original design too much.