MS Access Database Primary Key Update Workaround

'====================================================================================
'     MS Access Database Primary Key Update Workaround (Massi)
'====================================================================================
'   By Joe Bolen

'   Description:    Addresses a problem of Microsoft's Access database table's primary
'                   key during an insert not returning the new AutoNumber identity
'                   generated value to a Visual Studio .NET written programs. This
'                   problem occurs because MS Access does not allow multiple batch
'                   query statements in one command.
'
'   Documentation is at:
'                   App's Visual Basic .NET code is at http://pastebin.com/0NrHzLdV
'                   Video tutorial at YouTube: http://www.youtube.com/user/bolenpresents

'====================================================================================
'     Add AccessIDHelper.vb Class:
'====================================================================================

Imports System.Data.OleDb

Public Class AccessIDHelper

   ''' <summary>
   ''' Retrieves the primary key autonumber values from Access
   ''' </summary>
   ''' <remarks></remarks>
   Public Shared Sub SetPrimaryKey(ByVal trans As OleDbTransaction, _
                                   ByVal e As OleDbRowUpdatedEventArgs)

      If e.Status = UpdateStatus.Continue AndAlso _
         e.StatementType = StatementType.Insert Then
         ' If this is an INSERT operation...
         Dim pk = e.Row.Table.PrimaryKey
         ' and a primary key column exists...
         If pk IsNot Nothing AndAlso pk.Count = 1 Then
            Dim cmdGetIdentity As New OleDbCommand("SELECT @@IDENTITY", trans.Connection, trans)
            ' Execute the post-update query to fetch new @@Identity
            e.Row(pk(0)) = CInt(cmdGetIdentity.ExecuteScalar)
            e.Row.AcceptChanges()
         End If
      End If

   End Sub

End Class

'====================================================================================
'     Add to the strongly typed DataSet's partial class code behind:
'====================================================================================
'    Add Namespace of your DataSet Table Adapters, then for each TableAdapter add a
'    partial class of the TableAdpater with the Sub _adapter_rowUpdated. This
'    procedure will be called immediately after the table has been updated to the data
'    source, but before the connection is closed.
' ------------------------------------------------------------------------------------

Namespace SampleDBDataSetTableAdapters

    Partial Class CustomersTableAdapter

        Private Sub _adapter_RowUpdated(sender As Object, e As OleDb.OleDbRowUpdatedEventArgs) _
            Handles _adapter.RowUpdated

            ' Update Identity / AutoNumber field using AccessIDHelper class.
            AccessIDHelper.SetPrimaryKey(Me.Transaction, e)

        End Sub

    End Class

    Partial Class OrdersTableAdapter

        Private Sub _adapter_RowUpdated(sender As Object, e As OleDb.OleDbRowUpdatedEventArgs) _
            Handles _adapter.RowUpdated

            ' Update Identity / AutoNumber field using AccessIDHelper class.
            AccessIDHelper.SetPrimaryKey(Me.Transaction, e)

        End Sub

    End Class

End Namespace

Partial Class SampleDBDataSet
End Class

'====================================================================================
'   For further information, see ...
'
'   Massi, Beth. "Using TableAdapters to Insert Related Data into an MS Access
'   Database." Sharing the goodness.... May 14, 2009. http://blogs.msdn.com/b/
'   bethmassi/archive/2009/05/14/using-tableadapters-to-insert-related-data-into-
'   an-ms-access-database.aspx (accessed July 13, 2015).
'====================================================================================