Share via

MS-Access with VB.NET: Best practices (Part 3)

  • Article

Series

See Part 1 and Part 2 of this series

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  and part 2 which covers all the basics for designing a database, using base classes for connections with or without a database password, exception handling, CRUD operations, working with transactions and more. Basics for reading data, removal of data where in this part adding and editing data will be covered along with other important topics.c

Master-details relations

One of the most common scenarios for using the DataGridView control is the master/detail form, in which a parent/child relationship between two (or more tables) database tables is displayed. Selecting rows in the master table causes the detail table to update with the corresponding child data.

Implementing a master/detail form is easy using the interaction between the DataGridView control and the BindingSource component.

In the screenshots below, the first example display customers and their orders while the second example uses three ListBox controls to display a clothing product, available colors for the product and what sizes are available for a specific color.

Although displaying related data is easy there are other aspects which will go over next that require some knowledge of how to interact with the tables which are stored in a relational DataSet exposed using BindingSource components.

In the code samples the connection is done differently in that there is a singleton class to manage connections as some developer prefer this method rather than creating a connection for each method to access data. Rather than go over the connection process, examine the code in MS_AccessConnection class.

<>

Using a OleDbDataAdapter data is read into a DataSet for each relationship in this case customers and orders.

Method to load data and setup relationships between Customers and Orders table.

Imports System.Data.OleDb
Imports System.Windows.Forms
Imports MasterDetailsDataLibrary.Classes
Imports MasterDetailsDataLibrary.LanguageExtensions
 
''' <summary> 
''' Responsible for loading data from MS-Access followed  
''' by setting up BindingSource components and data relationships 
''' which will be used in a Win-form project. 
''' </summary> 
''' <remarks> 
'''  
''' </remarks> 
Public Class CustomerOrdersSetup
 ''' <summary> 
 ''' This is the master data 
 ''' </summary> 
 ''' <value></value> 
 ''' <returns></returns> 
 ''' <remarks></remarks> 
 Public Property MasterBindingSource As  New BindingSource
 ''' <summary> 
 ''' This is the details to the Master  
 ''' </summary> 
 ''' <value></value> 
 ''' <returns></returns> 
 ''' <remarks></remarks> 
 Public Property DetailsBindingSource As  New BindingSource
 
 Public Sub  New()
 End Sub
 ''' <summary> 
 ''' Setup master/details for displaying. 
 ''' </summary> 
 ''' <param name="errorMessage"></param> 
 ''' <returns></returns> 
 ''' <remarks> 
 ''' There are some extra assertion on failure to open a table 
 ''' in the event a developer while coding had one of the tables 
 ''' opened exclusively, it happens :-) 
 ''' </remarks> 
 Public Function Load(ByRef errorMessage As String) As Boolean
  Dim result As  Boolean
  Dim cn As OleDbConnection = Nothing
 
  Dim conn As MS_AccessConnection = MS_AccessConnection.GetInstance
  cn = conn.GetConnection(Builder.ConnectionString)
 
  ' 
  ' At this point our database connection is open and will stay 
  ' stay open until the app quits or you close the connection. If  
  ' you close the connection and use conn above the connection is 
  ' re-opened for you. Same thing if you were to use a Using Statement 
  ' which closes a connection, MS_AccessConnection class will re-open 
  ' as needed. 
  ' 
 
  Try
   Dim da As OleDbDataAdapter
   Dim ds As  New DataSet
 
   da = New OleDbDataAdapter(
   <SQL> 
    SELECT   
     Identifier,  
     CompanyName,  
     Address,  
     City,  
     PostalCode,  
     Phone 
    FROM   
     Customers  
    ORDER BY  
     CompanyName; 
   </SQL>.Value,
   cn)
 
   Try
    ' 
    ' Load Customers (Master to Orders) data into a DataTable in the DataSet (ds). 
    ' 
    da.Fill(ds, "Customers")
 
   Catch oex As OleDbException
    If oex.Message.Contains("exclusively locked") Then
     errorMessage =  "You have the Customer table open"
     Return False
    End If
   End Try
 
   ' 
   ' Note an alias is done in the SQL 
   ' 
   da = New OleDbDataAdapter(
   <SQL> 
    SELECT  
     Identifier,  
     OrderDate,  
     ShippedDate,  
     ShipName,  
     ShipAddress,  
     ShipCity,  
     ShipRegion,  
     ShipPostalCode,  
     Freight,  
     OrderID,  
     Orders.EmployeeID,  
     Employees.FirstName + ' '+ Employees.LastName As Employee, 
     Employees.HomePhone + ' ' + Employees.Extension As Phone 
    FROM Employees INNER JOIN Orders ON 
     Employees.EmployeeID = Orders.EmployeeID 
    ORDER BY Orders.ShippedDate DESC; 
   </SQL>.Value,
   cn)
 
   Try
 
    da.Fill(ds, "Orders")
    ' 
    ' Load Orders (detail to Customers) data into a DataTable in the DataSet (ds). 
    ' 
    ds.Tables("Orders").Columns.Add(New DataColumn With
      {
       .ColumnName =  "Process", .DataType = GetType(Boolean),
       .DefaultValue =  False
      })
 
 
   Catch oex As OleDbException
 
    If oex.Message.Contains("exclusively locked") Then
     errorMessage =  "You have the orders or employee table open"
     Return False
    End If
 
   End Try
 
   ds.Tables("Orders").Columns("EmployeeID").ColumnMapping = MappingType.Hidden
 
   ds.SetRelation("Customers", "Orders", "Identifier")
 
   ' 
   ' Let's create a DataColumn Expression on the customer table reaching down 
   ' into the details table via 'Child' in FreightExpression below. 
   ' 
   Dim freightExpression = "Sum(Child(CustomersOrders).Freight) "
 
   ds.Tables("Customers").Columns.Add(
    New DataColumn With
    {
     .ColumnName =  "Freight",
     .DataType =  GetType(String),
     .Expression = freightExpression
    }
   )
 
   MasterBindingSource.DataSource = ds
   MasterBindingSource.DataMember = ds.Tables(0).TableName
 
   DetailsBindingSource.DataSource = MasterBindingSource
   DetailsBindingSource.DataMember = ds.Relations(0).RelationName
 
   result = True
  Catch ex As Exception
   errorMessage = ex.Message
   result = False
  End Try
 
  Return result
 
 End Function
 
