The first thing you must do is connect to a data source. In order to do this, you must give VBA a few pieces of information. This information is passed to VBA in the form of a connection string. A connection string is fundamentally nothing more than a text string that holds a series of variables (also called arguments), which VBA uses to identify and open a connection to a data source. Although connection strings can get pretty fancy with a myriad of arguments and options, there are a handful of arguments that are commonly used when connecting to either Access or Excel. If you're new to ADO, it helps to focus on these commonly used arguments:

Take a moment now to see a few examples of how these arguments are put together to build a connection string:

In addition to building a connection to your data source, you must define the data set with which you need to work. In ADO, this dataset is referred to as the Recordset. A Recordset object is essentially a container for the records and fields returned from the data source. The most common way to define a Recordset is to open an existing table or query using the following arguments (see Table 15-1):

Recordset.Open Source, ConnectString, CursorType, LockType

The CursorTypes commonly used are shown in Table 15-2.

The following sections provide a few examples of how to declare a Recordset using the arguments you just covered.

MyRecordset.Open "MyTable", ConnectString
MyRecordset.Open "SQL", ConnectString, adOpenForwardOnly,adLockReadOnly

MyRecordset.Open "SQL", ConnectString, adOpenStatic, adLockOptimistic
MyRecordset.Open "SQL", ConnectString, adOpenDynamic, adLockOptimistic

Now it's time to put together the ADO fundamentals you have explored thus far to create your first ADO procedure. In this section, you build a procedure that transfers an Access table into an Excel spreadsheet.

Referencing the ADO Object Library

Before you do anything with ADO, you must first set a reference to the ADO Object Library. Just as each Microsoft Office application has its own set of objects, properties and methods, so does ADO. Since Excel does not inherently know the ADO Object Model, you need to point Excel to the ADO reference library, as shown in the following steps:

1. Open a new Excel workbook and the Visual Basic Editor.

   Tip

   Remember that in both Excel and Access you can access the VBE with the shortcut Alt + F11. Alternatively, you can access the VBE in Excel by selecting the Developer tab from the ribbon, and then selecting the Visual Basic icon.

   Depending on how Excel is set up, the Developer tab may not show up in your Ribbon by default. If it is not there, simply go to the top-left corner of the Ribbon and click the File tab in Excel 2010 (the Office icon in Excel 2007) and select Excel Options In the Personalize menu, you will see a check box entitled "Show Developer tab in ribbon." Make sure this box is checked.

2. Once you are in the Visual Basic Editor, go up to the application menu and select Tools

   

3. Scroll down until you locate latest version of the Microsoft ActiveX Data Objects Library. Place a checkmark beside this entry and click OK.

   Note

   It is normal to have several versions of the same library displayed in the References dialog box. It's generally best to select the latest version available. You will notice that in Figure 15-1, Microsoft ActiveX Data Objects Library 2.8 is used. Don't be too concerned if you only have earlier versions available; the examples in this chapter will run fine with those earlier versions.

4. After you click the OK button, you can open the Reference dialog box again to ensure that your reference is set. You will know that your selection took effect, when the Microsoft ActiveX Data Objects Library is displayed at the top of the Reference dialog box with a check next to it (Figure 15-2).

Note

You have just walked through setting a reference to the Microsoft ActiveX Data Objects Library using Excel. Keep in mind that these are the steps you take when you perform the same task in Access.

Also keep in mind that the references you set in any given workbook or database are not applied at the application level. This means that you need to repeat these steps with each new workbook or database you create.

Figure 15.1. Select the latest version of the Microsoft ActiveX Data Objects Library.

Figure 15.2. Open the References dialog box again to ensure that a reference to Microsoft ActiveX Data Objects Library has indeed been set.

Writing the Code

Once you have a reference set to the ADO Object Library, start a new module in the Visual Basic Editor by selecting Insert

Sub GetAccessData()

