Okay, so this is actually in Access and ASP, but that's not my fault!
[The company I work for is really messed up in some ways.]
Anyway, it's an SQL question, and I don't want to actually join an
Access/ASP list, if such exist...
So, I've got a table of records with an "autonumber" unique ID.
When a web user inserts a new record, I need the ID back from *that* record
in an atomic operation. Since it is on the web, the potential for a race
condition is high.
insert into story (story_id, title) select Max(story_id) + 1, 'Title' from story
worked fine in Access. Well, it always gave one higher than the actual
value inserted for the ID, but I can subtract 1. Alas, when I send it over
the web, it inserts, but when attempting to get the resulting ID, I get a
message about a closed object being inaccessible. Presumably, Access is
trashing the temp table from this query. :-(
So, how do I go about doing this the right way?...
NOTE: There *ARE* records whose only distinct field is ID, so I can't just
follow up with a select for the data input.
If your answer is Access/ASP specific, please reply direct only. No sense
cluttering this list more than I have.
PS I guess I'd better join an Access/ASP list... Anybody know of one?
--
--
-- "TANSTAAFL" Rich lynch@lscorp.com