Program Demonstrating MS Access Database Manipulation

seancampbellseancampbell Pennsylvania, USA
The code in this post details functions that will connect to an Access Database, allow you to display the data from it, add a new record, edit an existing record, and delete a record from a Table. The code is commented in such a way as to be informative and help you learn from it.

To use the code, set up a new form with the following objects on it (attached is a screenshot of the form after I configured it)
lvData as ListView
lvData.View = Details
lvData.MultiSelect = False
lvData.HideSelection = False
lvData.GridLines = True
lvData.FullRowSelect = True

btnOpen as Button
btnRefresh as Button
btnAdd as Button
btnEdit as Button
btnDelete as Button
btnSave as Button
btnCancel as Button

lblID as Label
txtFName as TextBox
txtLName as TextBox
txtAddress as TextBox
txtCity as TextBox
txtState as TextBox
txtZip as TextBox

(I also have 7 unnamed labels that are used to describe the textboxes and lblID visually)
Created an MS Acccess database with the following Table Definition
MS Access Database

TABLE Customer
ID as AutoNumber (PrimaryKey)
FName as Text
LName as Text
Address as Text
City as Text
State as Text (2)
Zip as Text (5)
and finally the following code was added to the Form. Since I used a Default form name, the definition starts with Class Form1, this should be "Class YourFormNameHere"
Imports System.Data.OleDb

Public Class Form1

Dim Con As System.Data.OleDb.OleDbConnection
Dim DBName As String = "Cust.mdb"
Dim EDITMODE As Boolean = False
Dim NEWMODE As Boolean = False

Private Sub Form38_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Con = New OleDbConnection
'If you click refresh without clicking Open first
'the above line will ensure there is no Nullreferenceerror on the Finally clause
'in the Try Catch
End Sub

Private Sub btnOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpen.Click
Dim FO As OpenFileDialog
FO = New OpenFileDialog
FO.InitialDirectory = Application.StartupPath
If FO.ShowDialog() = Windows.Forms.DialogResult.OK Then
DBName = FO.FileName
Else 'Mustve clicked Cancel, exit
End If

'Set the Connection String of the Connection Object.
'I found this connection string online, it seems to be the standard
'for connecting to MS Access databases. If you use a Username and
'password for your database, then you will need to use the correct
'ones on the end of this connection string

Con = New OleDbConnection
Con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBName & ";User ID=Admin;Password="
'Error handling keeps our software from crashing
'when an error occurs
Catch ex As Exception
'You could opt to do something here
'This code is called when an error occurs
'I usually do a MsgBox
MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Connection Error")
End Try

If Con.State = ConnectionState.Open Then
'It openned
btnOpen.BackColor = Color.Green
'It didn't open
btnOpen.BackColor = Color.Red
End If

Con.Close() 'We don't want to keep the database connection
'open constantly. In an access world it isn't as bad because the
'database typically resides on you local machine
'but if it were on a server, a constant connection would slow
'down the network significatnly
End Sub

Private Sub btnRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRefresh.Click
End Sub

Sub RefreshLV()

Dim SQL As String
SQL = "SELECT * FROM Customer" 'This statement grabs all records from the table

Dim DS As DataSet 'Object to store data in
DS = New DataSet 'Declare a new instance, or we get Null Reference Error

Con.Open() 'Open connection

Dim oData As OleDbDataAdapter
oData = New OleDbDataAdapter(SQL, Con)

'DS should contain our records, lets parse it and add them to the
'listview. Remember that DS.Tables will contain all tables in our query
'Since we only asked for 1 table of data, our data is located in
'DS.Tables(0) (which is a DataTable Object)
'If you prefered you could do:
'Dim DT As New System.Data.DataTable
'DT = DS.Tables(0)
'And work off of DT, but it doesn't gain you much if your not working
'with hundreds of thousands of records...

lvData.Items.Clear() 'prep Listview by clearing it
lvData.Columns.Clear() 'remove columns in LV

'create columns on listview
For i As Integer = 0 To DS.Tables(0).Columns.Count - 1
lvData.Columns.Add(DS.Tables(0).Columns(i).Caption, 75, HorizontalAlignment.Left)

'Parse and add data to the listview
For i As Integer = 0 To DS.Tables(0).Rows.Count - 1
'We use a ListViewItem to add items to a ListView
'When you declare a ListViewItem (as follows)
'Dim xItem as ListViewItem
'you have to set it equal to a NEW instance of the object
'or you will get a null reference error
'The first String you pass it goes in the first column
'Each subitem there after will go in the next column

'I consolidate these two lines into one line in the following code...
'Dim xItem As ListViewItem
'xItem = New ListViewItem(DS.Tables(0).Rows(i)("ID").ToString)

Dim xItem As New ListViewItem(DS.Tables(0).Rows(i)("ID").ToString)


Catch ex As Exception

'This code gets called regardless of there being errors
'This ensures that you close the Database and avoid corrupted data
End Try
End Sub

Sub ClearTextBoxes()
lblID.Text = ""
txtFName.Text = ""
txtLName.Text = ""
txtAddress.Text = ""
txtCity.Text = ""
txtState.Text = ""
txtZip.Text = ""
End Sub

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
'This function will prep the TextBoxes for entering New data
'This function will set EDITMODE to false incase we were in EDITMODE
'This function will set NEWMODE to True and make the lblID say "NEW RECORD"
lblID.Text = "NEW RECORD"
End Sub

Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
'Edit will require somewhere for us to enter in new values, for this I added
'some objects to the form, txtFName, txtLName, txtAddress, txtCity, txtState, txtZip, lblID
'Since we cannot change the ID, and this Primary key record is needed to update
'a row in the database, we will just use a Label to display it (lblID)
'Check if there is a row selected on the ListView
If lvData.SelectedIndices.Count > 0 Then
'ListViews have the capability to select multiple lines
'Since we want to edit just one record at a time, we have to change
'the MultiSelect option on the listview to False