'Step 1: Declare your Variables
  Dim MyConnect As String
  Dim MyRecordset As ADODB.Recordset

'Step 2: Declare your Connection String
  MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
               "Data Source=
C:OffTheGridalexCorp Restaurant Equipment and Supply.accdb"

'Step 3: Instantiate and Specify your Recordset
  Set MyRecordset = New ADODB.Recordset
  MyRecordset.Open "Query_Products", MyConnect, adOpenStatic,
adLockReadOnly

'Step 4: Copy the Recordset to Excel
   Sheets("Your First ADO Procedure").Select
   ActiveSheet.Range("A2").CopyFromRecordset MyRecordset

'Step 5: Add Column Labels
 With ActiveSheet.Range("A1:C1")
      .Value = Array("Product", "Description", "Segment")
      .EntireColumn.AutoFit
 End With

End Sub

Tip

Installing the sample files for this book ensures that you have the Access database referenced in the previous code. You will also find a workbook called Chapter15_SampleFiles.xls containing this procedure along with the others found in this chapter.

When writing your own procedures, you will alter the connection string to reference the path for your data source.

Take a moment to think about what you are doing in each step:

1. Declaring the necessary variables: Declare two variables: a string variable to hold the connection string, and a Recordset object to hold the results of the data pull. In this example, the variable called MyConnect holds the connection string identifying the data source. Meanwhile, the variable called MyRecordset holds the data returned by the procedure.

2. Declaring the connection string: Define the connection string for the ADO procedure. In this scenario, you are connecting to the ZalexCorp Restaurant Equipment and Supply.accdb found on the C drive.

3. Assigning data to your Recordset: Once you've defined your data source, you can fill your Recordset with some data. Specify that your Recordset is read-only and filled with data from the Query_Products query found in the ZalexCorp Restaurant Equipment and Supply Access database. When writing your own procedures, you can replace the Query_Products query name with that of your own tables.

   Also notice that you must set the MyRecordset variable to a new ADODB.Recordset (Set MyRecordset = New ADODB.Recordset). VBA requires that you instantiate the Recordset object before it can be used.

4. Copying the Recordset into Excel: By the time you reach this step, the MyRecordset object is filled with data from the Query_Products query. Now, you use Excel's CopyFromRecordset method to get it out and into your spreadsheet. This method requires two pieces of information: The location of the data output and the Recordset object that holds the data you need. In this example, you are copying the data in the MyRecordset object onto the sheet called "Your First ADO Procedure" starting at cell A2.

5. Adding column labels: Interestingly enough, the CopyFromRecordset method does not return column headers or field names. Step 5 is where you add the column headers yourself. You are telling Excel to fill cells A1 through C1 with the respective values in the array. Then you tell Excel to AutoFit those columns so that all the data can be seen.

Using the Code

Be sure to save your changes, and then close the Visual Basic Editor. At this point, you can run your procedure simply by running the GetAccessData macro.

Better still, you can get fancy and assign the macro to a button. This gives you and other users an easy way to call the ADO procedure whenever you need to refresh the data extract from Access. Follow these steps:

1. Select the Insert icon from the Developer tab on the Excel ribbon.

2. Click the Form button as demonstrated in Figure 15-3; then click anywhere on your spreadsheet to drop the button on the sheet. You will immediately see the Assign Macro dialog box shown here in Figure 15-4.

3. Click the macro name to assign the macro to the button.

4. Click OK.

Figure 15.3. Insert a Form button.

Figure 15.4. Assign a macro to the button.

The reward for all your efforts will be a worksheet that pulls data directly from Access at the click of a button! Remember, this is all without the use of third party applications (MS Query) or manual manipulation. With ADO and VBA, you can build all the necessary components at one time in a nicely packaged macro, and then simply forget about it. As long as the defined variables in your code (that is, the data source path, the Recordset, the output path) do not change, then your ADO-based procedures will require virtually zero maintenance.

