Establishing a Connection with the Data

Beore you can open a specific database in code to retrieve and manipulate data, you must establish a connection with the data source. You can do this by utilizing the ADO Connection object. You should begin by declaring an object variable of a Connection type, like this:

Dim conn As ADODB.Connection

Part II

The Connection object variable can be declared at procedure level or at module level. Recall that by declaring the variable at the top of the module you can reuse it in multiple procedures in your module.

Next, initialize the object variable by using the Set keyword:

Set conn = New ADODB.Connection

So far you've created a Connection object that doesn't point to any particular data source.

Next, you need to supply the connection information. When connecting to an unsecured Microsoft Jet database, this includes at least the Provider name and the Data Source name.

You can set the Connection object's Provider property to specify the name of the provider to be used to connect to the data source. In this example, we are connecting to a Microsoft Jet database, so we'll use the Microsoft.Jet .OLEDB.4.0 provider. The names of common data providers used with ADO are listed in Table 10-2. You need to specify additional information, such as the database path, to establish a connection by using the Connection object's ConnectionString property.

Table 10-2: Common data providers used with ADO

Provider Name

Provider Property

Description

Microsoft Jet

Microsoft.Jet.OLEDB.4.0

Used for Jet 4.0 databases. By default, this provider opens databases in Read/Write mode.

Microsoft SQL Server

SQLOLEDB

Used to access SQL Server 6.5, 7.0, and 2000 databases.

Oracle

MSDAORA

Used to access Oracle databases.

ODBC

MSDASQL

Used to access ODBC data sources without a specific OLE DB provider. This is the default provider for ADO.

Active Directory Service

ADSDSOObject

Used to access Windows NT 4.0 directory services, Novell Directory Services, and LDAP-compliant directory services.

Index Server

MSIDXS

Read-only access to Web data.

Here's the code fragment that specifies the minimum required connection information:

With conn

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

.ConnectionString = "Data Source=" & CurrentProject.Path & "\Northwind.mdb" End With

Next, use the Connection object's Open method to open the connection to a data source:

conn.Open

Creating and Manipulating Databases with ADO

ADO syntax is quite flexible. A connection to a database can also be opened like this:

conn.Open "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\Northwind.mdb"

As you can see in the code fragment above, the Provider name and the Data Source (path to the database) information is supplied as an argument when you call a Connection object's Open method. Or you could open the connection like this:

With conn

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

.ConnectionString = "Data Source=" & CurrentProject.Path & "\Northwind.mdb" .Open End With

After you open the connection to the database you can perform the required tasks. For now, let's display a message stating that the connection was opened:

MsgBox "Connection was opened."

When you are done with the database you connected to, you should close the connection like this:

conn.Close

The Close method of the Connection object closes the physical connection to the data source. To completely release the resources used by the Connection object, set the Connection object variable to Nothing, like this:

Set conn = Nothing

Now, to reinforce what you've just learned about establishing the connection with a data source, take a look at the three procedures below. Note that each one of these procedures performs the same task in a slightly different way. In this chapter, you will learn various methods of coding and supplying connection information.

Sub ConnectionExample1()

Dim conn As ADODB.Connection

Set conn = New ADODB.Connection

With conn

.Provider = "Microsoft.Jet.OLEDB.4.0;" .ConnectionString = "Data Source=" & _ CurrentProject.Path & "\Northwind.mdb" End With conn.Open

MsgBox "Connection was opened" conn.Close Set conn = Nothing MsgBox "Connection was closed" End Sub

Part II

Sub ConnectionExample2()

Dim conn As ADODB.Connection

Set conn = New ADODB.Connection conn.Open "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\Northwind.mdb" MsgBox "Connection was opened" conn.Close Set conn = Nothing MsgBox "Connection was closed" End Sub

Sub ConnectionExample3()

Dim conn As ADODB.Connection Set conn = New ADODB.Connection With conn

.Provider = "Microsoft.Jet.OLEDB.4.0;" .ConnectionString = "Data Source=" & _

CurrentProject.Path & "\Northwind.mdb" .Open End With

MsgBox "Connection was opened" conn.Close Set conn = Nothing MsgBox "Connection was closed" End Sub

0 0

Post a comment