'Since we know that there will only be one selected record anytime,
'we can directly reference the first selected row always.

Dim ItemNo As Integer = lvData.SelectedIndices(0) 'Grab the selected Index

'We have to set these global varialbes
'to tell our Save button what it is doing
'Look under btnSave_Click to see where these variables get used

lblID.Text = lvData.Items(ItemNo).SubItems(0).Text
txtFName.Text = lvData.Items(ItemNo).SubItems(1).Text
txtLName.Text = lvData.Items(ItemNo).SubItems(2).Text
txtAddress.Text = lvData.Items(ItemNo).SubItems(3).Text
txtCity.Text = lvData.Items(ItemNo).SubItems(4).Text
txtState.Text = lvData.Items(ItemNo).SubItems(5).Text
txtZip.Text = lvData.Items(ItemNo).SubItems(6).Text
'I wrote the following subroutine to clear the textboxes
'and lblID to make this process look cleaner, since I will do this
'in a few other places.
'It's best practice to make subroutines out of lines of code that get repeated
'in multiple places.
End If
End Sub

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
If lvData.SelectedIndices.Count <= 0 Then
Return 'No selected Item so exit
End If

Dim ItemNo As Integer = lvData.SelectedIndices(0) 'Grab the selected Index
'Like the UPDATE command, we are going to use
'OleDbCommand to execute a non-query and delete our record with WHERE parameters
'In this case, I won't build the parameters, but instead just include them in the
'SQL statement

Dim I As Integer = MsgBox("Are you sure you want to delete this record?", MsgBoxStyle.YesNo, "Are you sure?")
If I = MsgBoxResult.Yes Then

'DELETE FROM TableName WHERE PrimaryKey = ID
Dim cmd2 As New OleDb.OleDbCommand("DELETE FROM Customer WHERE ID = " & lvData.Items(ItemNo).SubItems(0).Text, Con)


MsgBox("Record Removed Successfully")

'They didn't really want to delete, so exit
Return 'This exits the sub
End If

Catch ex As Exception
End Try
End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
If EDITMODE = True Then
'So the EDITMODE Boolean has been set, and we know that we will be updating
'a record in our database. Since there is a chance that the user selected a different
'record in the ListView, we will use lblID to grab the ID of the record that
'we are updating

'We have a CON object declared globally, so I will use that

Dim ID As String = Trim(lblID.Text) 'Trim takes any spaces off the left or right sides of a string, probably not needed

'This connection string is the same as the one we just used with btnOpen
' (recycles the DBName var that we have declared globally)

'Here we are going to build a SQL statement that we pass parameters with
'the OleDbCommand object, which is used to Execute NonQuery based commands
'such as INSERT (new row) DELETE (delete row) or DROP TABLE (deletes a table from the db)
'Here we are using the UPDATE command which is used to update an existing record or records based on WHERE criteria

'UPDATE TableName SET Field=@a0, Field=@a1 WHERE PrimaryKey = @a2
'the @a0 represents a Parameter that will be set with the OleDbCommand object
Dim cmd2 As New OleDb.OleDbCommand("UPDATE Customer SET FName=@a0, LName=@a1, Address=@a2, City=@a3, State=@a4, Zip=@a5 WHERE (ID=@a6) ", Con)

cmd2.Parameters.AddWithValue("@a0", txtFName.Text)
cmd2.Parameters.AddWithValue("@a1", txtLName.Text)
cmd2.Parameters.AddWithValue("@a2", txtAddress.Text)
cmd2.Parameters.AddWithValue("@a3", txtCity.Text)
cmd2.Parameters.AddWithValue("@a4", txtState.Text)
cmd2.Parameters.AddWithValue("@a5", txtZip.Text)
cmd2.Parameters.AddWithValue("@a6", ID)


MsgBox("Record Updated Successfully")
EDITMODE = False ' Add success, end EDITMODE
ClearTextBoxes() 'Add Success, clean up textboxes
Catch ex As Exception
End Try
ElseIf NEWMODE = True Then

Dim oAdapter As OleDb.OleDbDataAdapter
Dim cb As OleDb.OleDbCommandBuilder
Dim dr As DataRow
Dim ds As DataSet
Dim strSQL As String = "SELECT * FROM Customer"

ds = New DataSet()
oAdapter = New OleDb.OleDbDataAdapter(strSQL, Con)
oAdapter.Fill(ds) 'Execute the Query and grab results

dr = ds.Tables(0).NewRow()

dr("FName") = txtFName.Text
dr("LName") = txtLName.Text
dr("Address") = txtAddress.Text
dr("City") = txtCity.Text
dr("Zip") = txtZip.Text
dr("State") = txtState.Text


cb = New OleDb.OleDbCommandBuilder(oAdapter)
oAdapter.InsertCommand = cb.GetInsertCommand

MessageBox.Show("Insert Successful")
NEWMODE = False 'Add success, end NEWMODE
ClearTextBoxes() 'Add Success, clean up textboxes
Catch ex As Exception

End Try

Else 'Its not Editmode or Newmode
'Do nothing
End If

RefreshLV() 'Regardless of what happens, might as well refresh the data in the Listview

End Sub

Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
'Pretty straight forward
End Sub

End Class

Hope you are able to learn from this, send me a private message if you would like to ask questions regarding this code.

Happy coding, Sean Campbell
This discussion has been closed.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!