Introduction

Working with Microsoft Access database is relatively easy when a developer understands options to interact with a database and has a decent understanding of writing code yet these easy methods that seemingly work can quickly cause a developer to encounter problems on every turn of developing a data-centric solution for their task. In this code sample/article methods which are also best practices will be introduced with the intent to allow a developer to write data-centric applications without problems that come from writing code without a clear understanding of how to interact with a Microsoft Access (MS-Access) database.

Before a Visual Studio solution is created first work out the database design, test the database to ensure all business requirements are met so that there is a clear path to follow rather than writing code and along the way end up struggling with bad database design. See the database design section below for ideas.


Series


Description

Novice level reading and searching

The following will lay the background to moving towards writing better code to access a database. In this case, the developer is novice level with the intent to start off by reading from an employee table and display the data into a DataGridView control with an option to find an employee by last name.

Calling out problems/issues

In the code sample below, data is read from the database in the load event of the form. The connection string is hard coded which means to deploy the application to another computer the path needs to be dynamic rather than hard code. Creating a dynamic connection will be shown later on. The second issue is there are several places were the same connection string is duplicated and lastly, the database is not protected with a password which may or may not be needed yet in many cases if someone comes along that find this application not doing what they want that user can circumvent your application and open the database, make changes and very possibly cause issues in your program e.g. cause orphan records and code to throw exceptions because the data in an unstable condition.

Since this is a read operation using a DataSet is overkill as only a DataTable is needed to read in data while depending on the application’s purpose may use a lighter weight container to populate the DataGridView control. Notice the SQL SELECT statement is in a string variable with the table name preceding field names, this usually happens when a developer writes the query in the database and paste the statement into their code.  In this case, the command text can be improved by removing the table name from each field name, if there was a JOIN then each table in the JOIN may be aliased which will be shown later.  

There is a try-catch statement with no logic in the catch which means any errors will go unnoticed. Couple this with using form load event which is known to allow exceptions to be thrown and not reporting these exceptions while the preferred event is the form’s Shown event plus having logic in the catch to deal with exceptions which should disallow the application to function normally plus alert the developer of issues using email or prompting the user to report the problem.   

Searching data

The developer copied code from the form load event and appended a WHERE condition in Button1 click event (all controls like buttons should have meaningful names so when there are many buttons, TextBox etc controls while in code a developer may known what a button does by its name).

Cardinal sin

Never append a concatenated WHERE condition to any query without using command parameters as done in Button1 click event to find a record, if there is a last name with an apostrophe in the field (and there is) an exception will be thrown from the database and caught in the catch. Using named parameters as shown in Button2 click event is how the WHERE condition should be constructed. For MS-Access, for each parameter you have a matching command parameter in the exact same order as in the field list e.g. WHERE ID=? AND Country = ? in the query create the parameters in the same order as MS-Access parameters are in ordinal position unlike SQL-Server where parameters are “named” parameters and for the record, Oracle uses named parameters but by default are done in ordinal positioning like MS-Access.

Code Sample

Overall review of the code sample below other than what is not mentioned above, the code to interact with a database is in the form, any code which reads and writes to the database should be in classes and called from the form.

Imports System.Data.OleDb
Public Class Form1
 Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
 
  Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\Database1.accdb"
 
  Using cn As New OleDbConnection(connectionString)
   Using cmd As New OleDbCommand With {.Connection = cn}
    cmd.CommandText = "SELECT Employees.EmployeeID, Employees.FirstName, " &
    "Employees.LastName, Employees.Title, Employees.HireDate FROM Employees;"
    Dim dt As New DataTable With {.TableName = "Employees"}
    Try
     cn.Open()
     Dim ds As New DataSet
     Dim employeeTable As New DataTable With {.TableName = "Employees"}
     ds.Tables.Add(employeeTable)
     ds.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges, employeeTable)
     DataGridView1.DataSource = ds.Tables("Employees")
     DataGridView1.Columns("EmployeeID").Visible = False
     Catch ex As Exception
     ' very common for a developer to simply ignore errors, unwise.
    End Try
   End Using
  End Using
 End Sub
