See also

Best practices part 1 of this series and Part 3

Introduction

This code sample/article is a continuation of part 1 where the goal is to provide developers with basic to intermediate to advance methods for working with Microsoft Access databases for desktop solutions utilizing VB.NET programming language with Microsoft Visual Studio 2017. Before working with the information in this code sample start with part 1 which covers all the basics for designing a database, using base classes for connections with or without a database password, exception handling. Basics for reading data, removal of data where in this part adding and editing data will be covered along with other important topics. Part 3 concludes the series. 

Building the solution

Use the following link for updated source code.

Inserting records

Adding new records has two flavors,

  1. add record(s) without the need to return it's key, while
  2. the other is to add record(s) and return the key.

Here, the focus will be on returning the primary key. First write code to add a record using the following model, create a connection and a command object with one parameter for each field to be inserted into the new record. Unlike prior examples in this series, parameters are not known by a question mark but instead named parameters even though MS-Access reads parameters by ordinal position. Using named parameters is easy to read and when there are issues easier to track down issues and finally if the database is changed to SQL-Server which uses named parameters part of your work is done already.

Signature for the add method

Public Function AddNewRow(
 pName As String,
 pContact As String,
 pContactTitle As String,
 ByRef pIdentfier As Integer) As Boolean

Each field is passed in strong typed which assist to prevent errors. An alternate method would be to pass in a DataRow or use a strongly typed class to represent fields to use in the add new record operation.

cmd.ExecuteReader runs the SQL INSERT, once executed set the command query to get the new primary key and set the incoming variable identifier with the key. If all works as expected the caller can use the new key to then be able to reference the record for read, update and delete operations. Refer to the following form (first procedure) to see how this method is called.

Public Function AddNewRow(
 pName As String,
  pContact As String,
  pContactTitle As String,
  ByRef pIdentfier As Integer) As Boolean
 
 Dim success As Boolean = True
 
 Try
  Using cn As New OleDbConnection(ConnectionStringWithPassword)
   Using cmd As New OleDbCommand With {.Connection = cn}
    cmd.CommandText = <SQL>
    INSERT INTO Customer 
     (
      CompanyName,
      ContactName,
      ContactTitle
     ) 
    Values
     (
      @CompanyName,
      @ContactName,
      @ContactTitle
     )
   </SQL>.Value
 
   cmd.Parameters.AddWithValue("@CompanyName", pName)
   cmd.Parameters.AddWithValue("@ContactName", pContact)
   cmd.Parameters.AddWithValue("@ContactTitle", pContactTitle)
 
   cn.Open()
 
   cmd.ExecuteNonQuery()
 
   cmd.CommandText = "Select @@Identity"
   pIdentfier = CInt(cmd.ExecuteScalar)
 
   End Using
  End Using
 
 Catch ex As Exception
  mHasException = True
  mLastException = ex
  success = False
 End Try
 
 Return success
End Function

This alternate method for adding a new record uses a class instance to supply data. The parameter customerList is for inserting multiple (or one) records.

Note that the command does not use a using statement, instead the command is created for each iteration of an insert. Alternately the command may be created once, create the parameters once then populated the parameters on each iteration if not for the second query to obtain the new primary key.

