I have 2 tables in which I need to store data from my ASP page. The first table (Students) contains the field ID that I need to insert into the 2nd table. I've tried using

"SELECT @@IDENTITY INSERT INTO Students (FirstName, LastName) VALUES ('Chris', 'Cote')"

but I keep getting and error that says:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '@@IDENTITY 'ID' INSERT'.
/IMI/login.asp, line 38

Is there an equivalent call to using the @@IDENTITY statement in Access databases?


Chris

Microsoft Access 2000 or later does support the @@IDENTITY property to retrieve the value of an Autonumber field after an INSERT.

You can't get identity value with the way you have used there.

"SELECT @@IDENTITY INSERT INTO Students (FirstName, LastName) VALUES ('Chris', 'Cote')"

It should be something like this.

"INSERT INTO Students (FirstName, LastName) VALUES ('Chris', 'Cote');
SELECT @@IDENTITY"

Hope that helps.

_________________________
- Vijay G
Strive for Perfection

I just tried using "INSERT INTO Students (FirstName, LastName) VALUES ('Chris', 'Cote'); SELECT @@IDENTITY", but it didn't work. Now I get the following error:

Expected end of statement.

Is it possible that Access doesn't allow the IDENTITY property?

Chris

If you are using 2000 and above, it supports.

_________________________
- Vijay G
Strive for Perfection

I think the problem is that MS Access can't do batch sql statements.

I don't know if this would work in Access but it works in SQL. Excuse me if I'm not understanding what you guys are talking about.

First insert into Students.
(This works in SQL.)

SQL = "Set nocount on;INSERT INTO Students (FirstName, LastName) VALUES ('Chris', 'Cote');Select TheID = @@identity;Set nocount off;"

Set oRS = oConn.Execute(SQL)
TheID = oRS("TheID")

Then use that 'ID' in your next insert statement. I don't know if Access uses the 'nocount' so you can try with and without the nocount statements.

Hope this helps.

Richard

After you insert the data into the first table, you can grab the top id using:

SELECT Max(StudentID) as MaxID FROM Students

... then you can insert MaxID on the next page... if I understand what you're trying to do