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

you should put the entire processing into a stored procedure, that's the best and most efficient way.

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.  

>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...

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.  

Please post your code.

And the driver you are using: ODBC or OLEDB for SQL Server?

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!
        objCN.BeginTrans

        ' 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
        objCN.commitTrans

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)
   
   
    objRS.Close
    Set objRS = Nothing
   
    ' Close the recordset and the database.

    'dbs.Close
    'objCN.Close
    'Set objCN = Nothing

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




Thanks for helping!

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
objRS.Close
Set objRS = Nothing
   
End Function

Unfortunate that produces the following error:

SET NOCOUNT ON; INSERT INTO dbo_app_complex_sql_groups (
sqlgroupname, sqlgroupdesc
) VALUES
(
'NPfIT DB build status *COPY FROM 637*', '*COPY FROM 637* '
); Select Scope_Identity() NewID
-2147217900
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?

I will try to use a straightforward ADODB connection (dsn free I think they call it) and get back to you.

Using a pure ADODB connection works!

e.g. changing

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

to

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

Thanks for the tips.