''' <summary>
''' Add many customer records
''' </summary>
''' <param name="customersList">List of new customers to add to the Customer table</param>
''' <returns>True if successful, false on error</returns>
Public Function AddNewCustomerRecords(customersList As List(Of Customer)) As Boolean
 mHasException = False
 Dim affected As Integer = 0
 
 Try
  Using cn As New OleDbConnection With {.ConnectionString = ConnectionString}
   cn.Open()
 
   For Each customer As Customer In customersList
    '
    ' Create a command for adding a new record. This must
    ' be done each iteration as in the code to get the new primary
    ' key the command is altered.
    '
    Dim cmdAdd = AddRecordCommand(cn)
 
    cmdAdd.Parameters("@CompanyName").Value = customer.CompanyName
    cmdAdd.Parameters("@ContactName").Value = customer.ContactName
    cmdAdd.Parameters("@EstablishedYear").Value = customer.EstablishedYear
    cmdAdd.Parameters("@Incorporated").Value = customer.Incorporated
 
    '
    ' Add record
    '
    affected = cmdAdd.ExecuteNonQuery()
 
    '
    ' If affected equals 1, this means the record was added,
    ' in turn get the new primary key by changing the command text.
    ' No need to clear parameters.
    '
    If affected = 1 Then
     cmdAdd.CommandText = "Select @@Identity"
     customer.Id = CInt(cmdAdd.ExecuteScalar)
    End If
 
   Next
 
  End Using
 Catch ex As Exception
  mHasException = True
  mLastException = ex
  '
  ' Exit on the first exception.
  '
  Return False
 End Try
 
 Return IsSuccessFul
 
End Function

Method to create an OleDb command with parameters.

''' <summary>
''' Used to setup parameters for the method AddNewCustomerRecords
''' </summary>
''' <param name="cn">A OleDbConnection that has been created as in AddNewCustomerRecords</param>
''' <returns>Fully prepared command object</returns>
Private Function AddRecordCommand(cn As OleDbConnection) As OleDbCommand
 Dim cmd As New OleDbCommand With {.Connection = cn}
  
 cmd.CommandText =
  <SQL>
   INSERT INTO Customer 
   (
    CompanyName,
    ContactName,
    EstablishedYear,
    Incorporated
   ) 
   VALUES 
   (
    @CompanyName,
    @ContactName,
    @EstablishedYear,
    @Incorporated
   )
  </SQL>.Value
  
 cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@CompanyName",
       .DbType = DbType.String})
  
 cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@ContactName",
       .DbType = DbType.String})
  
 cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@EstablishedYear",
       .OleDbType = OleDbType.Integer})
  
 cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@Incorporated",
       .OleDbType = OleDbType.Date})
  
 Return cmd
  
End Function

TIP If at anytime a query does not work as expected using parameters makes determine an issue is sometimes almost impossible leading a developer to move away from parameters and move to old string concatenation or dream up your own method to deal with parameters such as creating a helper using IFormatProvider, something like the following.

Public Class SqlFormatter
 Implements ICustomFormatter
 Public Function Format(
        pFormat As String,
        pArg As Object,
        pFormatProvider As IFormatProvider) As String _
  Implements ICustomFormatter.Format
 
  If pArg Is Nothing Then
   Return "NULL"
  End If
  If TypeOf pArg Is String Then
   Return "'" & CStr(pArg).Replace("'", "''") & "'"
  End If
  If TypeOf pArg Is Date Then
   Return "'" & CDate(pArg).ToString("MM/dd/yyyy") & "'"
  End If
  If TypeOf pArg Is IFormattable Then
   Return CType(pArg, IFormattable).
    ToString(pFormat, Globalization.CultureInfo.InvariantCulture)
  End If
 
  Return pArg.ToString()
 
 End Function
End Class

Helper class (for working in the form code)

Public Module Helpers
 Public Function Sql(formattable As FormattableString) As String
  Return formattable.ToString(New SqlFormatProvider())
 End Function
End Module

Form code

Public Class Form1
 Private Sub Button1_Click(sender As Object, e As EventArgs) _
  Handles Button1.Click
 
  Dim values As New Dictionary(Of String, String) From
    {{"FirstName", "Karen"}, {"LastName", "O'Brien"}}
 
  Dim strSQLSave = Sql(
  $"INSERT INTO Table1 (FirstName, LastName) values ({values("Karen")}, {values("Payne")})")
 
  Console.WriteLine(strSQLSave)
 
 End Sub
End Class

There is a better method to debug SQL with parameters using the following class. 