Writing a data extract procedure with ADO and SQL is very similar to writing an ADO procedure to extract data directly from an Access table. The difference is that instead of specifying a table name as the data source, you pass a SQL statement that defines the data you need. Start a new module and enter the following code.

Sub GetAccessData_With_SQL()

'Step 1: Declare your variables
   Dim MyConnect As String
   Dim MyRecordset As ADODB.Recordset
   Dim MySQL As String

'Step 2: Declare your connection string
   MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source= C:OffTheGridalexCorp Restaurant
Equipment and Supply.accdb"

'Step 3: Build your SQL statement
 MySQL ="SELECT Region, Market, Product_Description," & _
        " Sum(Revenue) AS Rev, Sum(TransactionCount) AS Units" & _
        " FROM PvTblFeed" & _
        " GROUP BY Region, Market, Product_Description"

'Step 4: Instantiate and specify your recordset
   Set MyRecordset = New ADODB.Recordset
   MyRecordset.Open MySQL, MyConnect, adOpenStatic, adLockReadOnly

'Step 5: Copy the recordset to Excel
   Sheets("ADO and SQL").Select
   ActiveSheet.Range("A2").CopyFromRecordset MyRecordset

'Step 6: Add column labels
   With ActiveSheet.Range("A1:E1")
       .Value = Array("Region", "Market", "Product_Description", _
       "Revenue", "Transactions")
       .EntireColumn.AutoFit
   End With

End Sub

Running this code queries the Access database and aggregates records on the fly to return data to an Excel sheet. Let's take a moment to talk about what you are doing in each step.

Passing criteria through your SQL statements allows you to evaluate each record in your dataset and selectively filter only the ones you need. This affords you tremendous flexibility that you can only achieve through SQL. Take a moment to review a few example SQL statements that use criteria to filter records.

"SELECT * FROM PvTblFeed" & _
         "WHERE Revenue > 2000"

"SELECT * FROM PvTblFeed" & _
          "WHERE Market = 'Denver'"

"SELECT * FROM PvTblFeed" & _
          "WHERE Effective_Date > #30/Jun/2004#"

"SELECT * FROM PvTblFeed" & _
          "WHERE (Effective_Date > #6/30/2004#) AND (Market = 'Denver')"

"SELECT * FROM PvTblFeed" & _
          "WHERE (Market = 'Denver') OR (Market = 'Charlotte'")

Using the LIKE Operator with ADO

Access users will note that the wildcard character used in the WHERE clause is not the asterisk (*) that is typically used in Access. Instead, the percent sign (%) is used. This is because the SQL statement will be passed through ADO, which only validates the percent sign as a wildcard character.

"SELECT * FROM PvTblFeed" & _
          "WHERE (Market Like 'C%')"

TROUBLESHOOTING ERRORS IN YOUR SQL STATEMENTS

Troubleshooting a SQL statement in VBA can be one of the most frustrating exercises you will undertake, primarily for two reasons:

• You are working in an environment where the SQL statement is broken up into pieces. Although this makes it easier to determine what the SQL statement is doing, it makes debugging problematic since you cannot readily see the statement as a whole.

• The error messages you get when SQL statements fail are often vague, leaving you to guess what the problem may be.

Here's a handy little trick you can implement to make troubleshooting a SQL statement a bit easier. Pass your SQL statement to a message box. The message box will enable you to see your SQL statement as a whole and more easily point out where the discrepancy lies. Take, for example, the following SQL statement:

MySQL ="SELECT Region, Market, Product_Description," & _
    "Sum(Revenue) AS Rev, Sum(TransactionCount) AS Units" & _
    "FROM PvTblFeed" & _
    "GROUP BY Region, Market, Product_Description"

This particular statement fails and throws the error shown in Figure 15-5.

The trick is to pass the MySQL string variable to a message box as demonstrated here:

