''IDENTIFY

  • Thread starter Thread starter Kate
  • Start date Start date
K

Kate

I've just inserted a new record in a table with a primary
key defined as Autonumber. I then want to do something
like:

rsNewID.Open "SELECT @@IDENTITY", CurrentProject.Connection
varNewID = rsNewID(0)

to get the primary key: I can't get it to work. Any
suggestions? I'm using ACCESS 2002 so is @@IDENTIFY a
valid parameter? (for some reason my help is not working
so I can't look it up!)

Any alternatives? 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 engines
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).

It is just not clear what data engine you are using..as mentiend..there are
two
on the office cd..
 
How do I tell which data engine I'm using?

In your example is tblmain the name of my table?

Between the addnew and the update statements can I just
assign the values as I would in an insert?, i.e.
TEACHER.name=form!![teachers name]

and finally what does the bookmark statement do?

Thanks in advance.
 
Kate said:
How do I tell which data engine I'm using?
Click to expand...

If you don't know..then it don't matter!

Likely..if you are not connecting to sql server....then you are using JET as
a file share (the default). Since you started using the identity..then I had
to ask, or at least find out if you are using sql-server (which people who
use sql server know of, and use @identify. So, I kind thought you might be
using sql server....it would appear that you are not).
In your example is tblmain the name of my table?
Click to expand...

Yes...that was just a code example for you to modify as you please.
Between the addnew and the update statements can I just
assign the values as I would in an insert?, i.e.
TEACHER.name=form!![teachers name][/QUOTE]

Actually, you can do the above..but to modify the it goes like:

rstRecords![Teacher name] = form![email]![teachers name]
rstRecords![LastName] = "Kallal"
[QUOTE]
and finally what does the bookmark statement do?[/QUOTE]

The bookmark is a throw away reference that ms-access issues. It is great
way to go to one record bookmark the record..and then return to it later in
code. So, the "lastmodifed" returns a bookmark that points to the last
record added.
Click to expand...
 
The following works in A2K2 using Access/JET Table:

Table1 has 3 Fields:

ID AutoNumber PK
Field1 Long
Field2 Text(50)

****Code tested in A2K2***
Public Sub TestAAIdentity()

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String

Set cnn = CurrentProject.Connection
strSQL = "INSERT INTO Table1 (Field1, Field2) " & _
" VALUES ( 100, 'ABCD')"
cnn.Execute strSQL

strSQL = "SELECT @@IDENTITY FROM Table1"
Set rst = New ADODB.Recordset
rst.Open strSQL, cnn
Debug.Print rst.Fields(0).Value
End Sub
******

When I run the code, a new Record is added into the Table and the ID (PK) of
the new Record is printed in the Debug window.
 
Back
Top