Namespace DataHelpers
 Public Module CommandPeeker
  ''' <summary>
  ''' Provides the ability to view a parameterized query values when using parameter names
  ''' such as @CompanyName but will not work with a parameter simply named ?.
  ''' </summary>
  ''' <param name="sender"></param>
  ''' <param name="token">Alternate parameter start token</param>
  ''' <returns></returns>
  ''' <remarks></remarks>
  <Runtime.CompilerServices.Extension()>
  Public Function ActualCommandTextByNames(
   sender As IDbCommand,
   Optional token As String = "@") As String
 
   Dim sb As New Text.StringBuilder(sender.CommandText)
   Dim emptyParameterNames =
     (
     From T In sender.Parameters.Cast(Of IDataParameter)()
     Where String.IsNullOrWhiteSpace(T.ParameterName)).FirstOrDefault
 
   If emptyParameterNames IsNot Nothing Then
    Return sender.CommandText
   End If
 
   For Each p As IDataParameter In sender.Parameters
 
    Select Case p.DbType
     Case DbType.AnsiString, DbType.AnsiStringFixedLength,
      DbType.Date, DbType.DateTime, DbType.DateTime2,
      DbType.Guid, DbType.String, DbType.StringFixedLength, DbType.Time, DbType.Xml
      If p.ParameterName(0) = token Then
       If p.Value Is Nothing Then
        Throw New Exception("no value given for parameter '" &
             p.ParameterName & "'")
       End If
       sb = sb.Replace(
        p.ParameterName, $"'{p.Value.ToString.Replace("'", "''")}'")
      Else
       sb = sb.Replace(
       String.Concat(
       token, p.ParameterName), $"'{p.Value.ToString.Replace("'", "''")}'")
      End If
     Case Else
      sb = sb.Replace(p.ParameterName, p.Value.ToString)
    End Select
   Next
   Return sb.ToString
  End Function
 
 End Module
End Namespace

Usage and doing a comparison between what is normally seen with parameters and with the extension method above.

Console.WriteLine(cmd.CommandText)
Console.WriteLine(cmd.ActualCommandTextByNames())

The output of both lines above will display in the Visual Studio Output window.

This is the display for the first console write line, we see no values.

INSERT INTO Customer 
 (
  CompanyName,
  ContactName,
  ContactTitle
 ) 
Values
 (
  @CompanyName,
  @ContactName,
  @ContactTitle
 )

While the output for the second write line using ActualCommandTextByBName the values are exposed which were passed into the query.

INSERT INTO Customer 
 (
  CompanyName,
  ContactName,
  ContactTitle
 ) 
Values
 (
  'Karen''s coffee',
  'Karen Payne',
  'Accounting Manager'
 )

Best to use parameters instead of string concatenation or creating your own routine to deal with values for any SQL statement. 

Updating records

Since in the insert record passing values in one example and a list in another, this update will be done by passing a DataRow to the update method. Both inserts or updates can be done with values passed, a list or single instance of a class, a DataRow or a DataTable.

There are common ways to create parameters, using cmd.Parameters.Add and cmd.Parameters.AddWithValue. In the update method, AddWithValue is used as done in a prior example in part one of this series where the command object and parameters were created in a different method then were executed.

.

An assertion is performed on cmd.ExecuteNonQuery to see if the value returned is 1 which indicates that the row was actually updated.

Public Function UpdateRow(pRow As DataRow) As Boolean
 
 Try
  Using cn As New OleDbConnection(ConnectionStringWithPassword)
 
   Using cmd As New OleDbCommand With {.Connection = cn}
    cmd.CommandText = <SQL>
     UPDATE 
      Customer 
     SET 
      Process = @Process,
      CompanyName = @CompanyName, 
      ContactName = @ContactName,
      ContactTitle = @ContactTitle
     WHERE Identifier = @Identifier
    </SQL>.Value
 
    Dim processParameter As New OleDbParameter With
      {
       .DbType = DbType.Boolean,
       .ParameterName = "@Process",
       .Value = pRow.Field(Of Boolean)("Process")
      }
 
    cmd.Parameters.Add(processParameter)
 
    Dim companyNameParameter As New OleDbParameter With
      {
       .DbType = DbType.String,
       .ParameterName = "@CompanyName",
       .Value = pRow.Field(Of String)("CompanyName")
      }
 
    cmd.Parameters.Add(companyNameParameter)
 
    Dim contactNameParameter As New OleDbParameter With
      {
       .DbType = DbType.String,
       .ParameterName = "@ContactName",
       .Value = pRow.Field(Of String)("ContactName")
      }
 
    cmd.Parameters.Add(contactNameParameter)
 
    Dim contactTitleParameter As New OleDbParameter With
      {
       .DbType = DbType.String,
       .ParameterName = "@ContactTitle",
       .Value = pRow.Field(Of String)("ContactTitle")
      }
 
    cmd.Parameters.Add(contactTitleParameter)
 
    Dim identifierParameter As New OleDbParameter With
      {
       .DbType = DbType.Int32,
       .ParameterName = "@Identifier",
       .Value = pRow.Field(Of Integer)("Identifier")
      }
 
    cmd.Parameters.Add(identifierParameter)
 
    Try
     cn.Open()
 
     Dim affected = cmd.ExecuteNonQuery
     Return affected = 1
 
    Catch ex As Exception
 
     mHasException = True
     mLastException = ex
 
     Return IsSuccessFul
 
    End Try
   End Using
  End Using
 
 Catch ex As Exception
 
  mHasException = True
  mLastException = ex
 
  Return IsSuccessFul
 
 End Try
End Function

TIPS

  • When adding or updating records there should be two assertions, one did the operation work or not and secondly was an exception thrown. It's possible for an operation like an update to fail without an exception e.g. in a multi-user environment the record to update was removed by another user. 
  • Before performing an insert or update, validate that field values match business rules, for example when entering a customer they must have a name but may not need a phone number right away.
  • Understand how to deal with database constraints. Developers without proper knowledge of databases will set a constraint in the database then wonder why there is a constraint violation when attempting to work on a field that for instance does not permit duplicates or as discussed before cascading rules prohibit an action.

Removing/deleting records

Removing a record in a single table solution is easy while in a relational database model considerations must be applied e.g. can a customer record be removed without removal of child tables or can a child table be removed while keeping the parent table which sends someone back to the drawing board if during testing or in production a delete operation throws a runtime exception.

  • Consider database rules to allow or disallow an action.
  • Business rules coupled with proper cascading properties of multiple related tables.

Removal at the core is simple, pass in a primary key, call the DELETE and do assertions as discussed above. If the record does not exists and no errors the method returns false.

Public Function RemoveCurrentCustomer(pIdentfier As Integer) As Boolean
 Try
  Using cn As New OleDbConnection(ConnectionStringWithPassword)
   Using cmd As New OleDbCommand With {.Connection = cn}
  
    cmd.CommandText = "DELETE FROM Customer WHERE Identifier = @Identifier"
  
    Dim identifierParameter As New OleDbParameter With
     {
      .DbType = DbType.Int32,
      .ParameterName = "@Identifier",
      .Value = pIdentfier
     }
  
    cmd.Parameters.Add(identifierParameter)
  
    Try
     cn.Open()
  
     Dim affected = cmd.ExecuteNonQuery
     Return affected = 1
  
    Catch ex As Exception
     mHasException = True
     mLastException = ex
     Return False
    End Try
   End Using
  End Using
  
 Catch ex As Exception
  mHasException = True
  mLastException = ex
  Return IsSuccessFul
 End Try
End Function

Working with transactions

"MS- Access databases include support for transactions, which enable multiple database commands to be grouped into a single logical unit of work. The database commands that comprise a transaction are guaranteed to be atomic, meaning that either all commands will fail or all will succeed. 

In general, transactions are implemented through SQL statements using the following pattern:

  1. Indicate the start of a transaction.
  2. Execute the SQL statements that comprise the transaction.
  3. If there is an error in one of the statements from Step 2, rollback the transaction.
  4. If all of the statements from Step 2 complete without error, commit the transaction.

When modifying data in a batch transaction, atomicity is not always needed. In some scenarios, it may be acceptable to have some data modifications succeed and others in the same batch fail, such as when deleting a set of emails from an email client. If there s a database error midway through the deletion process, it is probably acceptable that those records processed without error remain deleted. In such cases, the DAL does not need to be modified to support database transactions. There are other batch operation scenarios, however, where atomicity is vital. When a customer moves her funds from one bank account to another, two operations must be performed: the funds must be deducted from the first account and then added to the second. While the bank may not mind having the first step succeed but the second step fail, its customers would understandably be upset." [docs.microsodft.com]

In the following code sample, only one operation is performed to keep clear how to implement a transaction, in reality, a customer and an order and order details may be added. If the order details failed then the order and customer insert would rollback along with the order details.

Public Function AddNewRow(
 companyName As String,
 contactName As String,
 establishedYear As Integer,
 incorporated As Date,
 ByRef pIdentfier As Integer) As Boolean
  
 Using cn As New OleDbConnection(ConnectionString)
  Dim transaction As OleDbTransaction = Nothing
  Dim cmd = AddRecordCommand(cn)
  Try
   cn.Open()
   transaction = cn.BeginTransaction()
   cmd.Transaction = transaction
  
   cmd.Parameters("@CompanyName").Value = companyName
   cmd.Parameters("@ContactName").Value = contactName
   cmd.Parameters("@EstablishedYear").Value = establishedYear
   cmd.Parameters("@Incorporated").Value = incorporated
  
   cmd.ExecuteNonQuery()
  
   cmd.CommandText = "Select @@Identity"
   pIdentfier = CInt(cmd.ExecuteScalar)
  
   transaction.Commit()
  
  Catch oleDbException As OleDbException
  
   transaction.Rollback()
  
   mHasException = True
   mLastException = oleDbException
  
  Catch ex As Exception
  
   mHasException = True
   mLastException = ex
  
  End Try
 End Using
  
  
 Return IsSuccessFul
  
End Function

Working with Data Readers

Data readers are a good choice to read data that does not need to be changed. For example, a table contains country names is needed to be displayed in a ComboBox to allow a user to select a user's country where in the record added or modified only the primary key for the country is remembered in a number field while another example is to read one or more tables to create a list.

In the following example, the database holds computer information where in this case there is an interested to learn besides the current user of the computer who is the past has used this computer.

Two classes to represent data read via the data readers.

Public Class ComputerAsset
 Public Property AssetId() As Integer
 Public Property UserName() As String
 Public Property ComputerName() As String
 Public Property ServiceTag() As String
 Public Property FormerUsers() As List(Of User)
 
 Public Sub New()
  FormerUsers = New List(Of User)
 End Sub
 
 Public Overrides Function ToString() As String
  Return ComputerName
 End Function
 
End Class
Public Class User
 Public Property UserId() As Integer
 Public Property AssetId() As Integer
 Public Property Name() As String
 
 Public Overrides Function ToString() As String
  Return $"{Name}"
 End Function
 
End Class

Special note: Each class overrides ToString as this will be picked up for what to display in the user interface, in this case, ListBox controls, same works for ComboBox controls. 

Moving on to the read operations, since there are two data readers used in a loop each reader is given a clear name to assist in writing the code.

The outer while statement obtains computers then in the inner while user information is obtained while in the outer loop. As in all other code samples, the same exception handling is used.

Imports System.Data.OleDb
Imports KarensBaseClasses
 
Public Class DatabaseOperations
 Inherits AccessConnection
 
 Public Sub New()
  DefaultCatalog = IO.Path.Combine(
   AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
 End Sub
 
 Public Function GetComputerNames() As List(Of ComputerAsset)
 
  Dim results = New List(Of ComputerAsset)
 
  mHasException = False
 
  Dim selectComputerStatement =
    "SELECT AssetId, UserName, ComputerName, ServiceTag " &
    "FROM ComputerAsset;"
  Dim selectUserStatement =
    "SELECT UserId, AssetId, UserName " &
    "FROM [User] WHERE AssetId = @AssetId;"
 
  Dim assetId As Integer = 0
 
  Using cn As New OleDbConnection(ConnectionString)
   Using cmdComputer As New OleDbCommand With {.Connection = cn}
 
    cmdComputer.CommandText = selectComputerStatement
 
    Try
 
     cn.Open()
 
     Dim readerComputer = cmdComputer.ExecuteReader()
 
     While readerComputer.Read()
 
      assetId = readerComputer.GetInt32(0)
 
      Dim currentComputer = New ComputerAsset() With
        {
         .assetId = assetId,
         .UserName = readerComputer.GetString(1),
         .ComputerName = readerComputer.GetString(2),
         .ServiceTag = readerComputer.GetString(3)
        }
 
      results.Add(currentComputer)
 
      Using cmdUser As New OleDbCommand With {.Connection = cn}
 
       cmdUser.CommandText = selectUserStatement
       cmdUser.Parameters.AddWithValue("@AssetId", assetId)
 
       Dim readerUser = cmdUser.ExecuteReader()
 
       While readerUser.Read()
 
        currentComputer.FormerUsers.Add(New User() With
          {
            .assetId = assetId,
            .UserId = readerUser.GetInt32(0),
            .Name = readerUser.GetString(2)
          })
 
       End While
 
      End Using
     End While
    Catch ex As Exception
     mHasException = True
     mLastException = ex
    End Try
   End Using
  End Using
 
 
  Return results
 
 End Function
 
End Class

In the calling form, one ListBox will be populated with computers then as items change from a user traversing items the SelectedIndex event is triggered which then  changes the DataSource of the second ListBox for users of the computer. A DataSet with a relationship could also be used in tangent with two BindingSource components but in this case, there are only a handful of records so using data readers with lightweight classes makes sense.

Summary

In this article how to add, update and delete records has been presented for basic CRUD operations along with using transactions for both single and multiple operations failures for commits and rollbacks. Data readers for read only access to data.

Implementation, tie a Customer (DataTable) to an Order (DataTable) by the key field Identifier which resides in both tables.

Source code

Github repository