Connecting to an Access database and retrieving values

When I first started programming one thing that I needed to do was to be able to connect to a Access database. Yes sure there are other free databases but I was experienced on that so it was a very easy decision to make.

BUT, for the life of me all the programming books that we were given at school, college etc (substitute with your tertiary institute of choice) were plain to say the list. The Internet wasn’t helping a lot to some point that I needed clarification. Some times it was taking me weeks to find something.

Other times the explanation and the ways to do it where, although the “proper ones” too difficult for me as a newbie to understand. (Yes thick as a brick, I know!)

And at the end I was able to put all the pieces together and I had a working model easy to implement. And very proudly I can presented it here for everyone wanting to copy it. I would like to thank all the people that posted messages in all the technical forums. This is my way to giving back. Thank you.

Reading some values from the database

Include needed:
Imports System
Imports System.Data
Imports System.Data.OleDb

Controls needed:
OleDbConnection1
OleDbCommand1

ConnectionString for OleBdConnection1:

Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Data Source="c:\databaseName.mdb";Mode=Share Deny None;Jet OLEDB:Engine Type=5;Provider="Microsoft.Jet.OLEDB.4.0";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1

C:\databaseName.mdb The name of the file containing the database.

The connection DOESN’T use any password protection or other security measures and everybody can write or delete from it.

Legend:
TblInfo: The name of the table in the database.
SQLString: The string that will contain the SQL command.

The following code takes data from a database with 6 columns (0 to 5) and reads ONLY the values 0 (column 1) and 5 (column 5), being the name of a user (the former) and the date of employment (the later).

Code

Dim dateOfEmpl As String
Dim userName, SQLString As String
Dim dateOfEmpl As Date

‘set up the connection data
Dim strConnection As String = OleDbConnection1.ConnectionString
Dim connect As New OleDbConnection(strConnection)
‘ Select ALL VALUES from the Database in the ConnectionString, from the table TblInfo and sort them out by Name in asceenting orderSQLString = "SELECT * FROM TblInfo ORDER BY Name ASC"
Dim cmd As New OleDbCommand(SQLString, connect)

‘open the connection
connect.Open()
Try
Dim reader As OleDbDataReader = cmd.ExecuteReader
‘read the data
Do While reader.Read()
userName = reader(0)
dateOfEmpl = reader(5)
Loop
'close the connection when there is nothing else to read reader.Close()
connect.Close()
Catch MyError As Exception
MsgBox(MyError.Message, MsgBoxStyle.Critical, "Error")
End Try

With this code it’s easy after to expand to further usage. For example you can add the data into a list box as follows:

While reader.Read()
ListBox1.Items.Add(reader.GetString(0))
ListBox1.Items.Add(reader.GetString(1))
ListBox1.Items.Add(reader.GetString(2))
ListBox1.Items.Add(reader.GetString(3))
ListBox1.Items.Add(reader.GetString(4))
ListBox1.Items.Add(reader.GetString(5))
ListBox1.Items.Add(reader.GetString(6))
ListBox1.Items.Add(reader.GetString(7))
End While

And that’s it. Hope you find it helpful.