Output Inserted ID in MS Access & Coldfusion

I need to output the row ID of the last inserted row without using a MAX statement.  I have used the MAX statement in the past, however, it is not fool proof at accessing the correct ID. I am using CF 8 and understand that you can do this with the Result variables in cfquery however I am not able to do it using a MS access DB.  Is this a limitation with using MS Access?  Is there a way to do this using SQL?
<cfquery datasource="#application.dsn#" name="insertIT"  result="inserted">
INSERT INTO events (title,starttime,endtime,description,contact,marketing)
VALUES ('#form.title#',<cfqueryparam cfsqltype="cf_sql_timestamp" value="#starttime2#">,<cfqueryparam cfsqltype="cf_sql_timestamp" value="#endtime2#">,'#form.description#','#form.contact#','#form.marketing#')
</cfquery>
<cfoutput>
<cfdump var="#inserted#">
</cfoutput>

Open in new window

LVL 1
bjv211Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dqmqCommented:
Especially when you have an AutoNumber PK, it's important remember the natural key to your table.  The AutoNumber is generally a surrogate key and conveys no business meaning.   The table should also have a natural key (ideally, with a unique index) that uniquely identifies each row.

Without that, you could, for example, insert the same event twice with no means to tell them apart except the autonumber. Since that field has no business meaning, then you really do not know if you have a duplicate row or not.  Bottom line, you best have some combination of business-meaningful columns to identify a row.

It's that set of columns, or "alternate key", that identifies every row in business terms.  Lookup the ID column based on that key.
0
bjv211Author Commented:
Thanks for the overview, in your opinion what would be the best combination for the key here?
0
dqmqCommented:
What does one row of the table represent?  
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

bjv211Author Commented:
Basic information (date/time,contact person,location,title) of an event.  the current PK for this table is the Autonumber eventID which ties all the tables together
0
Kevin CrossChief Technology OfficerCommented:
If this where SQL, I would say use a <cftransaction> and just follow the INSERT with setting of a SQL variable with SCOPE_IDENTITY() and return the variable to CF.

Alternatively in CF, you could do (it has been a little bit for me and CF so forgive me if wrong tag) CFLOCK (lock application) and run query for insert and immediately following that SELECT MAX(ID) FROM TableName.  Unless you didn't want to use MAX for another reason other than synchronization/thread safety.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bjv211Author Commented:
mwvisa1, thanks for your post, you gave me a good search term and I discovered this http://cfmxplus.blogspot.com/2002/08/oh-my-select-identity-works-in-access.html  which seems reliable and efficient.
0
Kevin CrossChief Technology OfficerCommented:
Very nice!

Glad you found that.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.