Mini Project in VB.NET- Microsoft Access Database Connectivity

This Mini Project in VB.NET- Microsoft Access Database Connectivity will help you to understand the Database Connectivity. It uses DataReader, DataAdapter and Dataset for viewing and manipulating the data. We will create a form that accepts four values from user about a student- RollNo, Name, Class and Date of birth. The controls and the names given to the controls is shown in the below image.

Mini Project VB.NET Entry Form

Steps to create Mini Project in VB.NET- Microsoft Access Database Connectivity

Step 1- Create Database in Microsoft Access

Before designing and coding in VB.NET we will first create a database in Microsoft Access. The name given to database is StudentDB(with .accdb as extension). If you are using older version of Microsoft Access then the database file will have .mdb extension. In this case you will need the “Mircosoft.Jet.OLEDB.4.0” provider in place of “Microsoft.ACE.OLEDB.12.0”. The database we created contains one table with name tblStudent. The structure of the table is as given in the diagram

Mini Project VB.NET Table structure

Once you create the database and the table, copy StudentDB.accdb (StudentDB.mdb in case of older version of Microsoft Access) file to this location in your solution directory “bin\Debug”. In this example the folder is “F:\DotNet\project\basicApp\basicApp\bin\Debug”. When you do this you don’t have to give the absolute address of database file in the connections string.

Step 2- Declare a global oledeb connection object and create connection in module

The next step to  create Mini Project in VB.NET- Microsoft Access Database Connectivity is to make the connection with this database in your VB.NET application code. We will write this code in a module. A module is a project level file in .NET that doesn’t have any interface. It is used to declare the project level variables, constants, procedure and functions. It will help in accessing the common variables, constants, procedure and functions in any form or class of the current project.

Here we will declare a “oledb.connection” object. Then create a procedure to connect with our database stored in the \bin\Debug folder of our application (represented in the code with Application.StartupPath) The connection object conDB  and procedure connectDB are declared with Public modifier in the module file name Module1 so that they are global within the project.

 Module Module1
    Public conDB As New OleDb.OleDbConnection

    Public Sub connectDB()
        If conDB.State = ConnectionState.Closed Then
            conDB.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Application.StartupPath & "\studentDB.accdb"
            conDB.Open()
        End If
    End Sub

End Module

Step 3- Design Data Entry Form and check for connection

Once you create this module with the connection object and connectDB procedure, next step is to design the entry form.  We can check for database connectivity by running the form. Create the form as shown in the diagram by placing the needed controls and giving the name to the controls in the property window.

Now open the form_load event of the form and write this code and run the application.

 Private Sub Form2_Load(sender As Object, e As EventArgs) Handles Me.Load
        Module1.connectDB()
        Rowno=0   ‘initialization of counter to indicate current record
End Sub

Step 4- Add code to the form

First we need to declare global objects and variables in the form code (at top just after Public Class Classname)

 Dim stuReader As OleDb.OleDbDataReader             ‘an OLEDB data reader object
Dim stuCommand As New OleDb.OleDbCommand            ‘an OLEDB command object
Dim stuDataAdaptor As New OleDb.OleDbDataAdapter    ‘an OLEDB dataadapter object
Dim stuDS As DataSet                                ‘ a dataset
Dim rowno As Integer                                ‘ variable to store current record number being accessed from tblStudent through recordset

Next we will add code for different actions of the form.

btnShow– This button is added to fill the Listbox1 with the names and rollnos of the students whose data is stored in the table tblStudent.

This is the code

 Private Sub btnShow_Click(sender As Object, e As EventArgs) Handles btnShow.Click
        ListBox1.Items.Clear()
        stuCommand = Module1.conDB.CreateCommand()
        stuCommand.CommandText = "select rollno,Sname from tblstudent"
        stuReader = stuCommand.ExecuteReader
        While stuReader.Read
            ListBox1.Items.Add(stuReader.Item("rollno").ToString.PadRight(10) & " | " & stuReader.Item("Sname"))
        End While
    End Sub

Now the next code is for saving the data entered in the table tblStudent

 Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        Dim strSQL As String
        strSQL = "Insert into tblSTudent values(" & CInt(txtRollNo.Text) & ",'" _
                            & txtSName.Text & "','" _
                            & cboClass.Text & "',#" _
                            & dtpDOB.Value & "#)"
        stuCommand = New OleDb.OleDbCommand(strSQL, conDB)
        stuCommand.ExecuteNonQuery()
End Sub

The next task is to write code for the Next(>), Previous(<), First(<<) and Last(>>) buttons. This is done with two user defined procedure getData and showData and four button click events. getData is used to fetch the data from tblSudent into dataset through adapter. showData procedure accepts an integer value as argument that indicates which record from the recordset is to be displayed in the form.

 Private Sub getdata()
        stuDataAdaptor = New OleDb.OleDbDataAdapter("select * from tblStudent", conDB)
        stuDS = New DataSet
        stuDataAdaptor.Fill(stuDS, "tblStudent")
        reccount = stuDS.Tables(0).Rows.Count
    End Sub
Private Sub showdata(i As Integer)
        txtRollNo.Text = stuDS.Tables("tblStudent").Rows(i).Item(0)
        txtSName.Text = stuDS.Tables("tblStudent").Rows(i).Item(1)
        cboClass.Text = stuDS.Tables("tblStudent").Rows(i).Item(2)
        dtpDOB.Value = stuDS.Tables("tblStudent").Rows(i).Item(3)
    End Sub

Now the code for browse buttons by calling the above two procedures

 Private Sub btnFirst_Click(sender As Object, e As EventArgs) Handles btnFirst.Click
        getdata()
        showdata(0)
    End Sub

    Private Sub btnLast_Click(sender As Object, e As EventArgs) Handles btnLast.Click
        getdata()
        showdata(reccount - 1)
    End Sub

    Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click
        rowno = rowno + 1
        If rowno >= reccount - 1 Then
            rowno = reccount - 1
            btnLast_Click(sender, e)
        Else
            showdata(rowno)

        End If
    End Sub

    Private Sub btnPrev_Click(sender As Object, e As EventArgs) Handles btnPrev.Click
        rowno = rowno - 1
        If rowno <= -1 Then
            rowno = 0
            btnFirst_Click(sender, e)
        Else
            showdata(rowno)
        End If
    End Sub

Last action is to update the displayed record in the form so that the updated values are stored in the table. The code is written in the btnUpdate_Click event handler

 Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        Dim strSQL As String
        strSQL = "update  tblSTudent set Sname='" _
                            & txtSName.Text & "',SClass='" _
                            & cboClass.Text & "',Sdob=#" _
                            & dtpDOB.Value & "# where rollno=" & CInt(txtRollNo.Text)
        stuCommand = New OleDb.OleDbCommand(strSQL, conDB)
        stuCommand.ExecuteNonQuery()
 End Sub

Final Words

This is a very basic Mini Project in VB.NET- Microsoft Access Database Connectivity to make the readers understand the concepts of database connectivity and data manipulation.