''' <summary>
''' Common code to find a record but may not bite until down the road
 ''' An unescaped apostrophe will cause an exception to be thrown.
''' </summary>
 ''' <param name="sender"></param>
''' <param name="e"></param>
 Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
  Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\Database1.accdb"
 
  Using cn As New OleDbConnection(connectionString)
   Using cmd As New OleDbCommand With {.Connection = cn}
    cmd.CommandText = "SELECT Employees.EmployeeID, Employees.LastName, " &
     "Employees.FirstName, Employees.Title, Employees.TitleOfCourtesy, " &
     "Employees.BirthDate, Employees.HireDate FROM Employees WHERE (((Employees.LastName)='" &
     firstNameFindBadTextBox.Text & "'));"
 
    Dim dt As New DataTable With {.TableName = "Employees"}
    Try
     cn.Open()
     Dim ds As New DataSet
     Dim employeeTable As New DataTable With {.TableName = "Employees"}
     ds.Tables.Add(employeeTable)
     ds.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges, employeeTable)
     Catch ex As Exception
      MsgBox(ex.Message)
    End Try
   End Using
  End Using
End Sub
''' <summary>
''' Better but doing too much work
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
 
 Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\Database1.accdb"
 
 Using cn As New OleDbConnection(connectionString)
  Using cmd As New OleDbCommand With {.Connection = cn}
   cmd.CommandText = "SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName, " &
     "Employees.Title, Employees.TitleOfCourtesy, Employees.BirthDate, " &
     "Employees.HireDate FROM Employees WHERE (((Employees.LastName)=?));"
 
    cmd.Parameters.AddWithValue("?", employeeLastNameFindTextBox.Text)
    Dim dt As New DataTable With {.TableName = "Employees"}
    Try
     cn.Open()
     Dim ds As New DataSet
     Dim employeeTable As New DataTable With {.TableName = "Employees"}
     ds.Tables.Add(employeeTable)
     ds.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges, employeeTable)
     MessageBox.Show(ds.Tables(0).Rows.Count.ToString())
     Catch ex As Exception
      MsgBox(ex.Message)
    End Try
   End Using
  End Using
 End Sub
End Class

How to improve

  • Dependent on the project scope
    • If the project is for personal, home use create a class for accessing data which includes a common connection string and separate methods for interacting with data from reading, editing, adding, removing and searching methods. Any form in the project can call a method and the method(s) will respond no different from which form called it.
    • If the project is for multi-users for business or for selling then:
      • Create a class project that contains a connection class which is capable of dynamic connections, optionally shared connections, encryption of connection strings.
      • Interfaces for commonality of connections, conformity of common properties and methods for similar classes e.g. Customer and Contact class have similar navigation properties. Interfaces may come later for a developer who is just starting out yet when down later may end up taking more time to instrument.
      • Concrete classes for reference tables. Reference tables are read-only tables e.g. a list of country names, address types (business, home etc.)
      • Data classes for interacting with data where form or other classes call to interact with the database(s).

Suggested connection class

Even when working only with MS-Access there may be times when another database fits a project such as Microsoft SQL-Server, in this case using an interface to ensure any connection class has a ConnectionString.

Public Interface IConnection
 ReadOnly Property ConnectionString() As String
End Interface

Another advantage for ensuring both have a ConnectionString property is when a move is made from MS-Access to SQL-Server in regards to connections only the actual data provider changes.

AccessConnection also has a connection string for encrypted database password while SQL-Server does not, instead SQL-Server security is usually handled by active directory in tangent with properly setting up security in the database.

Considerations for login process

A login should at the very least provide an interface to permit user name and password to be entered and allow multiple login attempts.

Basic login projects

Additional options

  •  reveal password, provide a method to reset their password or contact someone who can reset the password.

Encryption of a database

  1. Open the selected database in exclusive mode.
  2. Under the File menu select "Encrypt with Password"
  3. Enter the password.
  4. Close the database and test with a connection string with the password.

If the database opens correctly with the password then continue with encrypting the connection string while if the connection fails open the database again.

  1. Under File, options, client settings.
  2. Scroll to the bottom
  3. Change default encryption to "use legacy encryption"
  4. Press OK
  5. Re-test, if there is an issue there is another known thing to try, shorten the password to under 14 in length.

