Part II: Programming Microsoft Access
492
Finally, open the .udl file in Notepad and copy the connection string from the file’s contents and
paste into your VBA program. The connection string in the following procedure was generated
using this trick:
Public Sub OpenConnection()
Dim cnn As ADODB.Connection
Dim strCnn As String
Set cnn = New ADODB.Connection
‘ The exact path to the database may
‘ vary in the following statement:
strCnn = “Provider=Microsoft.Jet.OLEDB.4.0;” _
& “Data Source=C:\Northwind.mdb;” _
& “Persist Security Info=False”
cnn.ConnectionString = strCnn
cnn.Open CurrentProject.Connection
‘ Connection is open
Debug.Print cnn.Provider
cnn.Close
Set cnn = Nothing
End Sub
The reason this trick works is that a .udl file is recognized by Windows as a container for an ADO
connection string. Double-clicking on the file opens the Data Link Properties dialog box, which is the
default editor for .udl files. You specify all the attributes needed in an ADO connection string
through the Data Link Properties dialog box, and those attributes are added to the .udl file.
The Provider tab in the Data Link Properties dialog box shows all the providers currently installed
on the local machine. ADO is a local process, so the provider must be installed locally for ADO to
work. The ADO providers you see in Figure 13.10 were installed either along with Windows 7 or
as part of Office 2010.
The ADO Command object
The second major ADO topic is the Command object. As its name implies, a Command object exe-
cutes a command against the data source opened through a Connection. The command can be
as simple as the name of an Access query, or as complex as a long SQL statement that selects doz-
ens of fields and includes WHERE and ORDER BY clauses. In fact, the Command object is the most
common way to execute SQL Server stored procedures from Access applications.
As you’ll see later in this chapter, the output from executing a Command object can be directed
into a recordset. The data in the recordset can then be used to populate a form or controls such as
text boxes, combo boxes, and list boxes.
There are many, many ways to use Command objects. The following procedure is just one example
of using a Command object. In this case, the Command object populates a recordset with data taken
directly from tblCustomers. (Recordsets are discussed in the next section.) The following pro-
cedure (ExecuteCommand1) is included in basADO_Commands in the Chapter 13.accdb
example database.