MySQL ="SELECT Region, Market, Product_Description," & _
     "Sum(Revenue) AS Rev, Sum(TransactionCount) AS Units" & _
     "FROM PvTblFeed" & _
     "GROUP BY Region, Market, Product_Description"
MsgBox (MySQL)

Figure 15.5. This error message is vague and practically useless.

As you can see in Figure 15-6, this activates a message box that contains your SQL statement in its entirety. Here, you can review the SQL and determine that the culprits for the error are two missing spaces, one before the FROM clause and one before the GROUP BY clause.

Figure 15.6. Using a message box allows you to more easily pinpoint errors in your SQL statements.

That's right; two measly spaces cause the entire SQL statement to fail. Remember, these lines of code are not separate SQL statements; they are actually pieces of one SQL statement that have been broken down into parts. They are pieced back together when the function is executed. In that light, you have to consider, and include, all syntax that is necessary to create a valid SQL statement, including spaces. In this example, the fix for the error is simply to add a space before the FROM and GROUP BY clauses.

MySQL ="SELECT Region, Market, Product_Description," & _
     "Sum(Revenue) AS Rev, Sum(TransactionCount) AS Units" & _
     "FROM PvTblFeed" & _
     "GROUP BY Region, Market, Product_Description"

Up until now, you have used Access as the data source for your data pulls. However, use can also use an Excel workbook as a data source. To do so, you would simply build a SQL statement that references the data within the Excel workbook. The idea is to pinpoint the dataset in Excel to query by passing a sheet name, a range of cells, or a named range to the SQL statement.

The code shown here demonstrates how to query data from an Excel worksheet. In this example, the entire used range in the SampleData worksheet is queried to return only those records that belong to the North Region.

Sub GetData_From_Excel_Sheet()

'Step 1: Declare your variables
   Dim MyConnect As String
   Dim MyRecordset As ADODB.Recordset
   Dim MySQL As String

'Step 2: Declare your connection string
   MyConnect ="Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=
C:OffTheGridChapter15_SampleFile.xlsm;" & _
              "Extended Properties=Excel 12.0"

'Step 3: Build your SQL Statement
   MySQL = " SELECT * FROM [SampleData$]" & _
           " WHERE Region ='NORTH'"

'Step 4: Instantiate and specify your recordset
   Set MyRecordset = New ADODB.Recordset
   MyRecordset.Open MySQL, MyConnect, adOpenStatic, adLockReadOnly

'Step 5: Clear previous contents
   Sheets("Excel Data Pull").Select
   ActiveSheet.Cells.Clear

'Step 6: Copy the recordset to Excel
   ActiveSheet.Range("A2").CopyFromRecordset MyRecordset

'Step 7: Add column labels
  With ActiveSheet.Range("A1:F1")
      .Value = Array("Region", "Market", "Product_Description", _
      "Revenue", "Transactions", "Dollar per Transaction")
      .EntireColumn.AutoFit
  End With

End Sub

To query an Excel workbook, follow these steps:

There are often times when you don't necessarily want to overwrite the data in your Excel worksheet when you bring in fresh data. Instead, you may want to simply add or append data to the existing table.

In a typical scenario, you would hard-code the location or range where you want a given Recordset to be copied. In these situations, this location must dynamically change to reflect the first empty cell in your worksheet. The code that follows demonstrates this technique.

Sub Append_Results()

'Step 1: Declare your variables
   Dim MyConnect As String
   Dim MyRecordset As ADODB.Recordset
   Dim MyRange As String

'Step 2: Declare your connection string
   MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source= C:OffTheGridalexCorp Restaurant
 Equipment and Supply.accdb"

'Step 3: Instantiate and specify your recordset
   Set MyRecordset = New ADODB.Recordset
   MyRecordset.Open "Query_Products", MyConnect, adOpenStatic,
adLockReadOnly

'Step 4: Find first empty row and use that to build a dynamic range
   Sheets("AppendData").Select
   MyRange = "A" & _
   ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row + 1

