Insert auto-number from newly inserted row in Access with Coldfusion

Question

I have a few tables: a medError table, an option table, and a selected_options table.

When somebody submits the relevant form, I have an insert which inserts all the info from the form to the medError table. However, on this form, there is a section of dynamic checkboxes based on the items in the option table. The selected_options table holds the ID of the row in the medError table along with selected options based on the IDs in the option table.

How would I take the ID # from the newly inserted row in the medError table and insert it into the selected_options table?

I'm using Coldfusion and Access.

Answer 1

See options #2 or #3 (@@IDENTITY -or- UUID column) for methods that should work with MS Access. http://mysecretbase.com/get_the_last_id.cfm

If you were using an enterprise database like MS SQL or MySQL you could use cfquery's result attribute. But IIRC, it is not supported for MS Access. One of the many reasons to upgrade..

Answer 2

Definitely go with the mySQL conversion. This Access to mySQL converter may help. Depending on your SQL may less painful than you imagine.