Both of these approaches suggested will usually work but both are prone to occasional
failure. There have been several posts here dealing with the same problem in SQL
Server, in
which the best solution has been to use a stored procedure to return the identity
value of the PK field just inserted. Since you can't do that in Access, here's
another method that's
almost as good and far more reliable than either method suggested so far:
Create a new table Next_Nbr with two fields: FieldName and NextNbr. FieldName is a
text field and the PK, use it to hold the name of any PK fields in tables you want to
run INSERT
queries on. NextNbr is a numeric field, not autonumbering, and so should be the PK
fields in the tables you're INSERTing new records into. Write a little CF routine
(and turn it
into a custom tag if you'll be using it a lot) that first queries the Next_Nbr table
to get the next number for the appropriate field when you want to insert a new record
into a
table, and then increments the number in the Next_Nbr field by one. Then you just use
the value you grabbed from the Next_Nbr table in your INSERT query and for any other
processing
you need after the INSERT query is done. Put the queries to Next_Nbr and your other
queries inside a CFTRANSACTION tag so they'll all roll back if there's any problem.
This method has worked very well for me in many situations. Unfortunately I can't
post the code because I didn't write it and don't have permission to give it away, but
it's very
easy to put together once you understand the basic concept. Once when doing some work
for a client where it wasn't possible to add a new table to their database, I modified
the
routine to update a number in a text file instead of a NextNbr table, and that worked
well too, though it wasn't the best option from a performance perspective. Still, it
beats the
hell out of hoping that there have been no other INSERTs before you do a SELECT
Max(ID) on a table or that a #content_just_submitted# value doesn't match a
#content_submitted_earlier#
record somewhere.
Regards,
Karl Simanonok
Original Messages:
============
Date: Sun, 23 Jul 2000 01:45:22 -0600
From: "Stephen R. Cassady" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Subject: Newbie needs a little PRO help
Message-ID: <000001bff479$f5894f20$[EMAIL PROTECTED]>
Isn't there a problem there with
> SELECT Max(ID) AS mid FROM Table_Name
in a busy database? As in somebody may have submitted another record in the
microsecond that elapsed between the post and the search of the ID?
Wouldn't it be better to
Select ID
>From Table_Name
Where BlahColumn Like "#content_just_submitted#"
(this works best where #content_just_submitted#" is a text field, or, add a
couple AND BlahColumn2 = "content2" AND Blah Column3 = ... ... ...
Stephen R. Cassady
Publisher, Spank! Youth Culture Online
http://www.spankmag.com
[EMAIL PROTECTED]
Date: Sat, 22 Jul 2000 16:45:09 -0700
From: "Mike Weaver" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Subject: Re: Newbie needs a little PRO help
Message-ID: <022c01bff436$df7102c0$31ba3dd0@main>
Thanks very much. That was it!!!
Mike
----- Original Message -----
From: "Stewart, Mark" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, July 22, 2000 2:26 PM
Subject: RE: Newbie needs a little PRO help
> If I understand you correctly, you need the "autonumber" that was just
> created when you inserted data into your db. If this is correct, try this:
>
> <cfquery name="name" datasource="datasource">
> SELECT Max(ID) AS mid FROM Table_Name
> </cfquery>
>
> Hope that helps...
>
> Mark
>
> -----Original Message-----
> From: Mike Weaver [mailto:[EMAIL PROTECTED]]
> Sent: Saturday, July 22, 2000 7:20 PM
> To: [EMAIL PROTECTED]
> Subject: Newbie needs a little PRO help
>
>
> This is a multi-part message in MIME format.
>
> ------=_NextPart_000_01FC_01BFF3F8.AC1EC580
> Content-Type: text/plain;
> charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
> I am developing an application which is going pretty well. I have =
> stumbled at a point where I need a query to generate the last entry in =
> an access database. I need the last entry in the "auto number" field =
> that is generated each time you make an entry. All assistance =
> appreciated.
>
> Mike
------------------------------
Date: Sun, 23 Jul 2000 01:48:00 -0600
From: "Stephen R. Cassady" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Subject: Newbie needs a little PRO help
Message-ID: <000101bff47a$54be2c40$[EMAIL PROTECTED]>
Or, would you lock the tables used in the set of queries that include the
> SELECT Max(ID) AS mid FROM Table_Name?
Sorry, still thinking aloud.
Stephen R. Cassady
Publisher, Spank! Youth Culture Online
http://www.spankmag.com
[EMAIL PROTECTED]
> If I understand you correctly, you need the "autonumber" that was just
> created when you inserted data into your db. If this is correct, try this:
>
> <cfquery name="name" datasource="datasource">
> SELECT Max(ID) AS mid FROM Table_Name
> </cfquery>
>
> Hope that helps...
>
> Mark
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
