Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

@@IDENTITY in Access 2000

0 views
Skip to first unread message

Mick Ruthven

unread,
Dec 10, 2000, 11:14:03 PM12/10/00
to
Access 2000 supposedly uses the Jet 4.0 engine, and the Jet 4.0 engine is
supposed to support @@IDENTITY (per MS Article Q232144).

Has anyone been successful getting @@IDENTITY to work with Access 2000? I've
not been successful.

Thanks,

Mick Ruthven


Nick Pelling

unread,
Dec 11, 2000, 5:40:24 AM12/11/00
to
Mick Ruthven wrote in message <911ka0$mii$1...@misc.macromedia.com>...


No success here either. The ASP workarounds I've had to construct to get
around this are particularly unpleasant. :-(


Rod Pitts

unread,
Dec 11, 2000, 8:44:32 AM12/11/00
to
Works just like in SQL Server but you must use an Access OLEDB Provider
connection. You also need to hand code it because the connection to
retrieve the autonumber must be the same connection used for the Insert.
Below is some sample code. Note it uses the ADO Connection object to do
this, not the Command object which UD uses for its Insert behavior.

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\database.mdb"
Set cnDatabase = Server.CreateObject("ADODB.Connection")
cnDatabase.Open strConn

'Set a variable to your hand coded SQL Insert string
' or to the one created by the MM_Insert behavior
' and remove the MM code that does the actual Insert
strSQL = "Your SQL Insert statement string"
' or strSQL = MM_insertStr

'Insert the record
cnDatabase.Execute strSQL

'retrieve the autonumber for the record just inserted
strSQL = "SELECT @@Identity"
Set rsNewAutoIncrement = Server.CreateObject("ADODB.Recordset")
rsNewAutoIncrement.Open strSQL, cnDatabase
varAutoIDValue = rsNewAutoIncrement(0).Value

'clean things up
rsNewAutoIncrement.Close
Set rsNewAutoIncrement = Nothing
cnDatabase.Close
Set cnDatabase = Nothing

--
Rod Pitts
"Mick Ruthven" <m...@impact-consult.com> wrote in message
news:911ka0$mii$1...@misc.macromedia.com...

Mick Ruthven

unread,
Dec 11, 2000, 11:00:25 AM12/11/00
to
Very interesting! I'll try it soon. Are there any disadvantages in using the
ADO Connection object instead of the Command object that UD uses (other than
having to manually replace UD's code).

Mick Ruthven

"Rod Pitts" <keyl...@hotmail.com> wrote in message
news:912lio$b6q$1...@misc.macromedia.com...

Nick Pelling

unread,
Dec 13, 2000, 7:45:52 AM12/13/00
to
Great! Thanks very much!

Cheers, .....Nick Pelling.....


0 new messages