Automating Microsoft® Access with VBA By Susan Sales Harkins, Mike Gunderloy Using the ADO Connection Object 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: ConnectionString— Specifies the data source. ConnectionTimeout— Determines how many seconds to wait for a data source to respond. The default is 15, which might not be long enough if you're on a busy network or pulling data across the Internet. Mode— Sets the permission mode. See Table 16.1 for the intrinsic constants for this property. Table 16.1. Mode Property Constants Constant Explanation adModeRead Read-only connection adModeReadWrite Read-write connection adModeWrite Write-only connection adModeShareDenyRead Other applications can't open a read connection adModeShareDenyWrite Other applications can't open a write connection adModeShareDenyNone All applications can open a connection with any permission adModeShareExclusive Other applications can't open a connection adModeRecursive Subrecords inherit permissions of current record CursorLocation— Determines the location of the OLE DB provided cursor. There are two constants: adUseServer sets a server-side cursor and adUseClient sets a client-side cursor. You can think of a cursor as a set of rows from a table plus an indicator of the current row. DefaultDatabase— Specifies a specific database on a server to use for all data processes with this particular Connection object. IsolationLevel— Controls how database operations on different connections affect one another. See Table 16.2 for a list of intrinsic constants. You don't need to worry about this unless you're writing code that works with multiple users logged into the database at the same time. Table 16.2. IsolationLevel Property Constants Constant Explanation adXactUnspecified Returned when provider can't determine the isolation level adXactChaos Default setting that protects pending changes from being overwritten adXactBrowse adXactReadUncommitted Enables you to view but not change uncommitted changes from other transactions adXactCursorStability adXactReadCommitted Enables you to view changes from other transactions only after they're committed adXactRepeatableRead Enables requerying a Recordset to include changes pending from other transactions adXacIsolated Table 16.2. IsolationLevel Property Constants Constant Explanation adXactSerializable Isolates all transactions from all other transactions Provider— Specifies an OLE DB provider before opening the connection. CommandTimeout— Specifies how long to wait before terminating an attempt to connect while executing a command. 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: Use the Open method's connectionstring argument after creating the Connection object. Use the Connection object's connectionstring argument after creating the Connection object, but before opening the actual connection. Either way, certain information can be passed in the form of five arguments that are concatenated together in the connection string: Provider— Specifies the name of the OLE DB provider; check provider documentation for the exact string. See Table 16.3 for a list of common provider strings. Table 16.3. Common Provider Strings Application String Microsoft Jet 3.51 Microsoft.Jet.OLEDB.3.5.1 Microsoft Jet 4.0 Microsoft.Jet.OLEDB.4.0 ODBC Drivers MSDASQL Oracle MSDAORA SQL Server SQLOLEDB Data Source— Identifies the file to which you're connecting. Remote Provider— Specifies the server provider when opening a client-side connection. Remote Server— Identifies the server. URL— Identifies the connection string as a URL. TIP 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 connection.Close 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 TIP 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. Working with Command Objects You can use the Connection or the Command object to retrieve and manipulate data. However, the Command object has a few advantages: Using a Command object, you can avoid a Recordset object and update data directly, which can be faster with some databases because the changes are made on the server instead of dragging the records across the network. The Command object supports parameters; the Connection object doesn't. The Command object has more properties than the Connection object, which enables you to fine-tune its behavior. By way of a definition, you can compare the Command object to a stored procedure or other data access object that returns data because the Command object executes the process that actually returns the data. The object does so by executing code against the OLE DB data source or by retrieving data from the OLE DB data source. This makes the Command object a flexible tool to have around. Creating a Command Object Creating a Command object is similar to creating a Connection object: declare it and then define it. But you don't open it, which makes sense given the difference between the two objects. Use the following structure to declare and define a Command object: Dim strConn As String Dim cmd As ADODB.Command strConn = connectionstring Set cmd = New ADODB.Command cmd.ActiveConnection = strConn The Command's connectionstring argument is identical to that used by the Connection object. Executing the Command Object The Command object executes code against the data source that manipulates that data in some way—either changing it at the source or retrieving it. Usually, you'll use a Recordset object to retrieve data, you'll read about that object a bit later. To execute code that updates or retrieves data, use the Command object's CommandText property, which contains the actual instructions executed against the data (technically, the provider). This property is a string expression or value that contains a provider command, which can be a SQL statement, the name of a table, a URL, or a call to a stored procedure. The following procedure automates a simple update query that increases each of the hourly rate values (in the Timetrack.mdb Tasks table) by three percent using a SQL UPDATE command directly against the data: Private Sub EditCA() 'Update hourly rate values by 3 percent. Dim strConn As String Dim cmd As ADODB.Command Set cmd = New ADODB.Command With cmd .ActiveConnection = CurrentProject.Connection .CommandText = "UPDATE Tasks " & _ "SET HourlyRate = HourlyRate + (HourlyRate * .03)" .Execute End With Set cmd = Nothing End Sub Enter the procedure into a standard procedure (or use Chapter 16's example module). With the cursor inside the procedure, press F5. Nothing seems to happen, but open the Tasks table and you can see that each value has been updated, as shown in Figure 16.3. The first value, $154.50, was $150.00. All the values have been increased. Figure 16.3. A Command object updated the hourly rate values.
Pages to are hidden for
"Automating Microsoft® Access with VBA"Please download to view full document