Output Inserted ID in MS Access & Coldfusion

Posted on 2008-10-29
Last Modified: 2012-06-22
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#','','')



<cfdump var="#inserted#">


Open in new window

Question by:bjv211
  • 3
  • 2
  • 2
LVL 42

Assisted Solution

dqmq earned 200 total points
ID: 22836265
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.

Author Comment

ID: 22836469
Thanks for the overview, in your opinion what would be the best combination for the key here?
LVL 42

Expert Comment

ID: 22836886
What does one row of the table represent?  
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.


Author Comment

ID: 22837433
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
LVL 59

Accepted Solution

Kevin Cross earned 300 total points
ID: 22837679
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.

Author Comment

ID: 22838082
mwvisa1, thanks for your post, you gave me a good search term and I discovered this  which seems reliable and efficient.
LVL 59

Expert Comment

by:Kevin Cross
ID: 22840341
Very nice!

Glad you found that.

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Create Form using Wizard 14 29
Excess tables to Excel BackUp 3 27
Operation must use an updatable query 4 23
Error: Operation must use an updateable query 2 9
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

937 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now