• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 655
  • Last Modified:

Retrieving @@IDENTITY value of last insert inside a transaction - Locking -- ADODB, VBA, Access 2002 - SQL Server


I am trying to retrieve the last IDENTITY (aut-increment) value inserted into a SQL Server 2000 database.  
The INSERT command is run from MS Access VBA code, using ADODB (MDAC 2.7 I think).

I can use a simple "Select @@IDENTITY from table_name" if I don't use transaction processing on the database connection, however I need use transaction processing because several inserts are tried and if any of them fail then the whole thing needs rolling back.

When using transaction processing, calling "Select @@IDENTITY" times out - understandable since the record is lock on the DB until the "CommitTrans" method is called for the connection.

Does anyone have any ideas how to achieve this?

(P.S. dues to this being an upgrade from a legacy system, the INSERT command is run by assembling a string of text and running this against the connection, not from using the ADO "AddNew" method).

Thanks in advance!
  • 6
  • 4
  • 2
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you should put the entire processing into a stored procedure, that's the best and most efficient way.
colly92002Author Commented:
Sorry a stored procedure is not practical in this case since the SQL used is dyanamic and depends on user interaction.  Basically it would mean re-writing the whole thing from scratch in a massive stored procedure with next to no error reporting if something goes wrong.  
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Sorry a stored procedure is not practical in this case since the SQL used is dyanamic and depends on user interaction.

stored procedures can accept parameters without any problem...

>with next to no error reporting if something goes wrong.  
not really true...

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

colly92002Author Commented:
Yes I appreciate that SP can accept parameters, and that they can assemble dynamic SQL.  However writing a stored procedure means re-writing the whole thing from scratch in a SP and completely re-engineering the way the Access fornt end works.   This is just a little bit of software that needs to ask the user questions between each stage, meaning I would have to assemble the answers and then pass over DB processing to the stored procedure with all the answers.  

This is too big a job for adding a little bit of added functionality!

All I want to know if is if I can add transaction processing and still retrieve the last IDENTITY value inserted, something I have never tried using ADODB.  
Anthony PerkinsCommented:
Please post your code.
Anthony PerkinsCommented:
And the driver you are using: ODBC or OLEDB for SQL Server?
colly92002Author Commented:
Here are snippets of the code  (will post full code if required):

Public objCN As ADODB.Connection

Private Sub btnCopy_Click()    
       Set objCN = Application.CurrentProject.Connection  ' Not sure about this!
        objCN.CursorLocation = adUseClient
        ' Cant use because need the @@IDENTITY value - damn!

        ' Run the SQL, and remember what ID was instered into the database
        sSQL = "insert ...."
        sTargetTab = "table_name"
        iRetId = fnExecInsertSQL(sSQL, sTargetTab)    ' <--- this bit does the insert and tries to get the @@IDENTITY
        ' If something goes wrog then rollbacktrans

End Sub

Function fnExecInsertSQL(sInsertSQL As String, STabName As String) As Integer
    'Dim dbs As Database
    Dim objRS As New ADODB.Recordset
    Dim sSQL As String
    Dim iID As Integer
    Dim sErrSQL As String
Dim Cmd As New ADODB.Command
    On Error GoTo fnExecInsertSQL_ErrorHandler
    objRS.CursorLocation = adUseClient
    objRS.CursorType = adOpenDynamic ' adopenKEYSET
    objRS.LockType = adLockBatchOptimistic

    'Set dbs = CurrentDb()
    'sInsertSQL = sInsertSQL & ";" & vbCrLf & " SELECT @@Identity from " & STabName & ";"
    Debug.Print "Running Insert SQL: " & sInsertSQL
    sErrSQL = sInsertSQL
    'dbs.Execute (sInsertSQL)
    'Cmd.commandtext = sInsertSQL
    'Cmd.ActiveConnection = objCN
    'Set objRS = Cmd.Execute
    objCN.Execute (sInsertSQL)

    sSQL = "SELECT @@Identity from " & STabName
    sErrSQL = sSQL
    Set objRS = objCN.Execute(sSQL)  <--- This times out because the record is locked on the DB
    iID = objRS.Fields(0)
    Set objRS = Nothing
    ' Close the recordset and the database.

    'Set objCN = Nothing

    fnExecInsertSQL = iID
    Exit Function
    MsgBox ("ERROR executing SQL: " & vbCrLf & sErrSQL & vbCrLf & _
            Err.Number & vbCrLf & Err.description & vbCrLf)
    fnExecInsertSQL = Err.Number
End Function

Thanks for helping!
Anthony PerkinsCommented:
Depending on your provider (OLEDB or ODBC) the following should work:

Function fnExecInsertSQL(sInsertSQL As String, STabName As String) As Integer
Dim objRS As ADODB.Recordset
Dim sSQL As String

' sSQL = "SET NOCOUNT ON; " & sInsertSQL & "; Select @@IDENTITY NewID"      ' Use this if using SQL Server 7
sSQL = "SET NOCOUNT ON; " & sInsertSQL & "; Select Scope_Identity() NewID"      ' Use this if using SQL Server 2000 or greater
Set objRS = objCN.Execute(sSQL)

fnExecInsertSQL = objRS.Fields("NewID").Value
Set objRS = Nothing
End Function
colly92002Author Commented:
Unfortunate that produces the following error:

SET NOCOUNT ON; INSERT INTO dbo_app_complex_sql_groups (
sqlgroupname, sqlgroupdesc
'NPfIT DB build status *COPY FROM 637*', '*COPY FROM 637* '
); Select Scope_Identity() NewID
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.


I am trying to use ADODB, but using the following connection:
Set objCN = Application.CurrentProject.Connection

Since my tables are ODBC linked tables, could this be causing the problem?
Anthony PerkinsCommented:
It is possible, as I believe you are going through JET when using linked tables and it may be throwing up on the T-SQL code.
colly92002Author Commented:
I will try to use a straightforward ADODB connection (dsn free I think they call it) and get back to you.
colly92002Author Commented:
Using a pure ADODB connection works!

e.g. changing

Set objCN = Application.CurrentProject.Connection  '(uses JET)


objCN.Open "Provider=SQLOLEDB.1;Server=my_server;database=my_db;uid=online_reports;password=my_password" '(ADODB)

Thanks for the tips.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now