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

Article
1/17/2024

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 a bad database design. See the database design section below for ideas.

Part 2 of this series.
Part 3 of the 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 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 button, TextBox etc controls while in code a developer may known what a button does by it’s 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.

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 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, conformaty 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. list of country names, address types (business, home etc.)
  - Data classes for interacting with data where form or other classes call to interact with 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.

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

Front end.
Back end.

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

Encryption of a database

Open the selected database in exclusive mode.
Under the File menu select "Encrypt with Password"
Enter the password.
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.

Under File, options, client settings.
Scroll to the bottom
Change default encryption to "use legacy encryption"
Press OK
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

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.
Using the connection string created above, under project properties, settings.
Create a new setting of type Connection String with a name of your choice.
Create a private variable of type ConnectionProtection (as in first code block below)
Encrypt (see second code block below) in your form or data class before using a connection and before deploying out in production.
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=" /> 
    </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 deterime 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 simply 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 form 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 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 then the one presented above.

References

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

Unit testing data operations for Access
Reading and writing images in Access table
Basics for master-detail DataGridView controls with Access
Populate TextBox and ComboBox using autocomplete for Access tables
BindingSource filter/find with Access tables
DataGridViewCheckBox with Access
Moving rows up/down in DataGridView and ListBox for Access
Export Access table to Microsoft Excel
Export Microsoft sheets to Access database
Retrieve schema from an Access database
Creating Access databases at runtime

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

Github repository.

Share via

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

Introduction

Description

Novice level reading and searching

Calling out problems/issues

Searching data

How to improve

Suggested connection class

OLEDB Provider is Not Registered on the Local Machine

Database design

Writing code for a basic read operation

See also

References

Summary

Source code

Additional resources

Share via

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

Introduction

Description

Novice level reading and searching

Calling out problems/issues

Searching data

How to improve

Suggested connection class

Considerations for login process

OLEDB Provider is Not Registered on the Local Machine

Database design

Writing code for a basic read operation

See also

References

Summary

Source code

Additional resources