Write your own Database code in VB .NET

In this next section, we’ll take a look at the objects that you can use to
open and read data from a Database. We’ll stick with our Access database, the
AddressBook.mdb one, and recreate what the Wizard has done. That way, you’ll
see for yourself just what is going on behind the scenes.

So close any open projects, and create a new one. Give it whatever name you
like, and let’s begin.

If you haven’t yet downloaded the Address Book database, you can get it here:

The Connection Object

The Connection Object is what you need if you want to connect to a database.
There are a number of different connection objects, and the one you use depends
largely on the type of database you’re connecting to. Because we’re connecting
to an Access database, we’ll need something called the OLE DB connection object.

OLE stands for Object Linking and Embedding, and its basically a lot of objects
(COM objects) bundled together that allow you to connect to data sources in
general, and not just databases. You can use it, for example, to connect to
text files, SQL Server, email, and a whole lot more.

There are a number of different OLE DB objects (called data providers), but
the one we’ll use is called “Jet“. Others are SQL Server and
Oracle.

So place a button on your form. Change the Name property to btnLoad.
Double click your button to open up the code window. Add the following line:

Dim con As New OleDb.OleDbConnection

The variable con will now hold the Connection Object. Notice
that there is a full stop after the OleDB part. You’ll then get a pop up box
from where you can select OleDbConnection. We’re also creating a New
object on this line.This is the object that you use to connect to an Access
database.

Setting a Connection String

There are Properties and Methods associated with the Connection Object, of
course. We want to start with the ConnectionString property. This can take MANY
parameters . Fortunately, we only need a few of these.

We need to pass two things to our new Connection Object: the technology
we want to use to do the connecting to our database; and where the database
is. (If your database was password and user name protected, you would add these
two parameters as well. Ours isn’t, so we only need the two.)

The technology is called the Provider; and you use Data Source
to specify where your database is. So add this to your code:

Dim dbProvider As String
Dim dbSource As String
Dim MyDocumentsFolder As String
Dim TheDatabase As String
Dim FullDatabasePath As String

dbProvider = “PROVIDER=Microsoft.Jet.OLEDB.4.0;”

First, we set up five string variables. The last line specifies the provider
technology we’re going to use to do the connecting, in this case Jet.OLEDB.4.0:

“PROVIDER=Microsoft.Jet.OLEDB.4.0;”

Notice the semicolon at the end of the line. This is needed.

If you’re trying to connect to a modern Access database, however, then you
may need a different provider, one called ACE:

dbProvider = “PROVIDER=Microsoft.ACE.OLEDB.12.0;”

There’s even a version 15.0:

dbProvider = “PROVIDER=Microsoft.ACE.OLEDB.15.0;”

Next, you need the name of your database, and where it is. This could be a
location on your computer, or on a server. What we’ll do is to copy our database
to the Documents folder of Windows. First, add a line for the name of your database:

TheDatabase = “/AddressBook.mdb”

To point to the Documents folder, add this line:

MyDocumentsFolder = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)

The line is a bit complex. After the equal sign, though, we have this:

Environment.GetFolderPath( )

The folder path you’re getting goes between the round brackets of GetFolderPath:

Environment.SpecialFolder.MyDocuments

The Special Folder in this case is the MyDocuments folder (MyDocuments is the
name of the Documents folder.) Other special folders are:

After setting a database name and pointing to the Documents folder, we can
combine it for a data source:

dbSource = “Data Source = ” & FullDatabasePath

What we’re doing here is adding a path and a database to the text string “Data
Source = “.

Your coding window should now look like like this:

We now need a connection string.

At the top of the code, we set up this line:

Dim con As New OleDb.OleDbConnection

The variable con now holds an OleDbConnection object. This con object needs
a database provider, and a data source. You do this with the ConnectionString
property:

con.ConnectionString = PROVIDER_AND_PATH_HERE

After the equal sign, you need a database provider and a data source (the data
source is the path to your database).

We could have put these two things all on one rather long line:

con.ConnectionString = “PROVIDER=Microsoft.Jet.OLEDB.4.0;Data
Source = ‘C:/databases/AddressBook.mdb'”

Here, we have the PROVIDER and the DATA SOURCE together. Separating the two
is a semicolon. The Source is a file path pointing to a folder on our hard drive.
This works perfectly well for a simple connection string.

But the path to our database (the Documents folder) is rather long, so we’ve
spread out our code a bit more.

It does the same thing, though: passes the ConnectionString property the name
of a data provider, and a path to the database.

So add this line to your code:

con.ConnectionString = dbProvider & dbSource

Here, we’re using an ampersand character to combine the provider and data source.

Now that we have a ConnectionString, we can go ahead and open the database.
This is quite easy – just use the Open method of the Connection Object:

con.Open( )

Once open, the connection has to be closed again. This time, just use the Close
method:

con.Close( )

Add the following four lines to your code:

con.Open( )
MessageBox.Show(“Database is now open”)

con.Close()
MessageBox.Show(“Database is now Closed”)

Your coding window will then look like this:

Before testing out your code, make sure you copy the AddressBook database file
over to the Documents folder on your computer.

Test out your new code by running your programme. Click your button and the
two message boxes should display. If they don’t, make sure your Data Source
path is correct. If it isn’t, you might see a OleDbException was unhandled
error message:

The error message is a bit on the vague and mysterious side. But what it’s
saying is that it can’t find the path to the database, so it can’t Open the
connection. The line con.Open in your code will then be highlighted in green.
You need to specify the correct path to your database. When you do, you’ll see
the message boxes from our code, and not the big one above.

Now that we’ve opened a connection to the database, we need to read the information
from it. This is where the DataSet and the DataAdapter come in.

Learn about the DataSet and DataAdapter –>