Is it possible to combine both the ADODB.Command.Execute to return the @@IDENTITY value

My first Q here so, please forgive me. Im developing in VB6, using MS Access 2000. I use stored procedures to insert / update records but I need to use the Command.Execute for some in order to solve problems with saving data to memo fields, but I would like to return the @@IDENTITY value after inserting

    '*** create an object
    Set oCmd = CreateObject("ADODB.Command")
    '*** assigning the command object parameters
    With oCmd
        '*** Stored Procedure Name (Param 1)
        .CommandText = colParams.Item(1)
        .CommandType = adCmdStoredProc
        .ActiveConnection = moConn.ConnectionString
        For iCount = 0 To .Parameters.Count - 1
            If Right(colParams(iCount + 2), 3) = "#@#" Then
                '*** This is for memo fields
                .Parameters.Item(iCount).SIZE = 8000
                .Parameters.Item(iCount).Type = adLongVarWChar
                .Parameters.Item(iCount).Value = Left(colParams(iCount + 2), Len(colParams(iCount + 2)) - 3)
                .Parameters.Item(iCount).Value = colParams(iCount + 2)
            End If
        '*** Store the result in a recordset
        Set oRecord = .Execute(lRecords)
    End With
   '*** Get Identity (doesnt work)
   lLastIdent =  oRecord(0)

What am I missing

I also use the RecordSet.Execute and have no problems, but I need to be able to save large Memo data to certain tables and using Command.Execute is the only way I can do it.

If someone can help with either @@Identity working with Command.Execute or saving large memo fields....please be my guest. I appreciate it. Thanks in advance!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Are you using Access or SQL Server? You say Access, but @@IDENTITY is a SQL Server function. You refer to stored procedures, but these aren't features in Access, which uses queries instead. Could you clarify, and also please post the code of the procedure you are using? Thanks.
You can use SQL server as A backend for Access (as well as Jet).  @@identity has been in access since Access 2000.

If you are using SQL Server backend (as I guess you are), you could do this with your SPs:

CREATE PROCEDURE <your proc name>
(@id int OUT, ...
SELECT @id = @@identity


Then set you first command parameter to be type adinteger and read it after the .Execute

Read this :
OK - not used Access for a while!
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ellisrichardsonAuthor Commented:
I am using MS Access 2000 and Visual Basic 6...
Are you using Jet or SQL/Server backend?
ellisrichardsonAuthor Commented:
Am using Jet and the above code to call the Command.Execute..
Why don't you have your store procedure return the @@Identity.  Just at the end of the procedure add

SELECT @@Identity

BTW, I would not even bother with a Command object, just use a Recordset and concatanate your SQL string to Execute the store procedure.

ellisrichardsonAuthor Commented:
You cannot include SELECT @@IDENTITY after an INSERT statement in an MS Access sotred procedure. MS Access will only allow you to do one thing!

At this point none of these suggestions seem to be working. I may have to look at it from another way

Microsoft Access 2000 or later does support the @@IDENTITY property to retrieve the value of an Autonumber field after an INSERT. Using the RowUpdated event, you can determine if an INSERT has occurred, retrieve the latest @@IDENTITY value, and place that in the identity column of the local table in the DataSet.

Take a look at the samples at the site.  They are for .NET, but should be adaptable to VB6.



Yeap same site.
ellisrichardsonAuthor Commented:
Thanks guys
either way the MSDN site relates to .NET and I am not using .NET, but i am using MSAccess 2000 and VB6. Am i doing something fundamentally wrong?
Did you look at the EE link?
ellisrichardsonAuthor Commented:
Read the link, but although that is for Access 2002, I tried it in another DB and it doesnt work. Hasnt that been written as an SQL Server Stored Proc, not an MS Access 2002 Stored Proc or am i missing something
This is a snippet that I've used lots of times with a access db backend.. you will have to manuplate it for your own purposes.

Public Function ExecuteActionQueryReturnID(ByVal sSQL As String) As Long
    'Note: Errors are not trapped here. They should be trapped and
    'handled appropriately in the calling routine
    Dim con As ADODB.Connection
    Dim lRecordCount As Long
    Dim rs As New ADODB.Recordset

    On Error GoTo Handle_Error
    Set con = GetConnection() 'Fetch a connection from the pool
    con.Execute sSQL, lRecordCount
    'you can do this type of thing only on the same connection
    sSQL = "SELECT @@identity"
    rs.Open sSQL, con, adOpenForwardOnly, adLockReadOnly

    ExecuteActionQueryReturnID = rs.fields(0).Value 'grab the identity of added record
    ReturnConnection con 'Return the connection to the pool
    Exit Function

    LogMessage "ExecuteActionQuery('" & sSQL & "')"
End Function



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ellisrichardsonAuthor Commented:
Thanks to everyone that helped, In the end I rewrote half of my app in about 20 hours to get this solution to work the right way.

I awarded this to coopzz, even though he told me what i already knew.... he made me realise that i had no other alternative than to re-do my code. He was the closest to what i asked for (Access 2000 / Vb6)

Thanks to all.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.