End Class

Once the tables are read into a DataSet relationships are set up using a language extension method.

Public Module Sample
 <DebuggerStepThrough()>
 <Runtime.CompilerServices.Extension()>
 Public Sub SetRelation(
        dataSet As DataSet,
        masterTableName  As  String,
        childTableName  As  String,
        keyColumn  As  String)
 
  dataSet.Relations.Add(
   New DataRelation(String.Concat(masterTableName, childTableName),
        dataSet.Tables(masterTableName).Columns(keyColumn),
        dataSet.Tables(childTableName).Columns(keyColumn)
        )
   )
 
 End Sub
End Module

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

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

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

ds.SetRelation("Customers", "Orders", "Identifier")

During the relationship setup, an important thing to mention is "accessing child data in the parent table". This is done using DataTable column Expression where "Child" references the Orders table to access the freight field for all order for a specific customer and by using "Sum" get the total freight sum.

Dim freightExpression = "Sum(Child(CustomersOrders).Freight) "

Next two BindingSource components are created and set up for the relationship.

Master.DataSource = ds 
Master.DataMember = ds.Tables(0).TableName 
  
Details.DataSource = Master 
Details.DataMember = ds.Relations(0).RelationName
  1. The first line assigns the DataSet (ds) to the DataSource of the BindingSource component (Master) to the Customer table (ordinal index of 0).
  2. Line two assigns a BindingSource Details.DataSource to the BindingSource component which just was set up for the master BindingSource component. Unlike in step 1 a table was designated as the data source, in this case, the relationship name (there is only the one in the prior code block) tells Details BindingSource which table to work with, the Order table. 
  3. In the calling form, both BindingSource components are set to BindingSource components in the form. Once loaded the first two BindingSource components are no longer in memory.

In the form calling the load method once the data has been set up both DataGridView controls DataGridViewColumns are set up for presenting the data which includes subscribing to CellFormatting event for the master DataGridView.

