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 an interface named Connection. In the .NET Framework, the Connection interface is defined by a class named ConnectionClass.

To create a connection to a database, declare a variable of type Connection or ADODB.ConnectionClass and initialize it using the new operator. This would be done as follows:

Imports ADODB

Module Central

    Sub Main()
        Dim conDatabase As Connection

        conDatabase = New ADODB.Connection
    End Sub

End Module

This can also be done as follows:

Imports ADODB

Module Central

    Sub Main()
        Dim conDatabase As New Connection

    End Sub

End Module

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

public Sub _Connection.Open(ByVal ConnectionString As String, _
     	  	            ByVal UserID As String , _
     		            ByVal Password As String, _
     		            ByVal Options As Integer)

As you can see, this method takes four arguments and all of them are required.

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:

Imports ADODB

Module Central

    Sub Main()
        Dim conDatabase As New Connection
        Dim strConnection As String

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

End Module

You can also include the value of the provider in single-quotes to delimit it.

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:

Imports ADODB

Module Central

    Sub Main()
        Dim conDatabase As New Connection
        Dim strConnection As String

        strConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
                        "Data Source='C:\\Programs\\Exercise1.mdb';"
    End Sub

End Module

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; the provider would be SQLOLEDB.

After creating the connection string, you can then pass it to the Connection.Open() method as the first argument. Here is an example:

Imports ADODB

Module Central

    Sub Main()
        Dim conDatabase As New Connection
        Dim strConnection As String

        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source='C:\\Programs\\Exercise1.mdb';"
        conDatabase.Open(strConnection)
    End Sub

End Module

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. If you don't have this information, you can omit it or you can pass each argument as an empty string. Here is an example:

Imports ADODB

Module Central

    Sub Main()
        Dim conDatabase As New Connection
        Dim strConnection As String

        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source='C:\\Programs\\Exercise1.mdb';"
        conDatabase.Open(strConnection, "", "", )
    End Sub

End Module

The fourth argument of the Connection.Open() method specifies whether the method should return after establishing the connection. If undecided, you can pass this argument as 0:

Imports ADODB

Module Central

    Sub Main()
        Dim conDatabase As New Connection
        Dim strConnection As String

        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source='C:\\Programs\\Exercise1.mdb';"
        conDatabase.Open(strConnection, "", "", 0)
    End Sub

End Module

After creating a connection to a database, you can specify what you want to do on the database. One of the most common operations you can perform is to submit a 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:

public Function _Connection.Execute(ByVal CommandText As String,
 			            ByRef RecordsAffected As Object,
  			            ByVal Options As Integer) As Recordset

The first argument, CommandText, can be a type of statement we will study in future lessons. The second argument, passed by reference, specifies the number of records that were affected by the operation. The third argument specifies how the provider should evaluate the command. If undecided, pass this argument as 0.

Here is an example of calling the Connection.Execute() method:

Imports ADODB

Module Central

    Sub Main()
        Dim conDatabase As New Connection
        Dim strStatement As String = "Something"
        Dim objAffected As Object
        Dim strConnection As String

        objAffected = Nothing

        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source='C:\\Programs\\Exercise1.mdb';"
        conDatabase.Open(strConnection, "", "", )
        conDatabase.Execute(strStatement, objAffected, 0)
    End Sub

End Module

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 a list of records.

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:

Imports ADODB

Module Central

    Sub Main()
        Dim conDatabase As New Connection
        Dim strStatement As String = "Something"
        Dim objAffected As Object
        Dim strConnection As String

        objAffected = Nothing

        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source='C:\\Programs\\Exercise1.mdb';"
        conDatabase.Open(strConnection, "", "", )
        conDatabase.Execute(strStatement, objAffected, 0)

        conDatabase.Close()
    End Sub

End Module

Probably a better way would consist of using a try...finally block to close the connection. Here is an example:

Imports ADODB

Module Central

    Sub Main()
        Dim conDatabase As New Connection
        Dim strStatement As String = "Something"
        Dim objAffected As Object
        Dim strConnection As String

        objAffected = Nothing

        Try
            strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source='C:\\Programs\\Exercise1.mdb';"
            conDatabase.Open(strConnection, "", "", )
            conDatabase.Execute(strStatement, objAffected, 0)
        Finally
            conDatabase.Close()
        End Try
    End Sub

End Module