'Step 5: Copy the Recordset to First Empty Row
   ActiveSheet.Range(MyRange).CopyFromRecordset MyRecordset

End Sub

The following steps show how to append records to an existing Excel table:

You will undoubtedly find a time when you need to pull data from an Excel file into an Access table. Again, there are several ways to get Excel data in Access, but using the one-two-three combination of VBA, ADO and SQL can provide some flexibility that is not easily attained using other methods.

The code that follows demonstrates how to query data from an Excel worksheet and append the results to an existing Access table. In this example, the SampleData worksheet is queried to return only those records that belong to the North Region.

Note that this code is designed to be run from Access. That is to say, you add this code to your Access database to pull data from Excel.

Sub GetData_From_Excel_Sheet()

'Step 1: Declare your variables
   Dim MyConnect As String
   Dim MyRecordset As ADODB.Recordset
   Dim MyTable As ADODB.Recordset
   Dim MySQL As String

'Step 2: Declare your connection string
   MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
               "Data Source=C:OffTheGridChapter15_SampleFile.xlsm;"& _
"Extended Properties=Excel 12.0"

'Step 3: Build your SQL statement
   MySQL = " SELECT * FROM [SampleData$]" & _
           " WHERE Region ='NORTH'"

'Step 4: Instantiate and specify your recordset
   Set MyRecordset = New ADODB.Recordset
   MyRecordset.Open MySQL, MyConnect, adOpenStatic, adLockReadOnly

'Step 5: Instantiate and specify your Access table
   Set MyTable = New ADODB.Recordset
   MyTable.Open "ExcelFeed", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic

'Step 6: Loop through each record and add to the table
   Do Until MyRecordset.EOF
   MyTable.AddNew
        MyTable!ActiveRegion = MyRecordset!Region
        MyTable!ActiveMarket = MyRecordset!Market
        MyTable!Product = MyRecordset!Product_Description
        MyTable!Revenue = MyRecordset!Revenue
        MyTable!Units = MyRecordset!Transactions
        MyTable![Dollar Per Unit] = MyRecordset![Dollar Per Transaction]
   MyTable.Update

MyRecordset.MoveNext
   Loop

End Sub

Use the following steps to append Excel records to an existing Access table:

From here, you simply fill the fields in the empty record you just created with the values that were returned from your SQL statement.

For many, text files are not only a source of data but also very much part of daily data operations. Given this fact, it's worth looking into how to pull data from text files using ADO and SQL. The connection string used to source a text file is as follows:

MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source= C:Integration;" & _
            "Extended Properties=Text"

A closer look at the Data Source argument reveals that only the file's directory is specified as the source for the data; not the actual file itself. The Extended Properties argument is set to Text.

Outside the difference in the construct of the connection string, querying a text file is very much similar to querying an Excel workbook.

Sub GetData_From_Text_File()
'Step 1: Declare your variables
    Dim MyConnect As String
    Dim MyRecordset As ADODB.Recordset
    Dim MySQL As String

'Step 2: Declare your connnection string
   MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
               "Data Source=C:OffTheGrid;" & _
               "Extended Properties=Text"

'Step 3: Build your SQL statement
   MySQL = " SELECT * FROM SalesData.csv"

'Step 4: Instantiate and specify your recordset
   Set MyRecordset = New ADODB.Recordset
   MyRecordset.Open MySQL, MyConnect, adOpenStatic, adLockReadOnly

'Step 5: Clear previous contents
   Sheets("Query Text").Select
   ActiveSheet.Cells.Clear

'Step 6: Copy the recordset to Excel

ActiveSheet.Range("A2").CopyFromRecordset MyRecordset

'Step 7: Add column labels
    With ActiveSheet.Range("A1:F1")
         .Value = Array("Region", "Market", "Product_Description", _
         "Revenue", "Transactions", "Dollar per Transaction")
          .EntireColumn.AutoFit
    End With

End Sub