Working with binary data

When working with physical files the data type to use is OLE Object, not Attachments as they can be extremely difficult to work with using OleDb data provider.

All the same rules and methods apply as when working with strings, dates, numbers and boolean except to work with binary data the following classes are utilized.

  • FileStream.
  • BinaryReader

When dealing with larger files to insert into a database it may be prudent to use a BackGroundWorker or asynchronous programming e.g. Await a Task.

In the source code look at the class DataOperations in BackendBlobs class library. The user interface portion is in the project WorkingWithBinaryData (a Windows forms project).

A closely related topic is interacting with images as shown in the following project for displaying, adding and save to the file system.

Searching the database tables

There are many flavors of searching for data. The main consideration is understanding how to construct a WHERE condition with one or more conditions along with do the conditions need to be exclusive or not meaning is a AND or OR or both needed. Follow this with does the search need to be case sensitive or case insensitive and does the search need to be a full search, starts with, ends with, contains etc.

When composing a search do not start by writing the query in code, instead, write the query in MS-Access and test it out. Once satisfied bring the query into your code and add replacement parameters.

Example, the requirement is to see if values entered for a user name or full name are found in a table if so alert the user that they are blacklisted.

Write the query in MS-Access which when finished looks something like this.

SELECT tbl_BlackList.ID, tbl_BlackList.User_Name, tbl_BlackList.Full_Name 
FROM tbl_BlackList 
WHERE (((tbl_BlackList.User_Name)="bills") OR ((tbl_BlackList.Full_Name)="bils"));

Note that case sensitivity is not a concern here, the query goes case insensitive matches. For case sensitive, we would use the following.

(this is getting ahead of the process)

SELECT ID 
FROM 
 tbl_BlackList 
WHERE  
 (StrComp(User_Name,@UserName, 0) = 0) OR (StrComp(Full_Name,@FullName, 0) = 0)

Once the query has been tested clean up the query as shown below and replace hard-coded values with parameters.

Public Function IsBlackListed(userName As  String, fullName As String) As Boolean
  
 Using cn As New OleDbConnection(ConnectionString) 
  
  Using cmd As New OleDbCommand  With {.Connection = cn} 
   cmd.CommandText = 
    <SQL> 
     SELECT ID 
     FROM  
      tbl_BlackList 
     WHERE  
      User_Name=@UserName OR Full_Name=@FullName; 
    </SQL>.Value 
   cn.Open() 
  
   cmd.Parameters.AddWithValue("@UserName", userName) 
   cmd.Parameters.AddWithValue("@FullName", fullName) 
  
   Dim reader = cmd.ExecuteReader() 
  
   Return reader.HasRows 
  
  End Using 
 End Using 
  
End Function

Filtering DataGridView

A typical request for an application is filtering data loaded into a DataGridView. A logical choice is to provide built-in functionality into the DataGridView control over creating controls on the same form or a child form to filter the DataGridView.

Microsoft developed a DataGridViewAutoFilterTextBoxColumn that is not part of the standard columns available for a standard DataGridView which is provided in the source code for this article, the original source.

To use the DataGridViewAutoFilterTextBox, add a new column to a DataGridView, set ColumnType to DataGridViewAutoFilterTextBox and set DataPropertyName to the source field of the DataGridView.DataSource.

Load data, in this case from an MS-Access database table into a DataTable where the DataTable is set to the DataSource of a BindingSource component.

Private Sub Form1_Shown(sender As  Object, e As EventArgs) Handles Me.Shown
 Dim ops As  New DatabaseOperations
 
 If ops.IsSuccessFul Then
  bsCustomers.DataSource = ops.LoadCustomers
  DataGridView1.DataSource = bsCustomers
 Else
  MessageBox.Show(ops.LastExceptionMessage)
 End If
 
End Sub

In each column setup as per above, there is an item for removal of a filter. To remove all filters, in this case in a button click event add the following which calls RemoveFilter passing in the DataGridView object.

Private Sub removeAlFiltersButton_Click(sender As  Object, e As EventArgs) _
 Handles removeAlFiltersButton.Click
 DataGridViewAutoFilterColumnHeaderCell.RemoveFilter(DataGridView1)
