|< Day Day Up >|
You need a connection to a data source and an object in which to store the data you retrieve from the data source. Technically, an ADO Connection object is a single connection to an OLE DB data source. What that means is that you can use the Connection object to connect to a data source.
You can connect implicitly or explicitly and neither method is more correct than the other. However, if you're going to use the same connection more than once, use an explicit Connection object. An implicit connection is created whenever you retrieve data without first declaring a Connection object. You might find it less confusing to use explicit Connection objects until you feel comfortable with the ADO object model.
Opening the Connection
An explicit connection actually declares and instantiates a Connection object as follows:
Dim cnn as ADODB.Connection Set cnn = New ADODB.Connection
We can't really show you an implicit connection because there's no declaration.
The Connection object comes with a number of properties that control the object's behavior:
Just remember to set the properties before actually opening the connection. For instance, to set a server side cursor, you use the following code:
Dim cnn as ADODB.Connection Set cnn = New ADODB.Connection cnn.CursorLocation = adUseServer
To open the connection, use the Connection object's Open method in the form
Connection.Open [connectionstring][, userID][, password][, options]
All the arguments are optional. The options argument is one of two intrinsic constants: adConnectUnspecified opens a synchronous connection (one on which only a single operation can proceed at one time) and is the default; adAsyncConnect opens an asynchronous connection. Using the default synchronous connection is typically fine for any operation that doesn't involve an extremely large amount of data.
About Connection Strings
There are two opportunities to specify the connection string:
Either way, certain information can be passed in the form of five arguments that are concatenated together in the connection string:
If you need help constructing a connection string, a visit to http://www.connectionstrings.com/ can likely solve all your problems.
The following connection string connects to the Northwind sample database that comes with Access on your local system from any database, assuming that it's installed in the default location:
Private Sub MakeConnection() Dim cnn As ADODB.Connection Dim strConn As String Set cnn = New ADODB.Connection strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Program Files\Microsoft Office\" & _ "Office\Samples\Northwind.mdb;" cnn.Open strConn MsgBox "Connection Made" cnn.Close Set cnn = Nothing End Sub
Just open a standard module and enter the procedure. Then, press F5. If the connection is made, Access displays the message shown in Figure 16.2. Click OK to clear the message box. This example establishes the data source as it opens the actual connection. (You can use any database as the Data Source argument, just be sure to type the complete and correct path.)
Figure 16.2. This message lets you know the connection was made.
Closing a Connection
The previous example doesn't do anything but connect to a data source. No data is retrieved. But you might have noticed the Close method at the end of the procedure. It's best to always disconnect the Connection object when you're done by executing the Close method. You don't have to destroy the object, because you might want to use it again.
The Close method takes the form
where connection represents a Connection object.
To reuse a closed Connection object, just execute the Open method. When you're actually done with the Connection object, set it to Nothing after closing the object as follows:
Set connection = Nothing
More often than not, you can use a simple connection shortcut. If the data you need is in the current database, use the CurrentProject object to define the connection as follows:
Dim cnn As ADODB.Connection Dim cnn As New ADODB.Connection Set cnn = CurrentProject.Connection
Using this method, you'll share exactly the connection to the data that Access itself is using.
|< Day Day Up >|