The Connection to a Database

Introduction

To use or access a database, a user typically launches it and opens the necessary object(s) from it. You too will need to access a database but with code. To programmatically access a database using the ADO library, you must first establish a connection. To support this, the ADODB namespace provides a class named Connection. You can also use ConnectionClass. To create a connection to a database, declare a variable of type ADODB.Connection or ADODB.ConnectionClass and initialize it using the New operator. This would be done as follows:

Private Sub btnConnection_Click(ByVal sender As System.Object, _

                ByVal e As System.EventArgs) Handles btnConnection.Click

        Dim conADO As ADODB.Connection

        conADO = New ADODB.Connection

End Sub

Opening a Connection

After declaring and initializing the Connection object, you can then open the connection. To support this, the Connection class is equipped with a method named Open. The syntax of the Connection.Open method is:

Open([ConnectionString As String = ""],

     [UserID As String = "",

     [Password As String = "",

     [Options As Integer = -1]

This method takes four arguments and all of them are optional. In reality, the first argument must be defined in order to establish as connection.

The Connection String

When establishing a connection to a database, you have two alternatives, you can use the first argument to the Connection.Open() method or you can separately create a connection string.

The connection string is text made of various sections separated by semi-colons. Each section is made of a Key=Value expression. Based on this, a connection string uses the following formula:

Key1=Value1;Key2=Value2;Key_n=Value_n;

One of the expressions you can specify in the connection string is the name of the provider. To do this, type Provider= followed by the provider you are using. For most databases we will create or use here, the provider will be Microsoft.JET.OLEDB.4.0. This means that our connection string can start with:

Private Sub btnConnection_Click(ByVal sender As System.Object, _

                ByVal e As System.EventArgs) Handles btnConnection.Click

        Dim conADO As ADODB.Connection

        Dim strConnection As String



        conADO = New ADODB.Connection

        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" . . .



End Sub

You can also include the value of the provider in single-quotes to delimit it. If you were working on an MSDE or a Microsoft SQL Server database, the provider would be SQLOLEDB.

The second part of the connection string specifies the data source. To provide this information, you can assign the path and name of the database to the Data Source attribute. Here is an example:

Provider='Microsoft.JET.OLEDB.4.0';Data Source='C:\Programs\Example1.mdb';"

It is important to note that the content of the connection string differs from one provider to another. If you were working on a Microsoft SQL Server database, your connection string would be different from the above done for a Microsoft JET database.

You can pass this connection string as the first (and probably the only) argument to the method. Here is an example:

Private Sub btnConnection_Click(ByVal sender As System.Object, _

                ByVal e As System.EventArgs) Handles btnConnection.Click

        Dim conADO As ADODB.Connection



        conADO = New ADODB.Connection

        conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

                    "Data Source='C:\Programs\SchoolRecords.mdb'")

End Sub

As mentioned earlier, the first argument is optional but you must find a way to specify it. In effect, there is an alternative. To separately support the connection string as its own object, the Connection class is equipped with a property named ConnectionString, which is of type String. To use it, declare a String variable, assign the connection string to that variable, and assign that variable to the Connection.ConnectionString property. You must do this prior to calling the Open() method. Here is an example:

Private Sub btnConnection_Click(ByVal sender As System.Object, _

                ByVal e As System.EventArgs) Handles btnConnection.Click

        Dim conADO As ADODB.Connection

        Dim strConnection As String



        conADO = New ADODB.Connection

        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

                    "Data Source='C:\Programs\SchoolRecords.mdb'"



        conADO.ConnectionString = strConnection

        conADO.Open()

End Sub

The Login Credentials

When creating your database, if you are working in a secure environment and the database requires authentication, you may need to provide login credentials, which include a username and a password. Normally, these properties are mostly applied if you are working on a Microsoft SQL Server database.

To specify the login credentials when accessing the database, you can pass the second and the third arguments to the Open() method of the Connection class.

Executing a SQL Statement

After creating a connection to a database, you can specify what you want to do on the database. One of the most usual operations you can perform is to submit a SQL statement to it (the connection). This is also equivalent to executing the statement.

To execute a statement, the Connection class is equipped with the Execute() method. Its syntax is:

Execute(CommandText As String,

 	[ByRef RecordsAffected As Object,

  	[Options As Integer = -1]) As ADODB.Recordset

The first argument, CommandText, can be a SQL statement. We will study SQL in future lessons. The second and the third arguments are optional. Here is an example:

Private Sub btnConnection_Click(ByVal sender As System.Object, _

                ByVal e As System.EventArgs) Handles btnConnection.Click

        Dim conADO As ADODB.Connection

        Dim strConnection As String

        Dim strStatement As String



        conADO = New ADODB.Connection

        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

                    "Data Source='C:\Programs\SchoolRecords.mdb'"

        strStatement = "Blah Blah Blah"



        conADO.Open(strConnection)

        conADO.Execute(strStatement)    

End Sub

When it is called, the Execute() method of the Connection class examines and executes its (first) argument, in this case strStatement. If this method succeeds, it returns an object called a record set. We will study record sets in future lessons.

Closing a Connection

When using a connection, it consumes resources that other applications may need. Therefore, after using it, you should close it and free the resources it was using so they can be made available to the other parts of the computer. To close a connection, the Connection class is equipped with the Close() method. This can be done as follows:

Private Sub btnConnection_Click(ByVal sender As System.Object, _

                ByVal e As System.EventArgs) Handles btnConnection.Click

        Dim conADO As ADODB.Connection

        Dim strConnection As String

        Dim strStatement As String



        conADO = New ADODB.Connection

        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

                    "Data Source='C:\Programs\SchoolRecords.mdb'"

        strStatement = "Blah Blah Blah"



        conADO.Open(strConnection)

        conADO.Execute(strStatement)   



    	conADO.Close

End Sub