End Sub

Additionally using the following code by subscribing to DataBindingComplete event for the DataGridView along with a label provides a visual cue how many records match the current filter for all columns with a filter.

Private Sub DataGridView1_DataBindingComplete(
 sender As Object,
 e As DataGridViewBindingCompleteEventArgs) Handles DataGridView1.DataBindingComplete
 
 Dim filterStatus As String = DataGridViewAutoFilterColumnHeaderCell.
   GetFilterStatus(DataGridView1)
 
 If String.IsNullOrWhiteSpace(filterStatus) Then
  filterStatusLabel.Visible = False
 Else
  filterStatusLabel.Visible = True
  filterStatusLabel.Text = filterStatus
 End If
End Sub

Another option is a custom filter popup found here on Code Project. This component takes two lines of code to implement while that will provide most needs the component provides a great deal of customization for advanced filtering.

Upscaling

Throughout this article data has been from Microsoft Access using base classes, if there is a decision to upscale to Microsoft SQL-Server using these classes little changes are needed, not large changes.

The first step is to analyze the database schema, does it need to be normalized? If so now is the time to normalize the database. Once this decision has been made perform the following steps to replace the Access connection with the SQL-Server connection. Both connection classes have ConnectionString property to use for creating a data connection.

Compare how the Access connection is setup