Implementing secure connections

  1. Using ConfigurationLibrary in the supplied source, add this project to a Visual Studio solution followed by adding a new reference in your project to ConfigurationLibrary.
  2. Using the connection string created above, under project properties, settings.
  3. Create a new setting of type Connection String with a name of your choice.
  4. Create a private variable of type ConnectionProtection (as in first code block below)
  5. Encrypt (see second code block below) in your form or data class before using a connection and before deploying out in production.
  6. Access the encrypted connection string (see third code block below) followed by opening a connection.

 

Below is how the connection string is stored in app.config in plain text.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
 <configSections>
 </configSections>
 <connectionStrings>
 <add name="SimpleLoad_OleDb_MS_Access2007_1.My.MySettings.CustomersConnection"
 connectionString="Provider=Microsoft.ACE.OLEDB.12.0;
 Data Source=Database2.accdb;Jet OLEDB:Database Password=karen" />
 </connectionStrings>
 <startup>
 <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1"/>
 </startup>
</configuration>

Once encrypted.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
 <configSections>
 </configSections>
 <connectionStrings configProtectionProvider="DataProtectionConfigurationProvider">
 <EncryptedData>
 <CipherData>
 <CipherValue>AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAICYgSlAvJUGmh7ph3+NpwAQAAAACAAAAAAAQZgAAAAEAACAAAADM/e+cdSnTFLYNfn97rLnb5rRxufW+FqEMM4cfBzl50wAAAAAOgAAAAAIAACAAAABCJiJfbbn0eGZSWCtCagDbKbqbR2fQlUTEDOv6Il4hWvABAACM6Ap0CI8gVbZjppoZhsBwQJ43AmyDYQdr6Ro4H6nrLcdNdp8G/W8+1jRWLdVf2LAx8yBLEFA+b9JzbgWWa965FWEI2uH3ssd345JqKuZ4E0Ietn/+hhrUDpcoCMTBGP2twhG8KJK0fQlnjq4WNepFofx/9DAUtkxonIszAuh3mUedxSp0zEXwqeXeBuPWcU8sLIysXnso9l3AFZRBCpfse2hhhgWWkuZpmBv+Uni5At+/sZtojpGOB9cujiW2iRUy3afc4umFZZ5lf6rb0xBe+rur8jNRjR1PNx81PscCap+c5cFTHruazf7mwdjXGxW2H+aDWLnRSGeqynqjzIzRz4VGtYXVCjRDPLJxON27HPvK8MObxUSkTZjQvFbtjh2ZM6mf5JfsY54Iash0akphf/sU31+ITxJYBqMu+BuglpLpEsgMdnSBr8IC3BCwPceZ5uQCXz1B3jc8l7K/9nzSINVRWLo5m6VChwdbHm6I5K1S766FK3mq5GmPiY9vcvcFabC2xhwFIbINuMHr06pQ4N1GF8aLXjhdLBXENmhRsmeAkr06DcJO58r/AKyc1KsnTby45Fv1ydGZ5+uUmqq0a9kueIPYhqaslZzlh7hScGdysS5WJ8tS66kKv6mPJUOUT5y2AR3MF0GfPVaonxudQAAAAEoD1EKCe28h6fg3Pu11FyM3O10MrvY7BV4CZB0X8uvnOLf6MVdTeDcMnznqeHxnRK9jbhTFAd3JvWQ2DFKYu8o=</CipherValue>
 </CipherData>
 </EncryptedData>
 </connectionStrings>
 <startup>
 <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1"/>
 </startup>
</configuration>

Declare an instance of ConnectionProtection.

Private operations As New ConnectionProtection(Application.ExecutablePath)

Code to encrypt.

If Not operations.IsProtected() Then
 operations.EncryptFile()
End If

Access encrypted connection string.

operations.DecryptFile()
ops.ConnectionStringWithPassword = My.Settings.CustomersConnection
operations.EncryptFile()

Tip, create a test connection method as per below. This provides an isolated method to test the connection without actually reading data which is for development and test environments.

Public Function TestConnection() As Boolean
 mHasException = False
 
 Using cn As New OleDbConnection(ConnectionStringWithPassword)
 Try
 cn.Open()
 Return True
 Catch ex As Exception
 mHasException = True
 mLastException = ex
 End Try
 End Using
 
 Return IsSuccessFul
 
