Drawing the unique key simultaneously

[Nicodemas]

Hello folks,

I'm trying to create a suspense system of my own.
I'm using Microsoft Access as a test, and planning to move it to Oracle, but bear with me on the MS Access issue at hand.

However, I've run into a situation that I have ran into in the past, and always wondered if it could be done!

I wonder if it is possible while inserting a record into a database, I could draw (using a SELECT or whatever) the unique id which MS Access automatically creates in the Autonumber field.

Does anyone know if this is possible?

So far I have searched Google with some search phrases, but no results that I can apply to my issue.

I do not think it is possible to do both simultaneously, but I've been wrong in the past.

Thanks for any and all help.

[lcscne]

Cant think of why:

SELECT Max([autRecordID])+1 AS noNextAutoNumber
FROM tblTableName;

(before update)

SELECT Max([autRecordID]) AS noNextAutoNumber
FROM tblTableName;

(after update)

wouldn't work.

[CardboardHammer]

I don't think there's transaction support in Access, so if you've got concurrent connections, you're going to have some extra work to od to keep users from stepping on eachother's toes.

There is transaction support in Oracle, as well as the equivalent of stored procedures (I don't remember what Oracle calls them). So you do the insert and query the max autonum within a stored procedure using an appropriate transaction level, and you're in business.

[Nicodemas]

I found a very interesting solution! For those interested on a method to do this, read below!

Normally I have a function that sets-up/breaks-down all my connections, and recordsets so all I have to do is invoke it with a database location, SQL statement, and recordset name as parameters, but this time I had to go the long route.




<!-- #include file="../_include/constants.icl" --><%

'//------------------------------------------------------------------------------------------
'//
'// NAME: Robert D. Simpson
'// DATE: 11 DECEMBER 2003
'//
'// This program:
'// - inserts a new record into the suspense database (tblSuspenses), and
'// - stores the unique ID of the suspense, and
'// - redirects a user to the details page.
'//
'//-------------------------------------------------------------------------------------------

const adOpenKeyset = 1
const adLockOptimistic = 3
const adCmdTable = 2

'// Connect to the database

Set objConn = Server.CreateObject("ADODB.Connection")
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDB & ";Persist Security Info=True"
objConn.Open strConnect

'// Create a new recordset

Set rs = Server.CreateObject("ADODB.Recordset")

'// Open the table

rs.Open "tblSuspenses", objConn, adOpenKeySet, adLockOptimistic, adCmdTable

'// Add the new record and update the table (which creates the autonumber)


rs.AddNew
rs("StartDate") = Date()
rs.Update

intSuspenseID = rs("Suspense_ID")

'// Close recordset and connection

rs.Close()
Set rs = Nothing

objConn.Close()
Set objConn = Nothing

'// Redirect to the details page

Response.Redirect("../suspense_details.asp?ID="& intSuspenseID &"")
%>