@@'IDENTITY Object problem - Help!

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

I'm trying to retrieve the autonumber field after an
insert and have tried the following different versions

Set objRecordset = objConnection.Execute("SELECT
@@IDENTITY")
Set rsNewID = objConnection.Execute("SELECT @@IDENTITY")
Set rsNewID = db.OpenRecordset("SELECT @@Identify")
Set rsNewID = db.OpenRecordset("GetBookingID")
Set rsNewID = commInsert.Execute("SELECT @@IDENTITY")

but get either Object Variable or with block variable not
set or Object Required.

What do I need to do? Thanks
 
the JET file share engine that comes with ms-access does not have an
identity feature.

It is NOT clear which data engine you are using? (there is two that ships on
the office cd..and one data engine is 100% compatible with sql server..and
does have identity..and also does support stored procedures).

So...it is not clear which data engine you are using. If you are using the
file share JET engine...then you have to do a insert via code..and THEN
retive the id.

So, you can use the following:

Dim rstRecords As DAO.Recordset
Dim lngNext As Long

Set rstRecords = CurrentDb.OpenRecordset("tblmain")
rstRecords.AddNew

' code can go here to add data, or set values to the reocord...or, you
could just use this code to "grab"
' the id of the reocrd added..and then use a update query (not a
instert query) to update your values.

rstRecords.Update
rstRecords.Bookmark = rstRecords.LastModified
lngNext = rstRecords!ID
rstRecords.Close
Set rstRecords = Nothing
MsgBox lngNext

So, you could even wrap the above in some public function like
"GetMyIdenity" and that would always give your the id of a newly added
record.

Note that if you are using JET..then right after the following command..you
can use/grab the id:

rstRecords.AddNew

However, if using odbc to sql server (linked table).then the record must be
written to disk first (so, you can not get the id right after addnew in that
case). However, the LastModifed code example will work for both jet, and sql
server (so, as a habit..you might as well adopted a coding standards that
works for both data engines).
 
See my code posted in the thread "IDENTITY started by Kate about 2 hours
after your thread (in GettingStarted or Queries).
 
And, I might add the reason why I spend time in these newsgroup to learn
things.

So, ignore my post about the fact that JET does not support identity....I
guess since jet 4.0...it does.....

Good post Van....and I think that using identity is quite neat..since you
then get compatible code between JET and server based code in case of
migration down the road....very nice...
 
Back
Top