MS-Access with VB.NET: Best practices (Part 1)
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.
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
- 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.
See also
Database selective connection strings
Move Access Data to the Cloud
Locate and Recover Corrupt Access Database Records
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
.