HOW TO CONNECT TO AN ACCESS (*.mdb OR *.accdb DATABASE IN VB.NET

Connecting to a database in .net could be a bit tricky to programmers of vb 6.0 and vb(a) who are already used to recordsets and the JET engine.

However, it is quite simple if you keep the following concepts in mind.

Data Connection
This provides a connection to your database. For the purpose of our example, we would use an oledb provider. Declare a variable as part of the oledb.oledbconnection method as follows:

Dim DBConn As New OleDb.OleDbConnection

Where DBConn is our connection variable.
We can now assign a connection string value to our connection object by:

                .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                                            My.Application.Info.DirectoryPath & _
                                           "\Database\test1.accdb;Persist Security  Info=False;"
                .Open()

Assuming our database is named test1.accdb and is located in a folder named database in the same folder as the application is located.


Command Object
This object is used to store SQL commands needed to specify requests to our database. It is declared as

Dim cmdSQL As New OleDb.OleDbCommand

where cmdSQL is any variable of your choice. The three main properties to specify in an Oledb Command object is its connection, command type and command text.

                                     cmdSQL.Connection = DBConn
                                     cmdSQL.CommandText = "SELECT * FROM YourTable"
                                     cmdSQL.CommandType = CommandType.Text

Data Adapter
This serves as a go-between to your dataset and connection string. it is declared similar to your connection object as

 Dim DatAdapter As New OleDb.OleDbDataAdapter(cmdSQL)

DataSet
This is similar to a recordset and is an in-memory representation of the requested table from the database.
it is declared as:

Dim DatSet As New DataSet 

The dataset is filled using the fill method of the data adapter by

DatAdapter .Fill(DatSet)

so that the whole code look like this:


    Public Sub OpenDbConn()

    Dim DBConn As New OleDb.OleDbConnection
    Dim cmdSQL As New OleDb.OleDbCommand
    Dim DatAdapter As New OleDb.OleDbDataAdapter(cmdSQL)
    Dim DatSet As New DataSet

        With DBConn
                .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                        My.Application.Info.DirectoryPath & "\Database\test1.accdb;Persist Security Info=False;"
                .Open()
        End With

        If DBConn.State = ConnectionState.Open Then

            With cmdSQL
                .Connection = DBConn
                .CommandText = "SELECT * FROM YourTable"
                .CommandType = CommandType.Text
            End With

            With DatAdapter
                .Fill(DatSet)
            End With

        End If
    End Sub