| With the connection declared, you can open it wherever it makes sense to do so in your VBA code. After it's open, you can begin to move data back and forth across the connection. This is another area that highlights the flexibility in ADO: As you'll see, it's even possible to establish the connection and open it simultaneously. Depending on your preferences, you can provide the connection string to the Connection object directly, as shown in the prior section. Or you can provide it as an argument to the Connection object's Open method, as shown in the following sections. Using the Open Method with No Arguments If you've already supplied the necessary connection information to the Connection object, you can simply invoke the Connection object's Open method. For example Dim cnConnectToLedger As New ADODB.Connection Dim strConnectToLedger As String strConnectToLedger = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Documents and Settings\Owner\My Documents\GL.mdb;" & _ "Persist Security Info=False" With cnConnectToLedger .ConnectionString = strConnectToLedger .Open End With
This approach is handy when it's part of a subroutine that you call repeatedly, using another subroutine to send the connection string as an argument: Sub GetConnectionStrings Dim strConnectData As String Dim i as integer For i = 1 to 10 strConnectData = Sheets("LedgerAccounts").Cells(i,1) MakeTheConnection (strConnectData) Next i End Sub Sub MakeTheConnection(strConnectData As String) Dim cnConnectToLedger As New ADODB.Connection With cnConnectToLedger .ConnectionString = strConnectData .Open End With 'Statements using the connection go here End sub
Passing an argument from one subroutine to another is a common practice in all programming languages, including VBA. In the preceding example, the subroutine named GetConnectionStrings picks up a series of 10 strings from the worksheet named LedgerAccounts by means of a For-Next loop. After the loop has obtained a string from the worksheet, it passes the string to the subroutine named MakeTheConnection. That subroutine uses the string that it receives as the value assigned to the Connection object's connection string. In this way, the main subroutine GetConnectionStrings can obtain a series of strings, each containing different connection information, and use them to direct the connection to different data sources. For example, if the ledger data is stored in different databases, the source locations in the worksheet might appear as shown in Figure 8.6. Figure 8.6. By resetting its connection string, you can use the same Connection object repeatedly. 
The first time the loop executes, information in cell A1 is picked up and passed to MakeTheConnection. The second time through the loop, the information in A2 is picked up and passed. Each time, MakeTheConnection sets a different connection based on the connection string it receives from GetConnectionStrings. Specifying the Connect String in the Open Method If you prefer, you can specify the connect string as part of the Open method itself. The Open method can take from zero to four arguments. The prior section showed how you can use it without arguments; of course, in that case, the Connection object itself must already have information about where the data is located. The Open method's syntax is Connection.Open ConnectionString, UserID, Password, Options
Each of its arguments is optional. Suppose that you need to open the databases with a different user ID. You might put the user ID in the connect string itself or in some other location perhaps next to the connect string in the worksheet, as shown in Figure 8.7. Figure 8.7. If you are confident enough of your security arrangements, you could specify passwords along with user IDs. 
With this sort of setup, your VBA code might be as follows: Sub GetConnectionStrings Dim strConnectData As String, strPassword as String Dim i as integer For i = 1 to 10 strConnectData = Sheets("LedgerAccounts").Cells(i,1) strPassword = Sheets("LedgerAccounts").Cells(i,2) MakeTheConnection strConnectData, strPassword Next i End Sub Sub MakeTheConnection(strConnectData As String, strPassword As String) Dim cnConnectToLedger As New ADODB.Connection cnConnectToLedger.Open strConnectToLedger, strPassword 'Statements using the connection go here End sub
Here the code picks up not one but two strings from the worksheet: a connection string and a password. They are both passed to the MakeTheConnection subroutine and used as arguments to the Connection object's Open method. There are two aspects of this code to bear in mind: When you supply the connection string as part of the Open method, you don't need to do anything special with the Connection object other than to open it. Recall that you can supply a user ID as part of the connection string itself. Using the UserID and (if necessary) the Password arguments to the Open method, you don't need to supply them with the connect string. If you do both, however, the values supplied as Open method arguments supersede any values supplied within the connection string.
NOTE Besides the Connection object, ADO has two other major objects, the Recordset and the Command object. They can both be used to establish connections, and they have other properties than are supplied by the Connection object. The Recordset and the Command objects are covered in Chapters 10 and 11.
| 