Error using @@Identity with Access 1

[Udaman33]

I am trying to return the record ID from an access database using the autonumber data field.

Connection string:

Set conn = Server.CreateObject("ADODB.Connection&quot
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;_
Data Source=C:\Inetpub\

http://wwwroot\Approvals\Approval_

Codes.mdb;Persist Security Info=False;"

After the INSERT INTO statement executes, I have inserted the code:

set rsID = Conn.Execute("SELECT @@IDENTITY&quot
varRecordID = rsID(0)
rsNewID.Close
Set rsNewID = Nothing

When I rem these lines, it works just fine, but with them enabled I get the error message:

Error Type:
Response object, ASP 0185 (0x8002000E)
A default property was not found for the object.

Everywhere I have read says this should work. Any ideas?

 

[Veep]

@@Identity doesn't fly with MSAccess. SQL Server.

 

[Udaman33]

Is there any way to do this with MSAccess?

Would it be better to assign the record ID on the ASP side?

 

[Veep]

I gave you the basics of what you need to do in the ASP.NET forum. You have two choices: Recordset or SELECT MAX after the insert.

 

[Udaman33]

I tried the max approach:

set rsNewID = Conn.Execute("SELECT Max(approvals.appid)_
FROM approvals&quot
varNewID = rsNewID("appid&quot
response.write rsNewID
rsNewID.Close
Set rsNewID = Nothing

and I get the same error message. Am I doing something wrong?

What is the other approach - using recordset?

 

[Veep]

You need to assign an alias:
"SELECT MAX(approvals.appid) as NewID FROM approvals"
Then:
varNewID=rsNewID("NewID&quot

 

[Udaman33]

You rule!!! Using an alias, @@Identity worked, too, which is great, because using MAX I had to assume that the most recent record was the one entered by that user. With Identity, I am assured the correct record. Thanks for all of your help.