(ADO) Return Primary Key After Adding New Record? 2

[hererxnl]

Hoping someone will give me some quick help here. I'm creating a new record in an Access DB with a VB6 app using the following code:

Set conn = CreateObject("ADODB.Connection")
connString = "PROVIDER=MICROSOFT.JET.OLEDB.4.0; DATA SOURCE=\\Whatever\Generic.mdb; User ID=admin; Password=;"
conn.Open connString

SQL = "Select * From Job"
Set rs = CreateObject("ADODB.Recordset")
rs.LockType = 3
rs.Open SQL, conn

rs.AddNew
rs.Fields("UserID") = fldUID
rs.Fields("Account#") = fldAcct
rs.Fields("Dept#") = fldDept
rs.Fields("VoxSize") = fldVS
rs.Fields("Priority") = fldP
rs.Fields("EndTime") = fldET
rs.Fields("DicTime") = fldDT
rs.Fields("Status") = fldStatus
rs.Update
Set rs = Nothing
Set conn = Nothing

I need to get the Primary key for the newly created record. The Primary key is a field called "Job#" and is a Long Integer, if that helps.

Thanks in advance.

 

[DrJavaJoe]

Do a keyword search for @@IDENTITY.



Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[hererxnl]

I read the MSDN article about @@IDENTITY and I'm confused, but what's new. The article mentions this property being associated with Access 2000 and higher. Unfortunately this is a legacy Access 95 database (sorry, forgot to mention this before). I've used "PROVIDER=MICROSOFT.JET.OLEDB.4.0" in all connections strings to this DB, but it does say this property requires both Jet4 and MSA2000+.

Ideas?

 

[hererxnl]

Should also mention that this is a multi user environment, therefore record counts before and after are useless, or so it seems to me.

 

[dilettante]

Have you tried it, perhaps in a small test case? It may work fine with Jet 4.0 and this old-format database.

There are also approaches like:

{set fields}
RS.Update
lngBookmark = RS.AbsolutePosition
RS.Requery
RS.AbsolutePosition = lngBookmark
lngPK = RS!MyPKField

I admit I have not tried these with Access 95 MDB files wthough.

 

[hererxnl]

Forgive the brain today...

Using the @@Identity apporach should the tail end of my code look like this:

rs.Update
Set rs = Nothing

Set rs = conn.Execute("SELECT @@Identity", , 1)
objJobNumber = rs(0).Value
Set rs = Nothing

When I try this, the value returned is 0, which is obviously not correct.

Thanks

 

[dilettante]

Does .Requery (above) help? It is sort of the long way around, but it may give you what you need.

 

[Scoty]

Hererxnl,
I have had a simular situation. In order to use the @@Indentity you need to be able to use the key work GO. It will not work in Access as Access does not know what GO means. Here is what I did. I added a new column to the table called UniqueTime. In my code I made a variable and assigned it to "Now()" (because it changes so fast). after inserting the record (along with the now variable) I then do a select on "Where UniqueTime = #" & MyNowVariable & "#" to return the record. I am then able to return the Primary Key.

Seems kinda of backwards but that is how I did it for a multi user enviroment

Scoty
:

"Learn from others' mistakes. You could not live long enough to make them all yourself."
-- Hyman George Rickover (1900-86),

 

[hererxnl]

scoty:
That's an interesting approach. Not to get off topic but this db is in constant use and as such there really isn't a time I could lock it up using the Access gui to insert a new field. Given that what would be your approach?

Second, it was my understanding after reading more about the @@Identity property that Jet 4 provider (which is what I've been using) enabled this functionality. I'm wondering if my code is just wrong.

dilettante:
I'm going to try your approach as it requires no db mods.

everyone else:
Can anyone who has used the @@Identity property with Jet 4 provider look at what I listed above and identify something I've overlooked or an erroneous implementation?

As always this forum is very helpful. Thanks all.

 

[DrJavaJoe]

hererxnl, try this replacing values where appropriate:

Dim adoConn As New Connection
adoConn.Open YourConnectionString
Dim adoCommand As New ADODB.Command
Dim adoRS As New ADODB.Recordset
cmdUpdate.CommandText = "Insert into YourTable (SomeField) " & _
"Values ('SomeValue')"
cmdUpdate.ActiveConnection = adoConn
cmdUpdate.Execute
Set adoRS = adoConn.Execute("SELECT @@Identity", , 1)
objJobNumber = adoRS(0).Value
adoRS.Close
adoConn.Close
Set adoConn = Nothing
Set adoRS = Nothing
Set adoCommand = Nothing




Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[hererxnl]

Thanks to everyone for offering assistance. I've not had any luck with the @@Identity approach but thankfully a combination of dilettante's and scoty's suggestion got the brain going again.

I realized that I have several unique values being added at Update time that I could recall in combination to retrieve my Primary key. The first was Now(), and when combined with my second unique variable (File.Size), I'm pretty confident that I won't retun anything except for the correct Primary Key value. I was hoping not to recreate a recordset as this operation is part of a loop and I was trying to optimize as I was coding, but this solution works.

Big Thanks to all of you...
Stars to dilettante and scoty

 

[VBVines]

use
rs.MoveLast after your update
Then have the value in the primary key column of the current record brought back as a variable or display it in
a text box such as this
TxtRes_id = rs!res_id

aspvbwannab