Working with Select Queries and Recordsets

So far in this chapter, I've focused 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 database window, icons for saved action queries will 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) that will display the fields named

FirstName, LastName, and Email from a table named Customers. The weird-looking criteria expression, InStr([Email],'')>"1", limits the display to those records that have the characters 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 will produce. 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 the Datasheet view of a query, which is plainly visible onscreen, a recordset will be visible only to VBA, not to humans.

Creating a recordset in VBA usually takes several lines of code. As always, there are a ton of options for how you write the code. In general, the syntax of statements that you need to create a recordset from one or more tables in the current database is as follows:

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

myRecordSet.Open SQLstatement where

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

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

1 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 in the current database).

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

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/or 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"

Like 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 currently 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 as follows:

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

I suppose that anyway you slice it, the code needed to create a recordset is just plain ugly and intimidating. All those Dim and Set statements at the top of each example shown so far 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.

0 0


  • lidya
    How to select only visible rows recordset vba?
    7 years ago
  • mattiesko
    How to open recordset in a query using MS Access VBA?
    7 years ago

Post a comment