title: VBA ADODB Transactions
body: <p>Have an application developed in Access 2010 connected to MySQL Server via ODBC.</p>
  
  <p>I have 2 tables </p>
  
  <p><code>ContactDetails</code> with columns:</p>
  
  <pre><code>ID, FirstName, LastName, TelNo, MobileNo, EmailAddress, PrimaryContact, TimeStamp
  </code></pre>
  
  <p>and <code>ReportingType</code> with columns:</p>
  
  <pre><code>ID, ReportType, ContactID, TimeStamp
  </code></pre>
  
  <p>I'm using a ADO transaction but when inserting into <code>ContactDetails</code>, I need to retrieve the ID so I can insert a corresponding record into <code>ReportingType</code> and set <code>ReportingType.ContactID</code> to be <code>ContactDetails.ID</code>.</p>
  
  <p>In VB.Net I know I can use "Select LAST_INSERT_ID()" at the end of the SQL statement and <code>ExecuteScalar</code> will return the auto incremented <code>ID</code>.</p>
  
  <p>Below is my code</p>
  
  <pre><code>Dim conn As ADODB.Connection
  
  On Error GoTo ErrorHandler
  Set conn = CurrentProject.Connection
  
  With conn
  
      .BeginTrans
  
       'insert a new customer record
      .Execute "INSERT INTO ContactDetails (" &amp; _
               "FirstName, " &amp; _
               "LastName , " &amp; _
               "TelNo , " &amp; _
               "MobileNo ," &amp; _
               "EmailAddress ," &amp; _
               "IsPrimaryContact) " &amp; _
               "Values ( " &amp; _
               "'" &amp; Me.FirstName &amp; "'," &amp; _
               "'" &amp; Me.LastName &amp; "'," &amp; _
               "'" &amp; Me.TeleNum &amp; "'," &amp; _
               "'" &amp; Me.MobileNum &amp; "'," &amp; _
               "'" &amp; Me.EmailAddress &amp; "'," &amp; _
               False &amp; ");", , adCmdText + adExecuteNoRecords
  
              'Added from a possible solution
              Dim rs As New ADODB.Recordset
              Set rs = conn.Execute("SELECT @@Identity", , adCmdText)
              Debug.Print rs.Fields(0).Value  ' This returned 0
  
          'Inset a new record into the ReportingType Table
          For i = 1 To ListView1.ListItems.Count
              If ListView1.ListItems(i).Checked Then
                   .Execute "INSERT INTO ReportingType " &amp; _
                            "(ReportType,  ContactID) " &amp; _
                            "VALUES " &amp; _
                            "('" &amp; colReportType(ListView1.ListItems(i)) &amp; "' , " &amp; ContactID &amp; ")"
              End If
  
          Next i
  
      .CommitTrans
  End With
  ExitHere:
      Set conn = Nothing
      Exit Sub
  ErrorHandler:
      If Err.Number = -2147467259 Then
          MsgBox Err.Description
          Resume ExitHere
      Else
          MsgBox Err.Description
          With conn
              .RollbackTrans
              '.Close
          End With
          Resume ExitHere
      End If
  End Sub
  </code></pre>
  
  <p>Please can you help me with this?</p>