Namespace Classes
 Public Class DatabaseOperations
  Inherits AccessConnection
 
  ''' <summary>
  ''' Default our connection to a database in the executable 
  ''' folder when not using a password
  ''' </summary>
  ''' <remarks>
  ''' Not used in the code sample but this is how to do a 
  ''' connection not encrypted.
  ''' </remarks>
  Public Sub  New()
   DefaultCatalog = Path.Combine(
    AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
  End Sub

Compared to the SQL-Server connection.

Namespace Classes
 Public Class DatabaseOperations
  Inherits BaseSqlServerConnection
 
  Public Sub  New()
   DefaultCatalog =  "NorthWindAzure3"
 
  End Sub

Next, in the MS-Access method to read data the data provider is OleDb for the connection and command.

Using cn As New OleDbConnection(ConnectionString)
 Using cmd As New OleDbCommand  With {.Connection = cn}

For SQL-Server the data provider changes to SqlClient.

Using cn As New SqlConnection(ConnectionString)
 Using cmd As New SqlCommand  With {.Connection = cn}

If the database schema changed then adjust the SELECT statement (and also other related statements) to work with the new schema. For this exercise, the schema has been normalized from.

SELECT
 C.Identifier, 
 C.CompanyName, 
 CT.ContactTitleId, 
 CT.Title, C.Address, 
 C.City, C.PostalCode, 
 C.Country
FROM
 ContactTitle AS CT 
INNER JOIN
 Customers AS C ON CT.ContactTitleId = C.ContactTitleId
ORDER BY
 CompanyName;

To the following (consider this unnecessary if the schema is deemed okay).

SELECT  C.CustomerIdentifier ,
   C.CompanyName ,
   C.ContactIdentifier ,
   C.ContactTypeIdentifier ,
   CT.ContactTitle ,
   Contact.FirstName ,
   Contact.LastName ,
   C.Street ,
   C.City ,
   C.PostalCode ,
   C.CountryIdentfier ,
   country.CountryName
FROM  dbo.Customers AS C
   INNER JOIN dbo.Contact ON C.ContactIdentifier = Contact.ContactIdentifier
   INNER JOIN dbo.ContactType AS CT ON C.ContactTypeIdentifier = CT.ContactTypeIdentifier
   INNER JOIN dbo.Countries AS country ON  C.CountryIdentfier = country.id
ORDER BY C.CompanyName;

 

In the above case, any data bindings need to be inspected to ensure the correct DataPropertyName is still the same, in the above case several bindings needed to be changed e.g. the customer primary key name changed, more tables are used in the SQL-Server database then with Microsoft Access.

Additional considerations, taking advantage to run multiple query statements together with SQL-Server, for example, insert a new record and return the new record's key.

Dim contactInsertStatement =  
  <SQL> 
  INSERT INTO dbo.Contact 
     (FirstName 
     ,LastName) 
    VALUES
     (@FirstName 
     ,@LastName);  
  SELECT CAST(scope_identity() AS int); 
  </SQL>.Value

Using a connection and command object no different than done with Microsoft Access which executes via Execute methods the above will insert and return the new key using.

CInt(cmd.ExecuteScalar())

The following shows inserting several records where a transaction is used (transactions work the same with MS-Access as discussed earlier). In this case, a lightweight data container is used rather than pass each field as an argument to the add method.

Public  Function AddNewCustomer(pCustomer As customer)  As  Boolean
 mHasException =  False
  
 dim contactInsertStatement =  
   <SQL> 
   INSERT INTO dbo.Contact 
      (FirstName 
      ,LastName) 
     VALUES 
      (@FirstName 
      ,@LastName);  
   SELECT CAST(scope_identity() AS int); 
   </SQL>.Value 
  
  
 Dim contactDevicesInsertStatement =  
   <SQL> 
   INSERT INTO dbo.ContactContactDevices 
      (ContactIdentifier 
      ,PhoneTypeIdenitfier 
      ,PhoneNumber 
      ,Active) 
     VALUES 
      (@ContactIdentifier 
      ,@PhoneTypeIdenitfier 
      ,@PhoneNumber 
      ,@ACTIVE); 
    SELECT CAST(scope_identity() AS int); 
   </SQL>.Value 
  
 Dim customerInsertStatement =  
   <SQL> 
   INSERT INTO dbo.Customers 
      (CompanyName 
      ,ContactName 
      ,ContactIdentifier 
      ,ContactTypeIdentifier 
      ,Street 
      ,City 
      ,PostalCode 
      ,CountryIdentfier 
      ,Phone 
      ,ModifiedDate) 
     VALUES 
      (@CompanyName  
      ,@ContactName 
      ,@ContactIdentifier 
      ,@ContactTypeIdentifier 
      ,@Street 
      ,@City 
      ,@PostalCode 
      ,@CountryIdentfier 
      ,@Phone 
      ,@ModifiedDate); 
    SELECT CAST(scope_identity() AS int); 
   </SQL>.Value 
  
 Dim contactIdentifier As Integer = 0 
 Dim contactDeviceIdentifier as Integer = 0 
  
 Using cn As New SqlConnection  With{.ConnectionString = ConnectionString} 
  
  
  cn.Open() 
  
  Dim sqlTran As  SqlTransaction = cn.BeginTransaction() 
     
  Using cmd As New SqlCommand  With 
   { 
    .Connection = cn,  
    .CommandText = contactInsertStatement,  
    .Transaction = sqlTran 
   } 
  
  
   Try
    cmd.Parameters.AddWithValue("@FirstName",pCustomer.ContactFirstName) 
    cmd.Parameters.AddWithValue("@LastName",pCustomer.ContactLastName) 
  
    contactIdentifier =  CInt(cmd.ExecuteScalar()) 
  
    cmd.CommandText = contactDevicesInsertStatement 
    cmd.Parameters.Clear() 
  
    cmd.Parameters.AddWithValue("@ContactIdentifier",contactIdentifier) 
    cmd.Parameters.AddWithValue("@PhoneTypeIdenitfier",pCustomer.PhoneTypeIdenitfier) 
    cmd.Parameters.AddWithValue("@PhoneNumber",pCustomer.PhoneNumber) 
    cmd.Parameters.AddWithValue("@Active",True) 
  
    contactDeviceIdentifier = CInt(cmd.ExecuteScalar()) 
  
    cmd.CommandText = customerInsertStatement 
    cmd.Parameters.Clear() 
  
    cmd.Parameters.AddWithValue("@CompanyName",pCustomer.CompanyName) 
    cmd.Parameters.AddWithValue("@ContactName",pCustomer.ContactName) 
    cmd.Parameters.AddWithValue("@ContactIdentifier",contactIdentifier) 
    cmd.Parameters.AddWithValue("@ContactTypeIdentifier",pCustomer.ContactTypeIdentifier) 
    cmd.Parameters.AddWithValue("@Street",pCustomer.Street) 
    cmd.Parameters.AddWithValue("@City",pCustomer.City) 
    cmd.Parameters.AddWithValue("@PostalCode",pCustomer.PostalCode) 
    cmd.Parameters.AddWithValue("@Phone",pCustomer.PhoneNumber) 
    cmd.Parameters.AddWithValue("@CountryIdentfier",pCustomer.CountryIdentifier) 
    cmd.Parameters.AddWithValue("@ModifiedDate",Now) 
  
    pCustomer.CustomerIdentifier = CInt(cmd.ExecuteScalar()) 
    pCustomer.ContactIdentifier = contactIdentifier 
    pCustomer.DevicesIdentifier = contactDeviceIdentifier 
    sqlTran.Commit() 
  
    return True
  
   Catch sex As SqlException 
  
    sqlTran.Rollback() 
    mHasException =  True
  
    Return False
  
   Catch ex As Exception 
    mHasException =  True
    mLastException = ex 
  
    Return False
  
   End Try
  End Using 
 End Using 
End Function

The above done is SQL-Server was made easy to transition from MS-Access to SQL-Server by following a pattern rather than haphazardly writing code in the fly.

References

(Some of the links are more relevant to part 2 than this article)

Source code

GitHub repository for the entire series.

Summary

In this part of the series master detail, basics have been covered while in the source code there are more advanced things to learn about. Basics for working with binary data where in the source code there is a great deal to learn from other than what has been presented above and finally basics on some part of searching. Searching is a very broad topic which is beyond the scope of this article.

In conclusion, material presented in this series should be considered a base framework to move forward with when working with Microsoft Access database. The framework can easily upsize to SQL-Server with little effort starting with connection class (and there is a base SQL-Server class provided in the repository) while with no much more effort will work with Oracle as the author can attest to as she has Interfaces and base classes for working with Access, SQL-Server and Oracle in a base library.

By writing database operations within forms limits a developer in reuse of code and if and when a form becomes corrupt that is the time to think it's time to move to data classes but with this knowledge, this can be done now and save time and headaches down the road.

In this series, all common operations have been covered for most operations typically needed to write a data-centric solution with VB.NET and Microsoft Access databases. Following these recommendations and patterns upscaling to Microsoft SQL-Server code wise does not require a large code refactor. 

Moving to SQL-Server, connections have a slightly different syntax, see BaseSqlConnection library in KarensBaseClasses class project, note that you can change this class name to a meaningful name for your projects e.g. BaseClassLibrary, BaseLibrary, TeamLibrary etc. When working with connection and command objects change the data provider from OleDb to SqlClient data provider. For command object parameters, if ? had been used for parameter names then use named parameters, for example for referencing FirstName change from ? to @FirstName. 

Microsoft SQL-Server has a very robust command set along with allowing multiple statements to be executed by one command object, for example, with MS-Access adding a new record required two executions of SQL queries while SQL-Server only requires one execution. Note also in regards to the data provider, SqlClient is used in place of OleDb and that the ConnectionString property of the Connection object is done exactly how all connections have been made throughout this article with MS-Access.

Public Function AddNewCustomer(ByVal pCompanyName As String, ByRef pNewIdentifier As Integer) As Boolean
 Using cn As New SqlConnection  With {.ConnectionString = ConnectionString}
  Using cmd As New SqlCommand  With {.Connection = cn}
 
   cmd.CommandText =  "INSERT INTO Customer (CompanyName) " &
        "VALUES (@CompanyName); SELECT CAST(scope_identity() AS int);"
 
   cmd.Parameters.AddWithValue("@CompanyName", pCompanyName)
   cn.Open()
   Try
    pNewIdentifier =  CInt(cmd.ExecuteScalar)
    Return True
   Catch ex As Exception
    Return False
   End Try
  End Using
 End Using
End Function

In conclusion, following these recommendations and patterns provides a solid foundation for working with MS-Access plus a solid path for upscaling to other supported databases by Visual Studio. If and when a decision is made to move to Entity Framework which does not expose connection and commands what has been taught here is still very useful as Entity Framework utilizes connections and commands under the covers.