Primary Key value from record added programmatically

[paul.schrum]

I am inserting a new record into a table and I want to get the primary
key value from the newly created record.

The table design is:

Field Name Type
unique_person_id Autonumber
id_rec Integer


The code will look something like this:

appendStatement = "INSERT INTO myTable (id_rec) VALUES " &
id_rec_var

DoCmd.RunSQL appendStatement

It is at this point that I want to know the value of unique_person_id
for the record that I just added. Can someone help me do this?

- Paul

 

[Albert D.Kallal]

You can use:

dim lngLastID as long

appendStatement = "INSERT INTO myTable (id_rec) VALUES " &
id_rec_var

DoCmd.RunSQL appendStatement

lngLastId = CurrentDb.OpenRecordset("select @@Identity from myTable")(0)

Note that the above is shorthand...and often would be written as

dim strSql as string
dim rstRec as DAO.ReocrdSet

strSql = "select @@Identity from myTable"
set rstRec = currentdb.OpenRecordSet(strSql)
lngLastId = rstRec(0)
rstRec.Close
set rstRec = nothing

In fact, since you are going to use a recocrdset, you might just use the
recordset to do the insert of data also...

set rstRec = currentdb.OpenRecordSet("myTable")

rstRec.Addnew
rstRec!LastName = "Kallal"
rstRec!DateAdded = date()

lngLastId = rstRec!ID <--- grab last id...
rstRec.Update
rstRec.Close
set rstRec = nothing

Note that if you use sql server, then the lngLastID will NOT be generated
until *after* you do the rstRec.Update. So, for good coding practices, and
to write code that works with sql server, you might use:

rstRec.Addnew
rstRec!LastName = "Kallal"
rstRec!DateAdded = date()
rstRec.Update

rstRec.Bookmark = rstRec.LastModified
lngLastId = rstRec!ID <--- grab last id...

rstRec.Close
set rstRec = nothing

Since you DO need to create a reocrdset, then might as well use it to both
"add" the data, and grab the last id...

However, either approach will work quite fine...

 

[paul.schrum]

Albert,

Thank you very much for that very thorough response. I find that for
my situation I am prefering the first solution:

lngLastId = CurrentDb.OpenRecordset("select @@Identity from myTable")(0)

But I have another question. That text "@@identity", is that literal
text or should I replace it with something depending on my context?

You see, I tried it just as you entered it (replacing "myTable" as
appropriate), but the value that it returned was '0' when it ought to
have been '431'.

- Paul

 

[Albert D.Kallal]

Thank you very much for that very thorough response. I find that for

my situation I am prefering the first solution:


But I have another question. That text "@@identity", is that literal
text or should I replace it with something depending on my context?

You got it right...just use @@Identity

Most database enignes, such as Sql server supprot the @@idenity feature

That idenitity is thus going to be a autnumber field, and thus also your
primary key.

You see, I tried it just as you entered it (replacing "myTable" as
appropriate), but the value that it returned was '0' when it ought to
have been '431'.

You don't mention what version of access, but that above exmaple will only
work with JET 4, and that means access 2000 and later....

So, if you are using access 97, then you are using jet 3x, and it dont'
work. These extsionsn are actually there so you can code to sql server
standards, and people comming from sql server take this @@idenitty for
granted.

Try the long hand version as follows, and see if that works

eg:

dim strSql as string
dim rstRec as DAO.ReocrdSet

strSql = "select @@Identity from myTable"
set rstRec = currentdb.OpenRecordSet(strSql)
lngLastId = rstRec(0)
rstRec.Close
set rstRec = nothing

does the above compile ok? *before* you try and run it?

If you are using pre a2000 version, then I don't belive there is a relbilaty
way of using sql to get the the last autonumber from the database (you could
use the dmax functionk but in a mulit-user environetn, that will not work!).
That means yo have to use:

set rstRec = currentdb.OpenRecordSet("myTable")

rstRec.Addnew
rstRec!LastName = "Kallal"
rstRec!DateAdded = date()

lngLastId = rstRec!ID <--- grab last id...
rstRec.Update
rstRec.Close
set rstRec = nothing

And, we should write code that at least would work with sql server in case
you do ever switch the back end from JET, to a sql engine, then we use

rstRec.Addnew
rstRec!LastName = "Kallal"
rstRec!DateAdded = date()
rstRec.Update

rstRec.Bookmark = rstRec.LastModified
lngLastId = rstRec!ID <--- grab last id...

rstRec.Close
set rstRec = nothing

 