End Function

Note in the above code block mHasException and mLastException. Both are from a inherited class. 

Public MustInherit Class AccessConnection
 Inherits BaseExceptionProperties
 Implements IConnection

BaseExceptionProperties: by inheriting this class all exception handling will have a common method to capture exception information along with providing a way to determine if a method was successful or unsuccessful.

In the following code block, a DataTable is returned even if there is an exception thrown.

Public Function LoadCustomers() As DataTable
 
 Using cn As New OleDbConnection(ConnectionStringWithPassword)
  Using cmd As New OleDbCommand With {.Connection = cn}
   cmd.CommandText = <SQL>
   SELECT 
   Process,
   Identifier, 
   CompanyName, 
   ContactName, 
   ContactTitle
   FROM Customer 
   ORDER BY CompanyName;
   </SQL>.Value
 
  Dim dt As New DataTable With {.TableName = "Customer"}
 
  Try
  cn.Open()
  dt.Load(cmd.ExecuteReader)
  Catch ex As Exception
   mHasException = True
   mLastException = ex
  End Try
 
  Return dt
 
  End Using
 End Using
End Function

How exceptions are known is by checking IsSuccessful which checks to see if mLastException was set to an exception in the code block above. bsCustomers.DataSource will be set to either an empty DataTable if IsSuccessful is False or a populated DataTable is IsSuccessful is True. When reviewing BaseExceptionProperties class note may of the properties are read-only as to keep a property which sets or gets exception details not to be tainted by a developer setting a property that provides inaccurate results.

bsCustomers.DataSource = ops.LoadCustomers()
If ops.IsSuccessFul Then
 contactTitles = ops.LoadContactTitles()
 BindingNavigator1.BindingSource = bsCustomers
End If

OLEDB Provider is Not Registered on the Local Machine

In the event provider is not registered is thrown as an exception see the following. The common method to remedy this is by changing the CPU setting under Configuration Manager under Solution Explorer window.

Database design

When designing a database, there is a temptation to place related data into one table which in short will cause issues down the road. A good example, a customer table which has a contact first name, last name, and title. In the case, title should reside in a reference table with a foreign key back to the customer table. Suppose a title Assistant Manager changes to Assistant Sales Manager? Each record in the customer table now needs to be updated while using a reference table, make the change in the reference table and all records with Assistant Manager are now Assistant Sales Manager. In some cases, an argument might be made that history is needed. Not using a reference table means zero history while if a history is needed then a history table may be in order.

On the following page are examples for designing databases that can assist in getting started with designing your database.

Business requirements are the first place to begin and dictate the database design. Create base tables, child tables and from here determine where reference tables are needed and integrate these tables together with base tables.

Types of tables

  • Core table,
    • this is where all main data resides, without these there is no reason for a database in the first place.
  • Lookup table, 
    • Typically these categories represent some kind of finite scale. Examples include priority, severity, frequency, quality, etc.
  • List Table, 
    • This is another kind of "reference" table, similar to a Lookup table, but it contains a longer listing of records and new records will frequently be added. (Hence "List".) Examples include: Vendors, Shippers, Suppliers, Reasons, Purposes, Genres, etc
  • Detail Table,
    • A Detail table contains specific items that are subordinate to the records in a Core table. Examples include Order Details, Event Logs, Incident Reports, User Comments, and so forth.
  • Cross-Reference Table,
    • A Cross-Reference table exists only to create a many-to-many relationship between two other tables. Typically, these two other tables will be Core tables, but that is not a requirement.

Next steps after creating tables

With the database open, select database tools, relationships. Select tables followed by setting relationships.

Special note: In the design shown below there are "extra" fields e.g. ContactTitle and ContactTitleId. The original database used ContactTitle which is a poor design while using a reference table is much better. ContactTitle in a real application would be removed. There are other fields which could use attention yet this sample gets the point across.

Once this is done populate tables with realistic data then write queries to meet business requirements for your Microsoft Visual Studio solution. Once satisfied with these queries working integrate these queries into the data class making sure to use parameters rather than string concatenation. . A decent understanding of SQL syntax is recommended when designing the database as this provides a clearer image of what type of query will be needed for any given data operation.

 

