MINNING ACCESS DATABASES WITH VB.NET


connecting to an access database:

1 File > New Project from the menu bar, Windows Application
2 View > Solution Explorer from the menu bar, click on Data Sources, choose data source, next
version 2010 of VBN NET select dataset, next
3 New Connection, Change button, Select Microsoft Access Database File, ok
4 Click the Browse button and navigate to where on your computer you downloaded our Access Database,
Click Test Connection to see if everything is OK
5 +, save connection string in a txt file
6 Click No on the message box to stop VB copying the database each time it runs
7 from data sources you can drag and drop tables or fields to the form to navigate and edit(to edit before
draging click the arrow near the table and choose detail view)

tip: When all Textbox are selected, click on the Format menu at the top
From the Format menu select Align > Lefts. The left edges of the Textboxes will align themselves
From the Format menu select Vertical Spacing > Make Equal. The space between each textbox will then be
the same .

codes:
OLE stands for Object Linking and Embedding

in a sub:
dim con As new OleDb.OleDbConnection
dim dbProvider,dbSource As string
dim ds As new DataSet
dim da As OleDb.OleDbDataAdapter
dim sql As string

dbProvider = "PROVIDER=Microsoft.jet.OLEDB.4.0;"
dbSource = "Data Source = E:/AddressBook.mdb" ' path of access database

con.ConnectionString = bProvider & dbSource

con.open()

sql = "SELECT * FROM tblContacts" 'sql command, dataset contains table tblContacts
da = new OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "AddressBook") ' dataset filled


con.close()

txtFirstName.Text = ds.Tables("AddressBook").Rows(0).Item(1)' getting data from ds
txtSurname.Text = ds.Tables("AddressBook").Rows(0).Item(2)
'or
'txtFirstName.Text = ds.Tables("whatever").Rows(0).Item(1)
'txtSurname.Text = ds.Tables("whatever").Rows(0).Item(2)

' MaxRows = ds.Tables("AddressBook").Rows.Count

end sub

Updating a Record:

Dim cb As New OleDb.OleDbCommandBuilder(da)

ds.Tables("AddressBook").Rows(inc).Item(1) = txtFirstName.Text
ds.Tables("AddressBook").Rows(inc).Item(2) = txtSurname.Text

da.Update(ds, "AddressBook")

MsgBox("Data updated")

Add a New Record

Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim dsNewRow As DataRow

dsNewRow = ds.Tables("AddressBook").NewRow()

dsNewRow.Item("FirstName") = txtFirstName.Text
dsNewRow.Item("Surname") = txtSurname.Text

ds.Tables("AddressBook").Rows.Add(dsNewRow)

da.Update(ds, "AddressBook")

MsgBox("New Record added to the Database")

Deleting Records from a Database:

Dim cb As New OleDb.OleDbCommandBuilder(da)

ds.Tables("AddressBook").Rows(inc).Delete() ' inc is an integer variable

da.Update(ds, "AddressBook")


tip: getting a folder path:
Dim fldr As String
fldr = Environment.GetFolderPath( Environment.SpecialFolder.MyDocuments ) & "/AddressBook.mdb"