[paul.schrum]

Thanks again Albert,

That did not fix it. I still get back a value of 0.

FYI, I am using ADO.

Here is the code I used:

appendStatement = "INSERT INTO tbl_unique_person (id_rec) " & _
"VALUES (" & carsID & ")"
DoCmd.RunSQL appendStatement
strSql = "select @@Identity from tbl_unique_person"
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open Source:=strSql
unique_id = rst(0)
rst.Close
Set rstRec = Nothing

- Paul

 

[paul.schrum]

More on this.

When I run this, just before

unique_id = rst(0)

rst.RecordCount is equal to -1.

- Paul

 

[paul.schrum]

In the Thread "Problems when referencing a closed form" PC Datasheet
said:

BTW, did you get your primary key problem fixed?

The only time you get this:
rst.RecordCount is equal to -1
is when you first open a recordset and immediately execute this statement.
If you want the true recordcount you need to precede this statement with:
Rst.Movelast

Another comment --- I don't know what you are doing but most likely you
should be using DAO.

Let me know if you need help. (No charge!)

Steve,

Thanks for your help. I may use this info later if I go back to it to
make it smoother.

In the mean time I worked around it like so:

appendStatement = "INSERT INTO tbl_unique_person (id_rec) " & _
"VALUES (" & carsID & ")"

DoCmd.RunSQL appendStatement

If Not IsNull(DLookup("[unique_id]", "tbl_unique_person", _
"[id_rec] = " & carsID)) Then
unique_id = DLookup("[unique_id]", "tbl_unique_person", _
"[id_rec] = " & carsID)
End If

Its ugly, but it got the job done. I can only work on this about 10
hours per week, so I don't really have time to go back and fix it to
the way you recommend. At least, I don't have the time right now. But
thanks a bunch for the recommendation. I will probably use it one day,
probably soon.

- Paul

 

[PC Datasheet]

Paul,

Here's another way you could have done it ----

If DCount("*", "tbl_unique_person", _
"[id_rec] = " & carsID)) Then
unique_id = DLookup("[unique_id]", "tbl_unique_person", _
"[id_rec] = " & carsID)
End If

Steve

In the Thread "Problems when referencing a closed form" PC Datasheet
said:

BTW, did you get your primary key problem fixed?

The only time you get this:
rst.RecordCount is equal to -1
is when you first open a recordset and immediately execute this
statement.
If you want the true recordcount you need to precede this statement with:
Rst.Movelast

Another comment --- I don't know what you are doing but most likely you
should be using DAO.

Let me know if you need help. (No charge!)

Steve,

Thanks for your help. I may use this info later if I go back to it to
make it smoother.

In the mean time I worked around it like so:

appendStatement = "INSERT INTO tbl_unique_person (id_rec) " & _
"VALUES (" & carsID & ")"

DoCmd.RunSQL appendStatement

If Not IsNull(DLookup("[unique_id]", "tbl_unique_person", _
"[id_rec] = " & carsID)) Then
unique_id = DLookup("[unique_id]", "tbl_unique_person", _
"[id_rec] = " & carsID)
End If

Its ugly, but it got the job done. I can only work on this about 10
hours per week, so I don't really have time to go back and fix it to
the way you recommend. At least, I don't have the time right now. But
thanks a bunch for the recommendation. I will probably use it one day,
probably soon.

- Paul

 

[StopThisAdvertising]

Paul,

Here's another way you could have done it ----

If DCount("*", "tbl_unique_person", _
"[id_rec] = " & carsID)) Then
unique_id = DLookup("[unique_id]", "tbl_unique_person", _
"[id_rec] = " & carsID)
End If

Steve

No sig ?? Now you are a good boy Steve !!
But I guess a lot of us will have no problem when you would use your old sig.

Arno R

 

[RoyVidar]

(e-mail address removed) wrote in message

Thanks again Albert,

That did not fix it. I still get back a value of 0.

FYI, I am using ADO.

Here is the code I used:

appendStatement = "INSERT INTO tbl_unique_person (id_rec) " & _
"VALUES (" & carsID & ")"
DoCmd.RunSQL appendStatement
strSql = "select @@Identity from tbl_unique_person"
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open Source:=strSql
unique_id = rst(0)
rst.Close
Set rstRec = Nothing

- Paul

Hi

