Working with Select Queries and Recordsets

To this point in this chapter, we focus mainly on Access action queries that you execute from VBA by using DoCmd.RunSQL. Select queries, which only display data (and never change the contents of a table), are a completely different story. In Access, you don't run a select query. You simply switch from Query Design view to Datasheet view to see the records returned by that query. And in VBA, you don't use DoCmd.RunSQL to execute a select query. Rather, you store the results of the query in a weird, invisible thing called a recordset.

When you click Queries in the Access Navigation pane, icons for saved action queries generally include an exclamation point (!), and icons for saved select queries have no exclamation point.

Look at an example, starting in Access. The left side of Figure 7-13 shows a fairly simple select query (in Query Design view) that displays the fields named FirstName, LastName, and Email from a table named Customers. The criteria expression, Like "*@aol.com*" , limits the display to those records that have the characters @aol.com somewhere in the e-mail address. Switching that query to Datasheet view shows the query results, as in the lower-right half of that same figure.

In VBA, that Datasheet view of the query shows exactly what a recordset that uses the same SQL statement of the query produces. As with any query, you can easily view (and copy) a select query's SQL statement by right-clicking the query's title bar and choosing SQL View. However, unlike in the Datasheet view of a query, which is plainly visible on-screen, a recordset is visible only to VBA, not to humans.

Figure 7-13:

Simple select query and its datasheet results.

Figure 7-13:

Simple select query and its datasheet results.

Creating a recordset in VBA usually takes several lines of code. As always, you have a ton of options for how to write the code. The syntax of statements that you need in order to create a recordset from one or more tables in the current database generally looks like this:

Dim cnnX As ADODB.Connection Set cnnX = CurrentProject.Connection Dim myRecordSet As New ADODB.Recordset myRecordSet.ActiveConnection = cnnX

myRecordSet.Open SQLstatement where

I cnn is a variable name of your choosing that defines the connection.

I myRecordSet is the name that you want to give to your recordset. (You use whatever name you put here to refer to the recordset from elsewhere in the procedure.)

I SQLstatement is a valid SQL statement that isn't an action query (for example, the SQL from any select query's SQL view, or the name of a table or query in the current database).

If the code shows you an error message stating that it doesn't know what the ADODB.Connection is, you have to tell VBA where to look for it. Choose ToolsOReferences from the VBA Editor menu bar, scroll down to Microsoft ActiveX Data Objects 2.8 Library in the Available References list, and check the box next to it. Click OK and VBA knows what an ADODB.Connection is, even if you don't.

Start with a simple example. Suppose that you want to create a recordset named myRecordSet that contains all the fields and records from a table named Customers. In that case, you don't need SQL because using the table name in the myRecordSet.Open statement is sufficient, as shown here:

Dim cnnl As ADODB.Connection Set cnnl = CurrentProject.Connection Dim myRecordSet As New ADODB.Recordset myRecordSet.ActiveConnection = cnnl myRecordSet.Open "[Customers]"

If you want the recordset to contain only some fields and records from the Customers table, use a valid SQL statement in place of the whole table name. For example, the SQL statement SELECT FirstName, LastName FROM Customers creates a recordset that contains only the FirstName and LastName fields from the Customers table. Using that SQL statement in place of the table name in the code looks like this:

Dim cnnl As ADODB.Connection Set cnnl = CurrentProject.Connection Dim myRecordSet As New ADODB.Recordset myRecordSet.ActiveConnection = cnnl myRecordSet.Open "SELECT FirstName, LastName FROM Customers"

As with action queries, the SQL statement for a select query can be very long. To prevent super-wide lines in your code, you can store the SQL statement in a variable in chunks. Then use the variable name in place of a table name or SQL statement in the myRecordSet.Open statement. For example, the following SQL statement is from a query that shows the CustID, FirstName, LastName, and Email fields from a table named Customers but only for records where the Email address field is now empty (or null, in programmer lingo):

SELECT Customers.CustID, Customers.FirstName,

Customers.LastName, Customers.Email FROM Customers

To use that SQL statement in VBA, you could write the code this way:

Dim cnnl As ADODB.Connection Set cnnl = CurrentProject.Connection Dim myRecordSet As New ADODB.Recordset myRecordSet.ActiveConnection = cnnl

'We'll put lengthy SQL statement in variable named mySQL. Dim mySQL As String mySQL = "SELECT Customers.CustID, Customers.FirstName," mySQL = mySQL & " Customers.LastName, Customers.Email" mySQL = mySQL & " FROM Customers"

mySQL = mySQL & " WHERE (((Customers.Email) Is Null))"

'Now we use mySQL variable name in statement below. myRecordSet.Open mySQL

We suppose that any way you slice it, the code needed in order to create a recordset is just plain ugly and intimidating. All those Dim and Set statements at the top of each example shown to this point in this section need to be executed before the recordset is created with the .Open method. You wouldn't have to use those exact lines: They're just the standard lines that you use to build a recordset from a table or query in the current database. However, you do have to define a connection and name for a recordset before you can open it.

Was this article helpful?

0 0

Responses

Post a comment