Writing code for a basic read operation

. Place your MS-Access database in the root folder of the project. Under solution explorer select the database, select properties.  . Create a data class, named DatabaseOperations, add in the following import statements or allow Visual Studio to add them. Set the property "Copy to Output Directory" to "Copy always". When using "Copy always" on each build of the project the database overwrite the database in the Debug folder. If you want to keep changes then see the following MSDN code sample.
Imports System.Data.OleDb
Imports System.IO
Imports KarensBaseClasses

And the following inherits statement as shown below.

Public Class DatabaseOperations
 Inherits AccessConnection

Create a new constructor as shown below which sets the database for the underlying connection string in AccessConnection class.

Public Sub New()
 DefaultCatalog = Path.Combine(
 AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
End Sub

Open the MS-Access database, create a SELECT statement. In the following example, there are two related tables, customers and contact title. When creating the SELECT statement in the query designer select a table, select table properties and set the alias (this is an optional step) e.g. for Customers table the alias will be C, ContactTitle will be CT.

In the Customers table, there are two keys, the first is CustomerID which is what the original author of the table used and is not used anymore, the column has been left to show what not to use as a key while the field Identifier is an auto-incrementing key.

Copy the newly created SELECT statement into code (see below) while formatting the SELECT statement so it's easy to read..

Note the SQL is placed in an XML Literal construct so there is no string concatenation needed and if the query changes it's very simple to replace the statement or modify the statement.

Write code to read the data using a connection and command object which will use a DataTable to read data from the SELECT statement. Once the DataTable has been populated write code to hide the key fields using ColumnMapping = MappingType.Hidden.  

Completed class

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
 Public Function LoadCompanyNames() As List(Of String)
 Dim nameList As New List(Of String)
 Using cn As New OleDbConnection(ConnectionString)
 Console.WriteLine(ConnectionString)
 Using cmd As New OleDbCommand With {.Connection = cn}
 cmd.CommandText = "SELECT  CompanyName FROM Customers"
 cn.Open()
 Dim reader = cmd.ExecuteReader()
 While reader.Read()
 nameList.Add(reader.GetString(0))
 End While
 End Using
 End Using
 Return nameList
 End Function
 ''' <summary>
 ''' Read customers from database into a DataTable
 ''' </summary>
 ''' <returns>Populated DataTable of Customers</returns>
 ''' <remarks>
 ''' XML Literals allow a developer to write clean SQL with no string concatenation.
 ''' </remarks>
 Public Function LoadCustomers() As DataTable
 
 Using cn As New OleDbConnection(ConnectionString)
 Using cmd As New OleDbCommand With {.Connection = cn}
 cmd.CommandText = <SQL>
 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;
 </SQL>.Value
 
 Dim dt As New DataTable With {.TableName = "Customer"}
 
 Try
 cn.Open()
 dt.Load(cmd.ExecuteReader)
 
 '
 ' Hide primary keys
 '
 dt.Columns("Identifier").ColumnMapping = MappingType.Hidden
 dt.Columns("ContactTitleId").ColumnMapping = MappingType.Hidden
 
 Catch ex As Exception
 mHasException = True
 mLastException = ex
 End Try
 
 Return dt
 
 End Using
 End Using
 End Function
 
 Public Sub RemoveCustomer(customerKey As Integer)
 
 Using cn As New OleDbConnection(ConnectionString)
 Using cmd As New OleDbCommand With {.Connection = cn}
 cmd.CommandText = "DELETE FROM Customers WHERE Identifier = ?"
 cmd.Parameters.AddWithValue("?", customerKey)
 Try
 cn.Open()
 cmd.ExecuteNonQuery()
 Catch ex As Exception
 mHasException = True
 mLastException = ex
 End Try
 
 End Using
 End Using
 
 End Sub
End Class
 
 
 
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
 ''' <summary>
 ''' Read customers from database into a DataTable
 ''' </summary>
 ''' <returns>Populated DataTable of Customers</returns>
 ''' <remarks>
 ''' XML Literals allow a developer to write clean SQL with no string concatenation.
 ''' </remarks>
 Public Function LoadCustomers() As DataTable
 
 Using cn As New OleDbConnection(ConnectionString)
 Using cmd As New OleDbCommand With {.Connection = cn}
 cmd.CommandText = <SQL>
 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;
 </SQL>.Value
 
 Dim dt As New DataTable With {.TableName = "Customer"}
 
 Try
 cn.Open()
 dt.Load(cmd.ExecuteReader)
 
 '
 ' Hide primary keys
 '
 dt.Columns("Identifier").ColumnMapping = MappingType.Hidden
 dt.Columns("ContactTitleId").ColumnMapping = MappingType.Hidden
 
 Catch ex As Exception
 mHasException = True
 mLastException = ex
 End Try
 
 Return dt
 
 End Using
 End Using
 End Function
End Class

Place a DataGridView on the form. In the code editor select form events in the top of the editor.

In the combo box next to this find the Shown event, click to create. Finish up by using the code below to create an instance of the data class, create a BindingSource component. The BindingSource is not used here but will be discussed later. The BindingSource component will assist in many common operations from permitting navigation from row to row, obtaining current row data and much more.

Full code for the form.

Public Class Form1
 Private bsCustomers As New BindingSource
 Private Sub Form1_Shown(sender As Object, e As EventArgs) _
 Handles Me.Shown
 
 Dim ops As New DatabaseOperations
 
 bsCustomers.DataSource = ops.LoadCustomers
 DataGridView1.DataSource = bsCustomers
 
 End Sub
End Class

Press F5 to build the project which will display the data in the DataGridView. If the data is not displayed this indicates an exception in the load method. Change the above code to utilize the exception class.

Public Class Form1
 Private bsCustomers As New BindingSource
  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
End Class

The exception will be displayed in the MessageBox. There are other ways to learn what caused the exception yet in this example keep with the base exception class. 

Note that the header text for each DataGridView column is the name of the field from the SELECT statement. To change this either set each column's text as per below.

If ops.IsSuccessFul Then
 bsCustomers.DataSource = ops.LoadCustomers
 DataGridView1.DataSource = bsCustomers
 
 DataGridView1.Columns("CompanyName").HeaderText = "Company"
 
Else
 MessageBox.Show(ops.LastExceptionMessage)
End If

Or create a column in the designer for each field/column. Set the header text,  set the property DataPropertyName to the field from the SELECT. Set the DataGridView to not auto generate columns.

Private Sub Form1_Shown(sender As Object, e As EventArgs) _
 Handles Me.Shown
 
 DataGridView1.AutoGenerateColumns = False

The source code for this code sample is located here on GitHub

Caveats

  • A BindingSource can also be added to a form by selecting this component from the IDE toolbox by double-clicking the component which places a new instance on the form.
  • A BindingNavigator control can be placed on the form and associated with the BindingSource to provide additional functionality for navigation and add, remove and edit records which will be covered also in the next code sample.

Continue by double-clicking a BindingNavigator from the IDE toolbox. The BindingNavigator by default docks to the form top region.

Add a line, BindingNavigator1.BindingSource = bsCustomers

Public Class Form1
 Private bsCustomers As New BindingSource
  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
   BindingNavigator1.BindingSource = bsCustomers
  Else
   MessageBox.Show(ops.LastExceptionMessage)
  End If
 
 End Sub
End Class

Press F5 to run, note the navigation buttons are active except the first as we are on the first record.

By pressing button buttons this moves the current row in the DataGridView. Press the plus button moves to a new row in the DataGridView while pressing the delete button deletes the current row. None of the actions (add or remove) are sent to the database table as the DataTable which loaded the table is detached from the database.

Removing records should have a prompt, ask the user if they really want to remove the record.

Add the following code module (GitHub source) to the project.

Namespace My
 
 <ComponentModel.EditorBrowsable(ComponentModel.EditorBrowsableState.Never)>
 Partial Friend Class _Dialogs
 ''' <summary>
 ''' Ask question with NO as the default button
 ''' </summary>
 ''' <param name="pQuestionText">Text for asking a question</param>
 ''' <returns>
 ''' True if yes button pressed, False if no button was selected 
 ''' or ESC pressed</returns>
 ''' <remarks></remarks>
  Public Function Question(pQuestionText As String) As Boolean
 
   Return (MessageBox.Show(
    pQuestionText,
    My.Application.Info.Title,
    MessageBoxButtons.YesNo,
    MessageBoxIcon.Question,
    MessageBoxDefaultButton.Button2) = MsgBoxResult.Yes)
 
  End Function
 End Class
 
 <HideModuleName()>
 Friend Module KarensDialogs
  Private instance As New ThreadSafeObjectProvider(Of _Dialogs)
   ReadOnly Property Dialogs() As _Dialogs
    Get
     Return instance.GetInstance()
    End Get
  End Property
 End Module
End Namespace

Select the BindingNavigator, select properties and change DeleteItem to none.

Add the following code module.

''' <summary>
''' Helper extensions for BindingSource component.
''' </summary>
Public Module BindingSourceExtensions
 ''' <summary>
 ''' Return underlying DataTable
 ''' </summary>
 ''' <param name="sender">BindingSource with a DataTable as it's DataSource</param>
 ''' <returns>DataTable</returns>
 <Runtime.CompilerServices.Extension>
 Public Function DataTable(sender As BindingSource) As DataTable
  Return CType(sender.DataSource, DataTable)
 End Function
 ''' <summary>
 ''' Current current DataRow
 ''' </summary>
 ''' <param name="sender">BindingSource with a DataTable as it's DataSource</param>
 ''' <returns>DataRow</returns>
 <Runtime.CompilerServices.Extension()>
 Public Function CurrentRow(sender As BindingSource) As DataRow
  Return CType(sender.Current, DataRowView).Row
 End Function
End Module

Select the BindingNavigator, double click the delete button and add the following.

Private Sub BindingNavigatorDeleteItem_Click(sender As Object, e As EventArgs) _
 Handles BindingNavigatorDeleteItem.Click
 
 If My.Dialogs.Question($"Remove '{bsCustomers.CurrentRow().Field(Of String)("CompanyName")}'?") Then
 
 End If
End Sub

Pressing delete button prompts to remove the current row with No as the default button.

Add the following method to the data class.

Public Sub RemoveCustomer(customerKey As Integer)
 
 Using cn As New OleDbConnection(ConnectionString)
  Using cmd As New OleDbCommand With {.Connection = cn}
   cmd.CommandText = "DELETE FROM Customers WHERE Identifier = ?"
   cmd.Parameters.AddWithValue("?", customerKey)
   Try
    cn.Open()
    cmd.ExecuteNonQuery()
    Catch ex As Exception
    mHasException = True
    mLastException = ex
   End Try
 
  End Using
 End Using
 
End Sub

Update the delete button code to call the remove method above.

Private Sub BindingNavigatorDeleteItem_Click(sender As Object, e As EventArgs) _
 Handles BindingNavigatorDeleteItem.Click
  
 If My.Dialogs.Question($"Remove '{bsCustomers.CurrentRow().Field(Of String)("CompanyName")}'?") Then
  Dim ops As New DatabaseOperations
  ops.RemoveCustomer(bsCustomers.CurrentRow().Field(Of Integer)("Identifier"))
 End If
End Sub

Press F5 to run, select a record, press delete button and then press Yes. In this case, the delete will fail as the Customer record has child records in the Order table.

Exception message:

The record cannot be deleted or changed because the table 'Orders' includes related records.

To fix this, open the database in the project folder. Select database tools, relationships. Select the line between Customers and Orders table, right-click. edit relations. Find the checkbox "Cascade Delete related records" and check it. Press OK, save and try the operation again. This time the record will be removed and all child order records. If the Orders table had child records this process must be done on the child records too.

There is one other method for removing a record, single click a row by single clicking the row header and pressing delete on the DataGridView. This is covered in the following code sample included starting on line 137. The same dialog ask if the current record should be removed. Note in this class the delete operation is more robust than the one presented above.

See also

References

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


Summary

In the code sample basics have been covered to design a database, connection and read data using base classes, working with a BindingSource and BindingNavigator. In the next part continuation of CRUD will be discussed, working with transactions, basics for master-detail databases, reference tables, working with data readers and reading/writing binary data. Included in part 3 is upscaling to SQL-Server which may be a future consideration as a database grows.

Source code