Probably not the best of explanations, but you are, well, kind of
not using ADO all the way ... you're using the RunSql thingie of
the DoCmd object, which (not that I know exactly what it's using,
I'm assuming something DAOish, currentdb/dbengine...) isn't
ADO - now if you use ADO through this, you should be fine, how?

dim cn as adodb.connection
set cn = currentproject.connection
appendStatement = "INSERT INTO tbl_unique_person (id_rec) " & _
"VALUES (" & carsID & ")"
cn.execute appendStatement,,adcmdtext
strSql = "select @@Identity from tbl_unique_person"
set rst = cn.execute(strSql,,adcmdtext)
unique_id = rst.fields(0).value
rst.Close
Set rstRec = Nothing
Set cn = nothing

Try this, and see if it works out.

 

[RoyVidar]

This is a bit embarrasing, I didn't see it for some reason, strip of
the from clause of the @@identity statement. I e use only

"select @@identity"

 

[Albert D.Kallal]

Your first problem is that you go to all he trouble to setup a connection,
and a ado record, then you turn around and dump all that work, and use the
docmd.RunSql. The run sql is using what connection? (you don't really
know...do you?). What object model is docmd.runsql? (again, you don't
know).

You don't want to mix you object models, and in fact if you mix ado/dao in
your code this way, then the @@identity don't work...

However, your code is failing due to the use of docmd.runsql...(it does NOT
work for DAO, or ADO code..).

Further, since runSql always prompts you to confirm updates, then again you
really don't want to use it.

I never use the "runsql" from the docommand object in code anyway, I guess I
should have mentioned this issue.

So, you need to use DAO, or ADO, and use:

For DAO you can use:

appendStatement = "INSERT INTO tbl_unique_person (id_rec) " & _
"VALUES (" & carsID & ")"

currentdb.Execute appendStatement

lngLastId = CurrentDb.OpenRecordset("select @@Identity from
tbl_uniquire-person")(0)


or, for ADO, you can use:

appendStatement = "INSERT INTO tbl_unique_person (id_rec) " & _
"VALUES (" & carsID & ")"

CurrentProject.Connection.Execute strSql

Set rstRec = New ADODB.Recordset
rstRec.Open ("select @@Identity from tbl_unique_person"),
CurrentProject.Connection
lngLastId = rstRec(0)
rstRec.Close
Set rstRec = Nothing


So, the other lesson of this story is not to mix the object models. If you
insert using ADO, and then try and grab the last id using DAO it don't work.
You are using a different connection + object model, and that simply don't
work....

Note that NEITHER of the above works with if you use the runsql....

So, use currentdb for DAO

and

CurrentProject.Connection for ADO....

 

[Albert D.Kallal]

This is a bit embarrasing, I didn't see it for some reason, strip of
the from clause of the @@identity statement. I e use only

"select @@identity"

Hum, you don't need the table as you point out, but I would keep for
compatibility reasons. If you are inserting into two tables...then which key
is will you get back???

Anyway, you can see my other post, but the problem you identified (correctly
I might add!) was use of the docmd.Runsql.

 

[RoyVidar]

Albert D.Kallal wrote in message

Hum, you don't need the table as you point out, but I would keep for
compatibility reasons. If you are inserting into two tables...then which key
is will you get back???

I thought it would do the same as when executed against SQL server,
return the last
inserted identity (autonumber) on the connection. Which autonumber(s)
would the below
return?

dim cn as adodb.connection
set cn = currentproject.connection
cn.execute "insert into table1 (test) values
('mytest')",,adcmdtext+adexecutenorecords
cn.execute "insert into table2 (blah) values
('myblah')",,adcmdtext+adexecutenorecords
debug.print "table1 ?", cn.execute("select @@identity from
table1",,adcmdtext).fields(0).value
debug.print "table2 ?", cn.execute("select @@identity from
table2",,adcmdtext).fields(0).value
debug.print "no table?", cn.execute("select
@@identity",,adcmdtext).fields(0).value

 

[Albert D.Kallal]

dim cn as adodb.connection

set cn = currentproject.connection
cn.execute "insert into table1 (test) values
('mytest')",,adcmdtext+adexecutenorecords
cn.execute "insert into table2 (blah) values
('myblah')",,adcmdtext+adexecutenorecords
debug.print "table1 ?", cn.execute("select @@identity from
table1",,adcmdtext).fields(0).value
debug.print "table2 ?", cn.execute("select @@identity from
table2",,adcmdtext).fields(0).value
debug.print "no table?", cn.execute("select
@@identity",,adcmdtext).fields(0).value

I don't know what happens in the above since never tried it without using
the table....

I just have to try it!!