Cover | Table of Contents | Colophon

Table of Contents

Chapter 1: Introduction to Access/Excel Integration

Content preview·Buy reprint rights for this chapter

Most business users understand Excel; its power and practically universal acceptance make it a key application to learn. While Excel is a powerful tool on its own, you can do a lot more with it when you add the power of a relational database. Whether you store your data in a simple Access database or link an Access database to your corporate data warehouse, you'll be able to do a lot of things more easily. A simple query combined with an Excel workbook can supply many of the benefits of expensive reporting packages using the tools you already have on your desktop.

Consider the following scenario. Your company stores sales information in a database, and each sales record carries an identifier that tells who sold the item. You also have a table of salespeople that tells what region they are in and who supervises them. Senior management wants to find out how each salesperson, sales manager, and region performs on a daily basis. Since they want to see the reports so frequently, it will be necessary to automate these reports as much as possible. This book will show you how to gather the information and build the reports, charts, and supporting details that are necessary to meet these business objectives.

If you consider the other uses of corporate data, you will begin to understand how useful these skills can be. Here is a short list of fairly common uses of data:

• Producing a monthly commission schedule
• Reporting sales by product, region, sales manager, or salesperson
• Doing financial reporting
• Producing invoices
• Performing analysis of data (average profit per sale, sales by month, etc.)
• Producing trend information to aid corporate planning
• Populating financial models and storing results
• Graphing financial and sales information

Building systems that can simplify and automate these tasks can make complex projects much simpler. Fortunately, you likely already have the tools you need to do this on your computer and just need to assemble the parts correctly.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Communications Between Excel and Access

Content preview·Buy reprint rights for this chapter

There are several ways to exchange data between Access and Excel. Automation (formerly called OLE Automation) is a method of communication that gives you access to another application's objects . Using Automation, you can actually take control of the other application and send and retrieve data, set properties, run methods, and perform many other tasks. This book will explore in depth how automation can be used to allow integration between Access and Excel.

One of the original ways to communicate between Windows programs was dynamic data exchange (DDE) . While this can be useful, I do not recommend it between Office applications. It is sometimes necessary when you are communicating with a program that does not have a very useful object model. However, the object models for all of the programs in the Office suite allow you so much flexibility that I cannot imagine a situation when DDE would be preferable to Automation with VBA.

The other methods of communication treat Access or Excel simply as a data source and allow query access. This is accomplished through ActiveX Data Objects (ADO) or Data Access Objects (DAO). In addition to these programming methods, both Access and Excel offer data access methods from the standard user interface that work well for simple tasks.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Automation Objects

Content preview·Buy reprint rights for this chapter

If you are new to programming, the mention of objects might not make sense. Objects are programming items that make your life much easier. As an example, one of the main Excel objects is the Worksheet. The Worksheet object is a container for many other objects, such as cells, pivot tables, and charts. By using the Excel object model, you can perform many tasks with one line of code that would have taken hours if there were not another method available. Let's assume that you want to press a button on an Excel worksheet to print it. The following code prints the worksheet when you press the CommandButton1 button:

    Private Sub CommandButton1_Click()
    Dim xlws as Worksheet
    Set xlws = ActiveSheet
    xlws.PrintOut
    Set xlws = nothing
    End Sub

In this very short procedure, you declare a variable that is an Excel Worksheet (if you were automating Excel from another application, you would declare this as Excel.Worksheet and declare another variable as Excel.Application, but while in Excel this step is not needed). Next, you set this variable equal to the active worksheet—ActiveSheet represents the current worksheet in the active workbook. Once there is a reference to the active worksheet, you can call any of the methods that are part of the object. In this example, you call the PrintOut method of the worksheet. There are several objects in Excel that have a PrintOut method; in each case, it simply prints the object. The final step sets the xlws variable to nothing, which tells Excel to no longer store a reference to the object. The xlws variable in this procedure still exists, even though you are no longer using it—if you were in a procedure that used several worksheets, you could set xlws to nothing and reuse that variable with any other worksheet.

This code may still look complicated, but if you did any programming in MS-DOS where you had to understand how each printer worked and how to send commands to it, you would see how simple this is by comparison.

In future chapters, you will see how to set a reference to each application and how the object model of each can be used to accomplish even the most demanding tasks. You can also get context-sensitive help while working with the VBA project, and when you are not sure how to tackle an Excel task with VBA but know how to do it with the user interface, you can always record a macro and then review the code. Please see Appendix A for a review of the most commonly used objects and their usage in Excel and Access.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

ADO and DAO

Content preview·Buy reprint rights for this chapter

As stated earlier, ADO and DAO are the two primary methods of data access. For the purposes of connecting to a data source and simply extracting data, the two may be used interchangeably. According to Microsoft, DAO was designed specifically for the Microsoft Jet database at the heart of Access, but it is still able to access other databases while taking a performance hit. There are also some differences in features when it comes to making changes to a data source (adding tables, fields, etc.) and performing more complex query functions, such as data shaping, turning the query result into XML, and using cursors. I generally use DAO when dealing with Access (Jet) databases and ADO when dealing with SQL Server or other databases.

If you have done any work in Microsoft Access, you are probably familiar with queries . When you build a query in the design mode in Access, you are really making a graphical representation of the SQL. To see how this works, you can change the query view in Access to SQL View and see what this looks like.

When you use ADO and DAO, you can reference queries and tables and simply open them. Eventually you will need to modify queries or write them from scratch. In those cases, you can get a head start by designing the query graphically in Access, changing the view to SQL view, and copying the text to your VBA project. You can then make any changes that you need to.

While you can simply copy the text of a query and use it in your code, you can also write SQL on the fly within VBA. This is useful when you want to give users the option to bring in certain fields from the database, change the field used to sort, modify the sort order, etc. Also, there are times when you want to place criteria for a query directly in the query instead of using parameters.

In both ADO and DAO, the primary objects that you will work with are queries, recordsets, fields, and parameters. When using DAO, you also have an object called a QueryDef that performs specific tasks in the book. The

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Tackling Projects

Content preview·Buy reprint rights for this chapter

This is probably an appropriate time to discuss how to tackle a project that would benefit from integrating Access and Excel. If this discussion doesn't make sense at first, go through the first couple of chapters and come back to it.

The very first step that you need to take, prior to starting a project that integrates Access and Excel, is to determine whether you need the power of both applications. I wouldn't suggest using both applications if you can accomplish the same task with one application and few compromises. If you decide that you do need both applications, the following model should help you perform the initial planning.

The first step in the actual project is to determine which application will serve as the primary application for the user interface. Generally, this decision should be driven by end user needs and preferences. Although there are some exceptions to this, during your initial planning, assume that the program the users see should be the one that they are most comfortable with.

The second step is to determine what information you will need from your end user. It is important to note that in some cases a project will support multiple end users with different needs. A good example is an application that has one end user who wants to input sales data and another end user who wants to create reports based on that sales data. In this example, the two users will share the same data source but will need completely different user interfaces.

Once you have determined those items, your next step is to determine how you will communicate with the other application. Several factors influence this decision. First, the layout of the data makes some types of communication impossible or, at a minimum, silly to try. For example, an Excel spreadsheet with five data points on two worksheets in multiple rows and columns that are not contiguous would not be a candidate for using ADO or DAO, since they expect tabular structures. Likewise, if you need to pull 500 records from an Access database into an Excel sheet that mimics a database table, you probably want to let DAO or ADO do most of the work. This choice is also driven by how much control you need over the other application and the amount of processing that you need to perform on the data.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Designing Applications

Content preview·Buy reprint rights for this chapter

If you are writing code simply to make your own projects easier, thinking of them as applications might not be especially relevant. However, if you are building Microsoft Office applications that will be used by others, it is important to think about how the applications might change and how those changes can be dealt with. If you build an application that creates a set of reports and emails them to users, you could hardcode all kinds of information into the code. If you do that, though, any time the reports or recipients change, you will need to change the code.

I try to put elements that might change into tables that can be easily changed. To manage outgoing email, you could have a table that lists the reports and email addresses of the recipients. The code would open that table and send the reports based on the information in the table. This would allow the end user to make the changes necessary to email new or existing reports to new recipients.

While this might not seem very important, if you do not consider factors like this, you will spend more time modifying and maintaining applications than developing them. The same thoughts apply to connection strings to data sources, report formats, and other items where information can change over time. I once helped change an application that was written to produce a report of general ledger accounts with transactions over $1,000. Over time, the company grew and wanted to look at accounts over $50,000. As it turned out, the $1,000 parameter was hardcoded in the application code. Instead of just changing that code, I added a table that held parameters for the general ledger accounts to be queried, the dollar amount to review, and the tables holding the information (each type of transaction had a different table). Once I did this, changes to these criteria could be made without programming.

Some developers seem to build applications that always require developer assistance to make changes. This isn't a strategy I recommend, as it is dangerous for the end user. There are countless requests on the project boards online to modify applications that say that they cannot locate the original developer, or the original developer does not have time to work on it, or other similar reasons. It is also a good idea to document what each procedure does so that if you look at something you made two years ago, you can still follow what you were thinking.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Next Steps

Content preview·Buy reprint rights for this chapter

In the next chapter, I will introduce you to accessing data from the Excel user interface. This will be very useful for simple tasks for which you need a table of data from a database or another spreadsheet. You can also write database queries with Microsoft Query if you need more specific information than a table or prewritten query. These tasks are all managed from Excel's External Data toolbar. At the end of the next chapter, I will introduce PivotTables as a method for summarizing the data.

To give you a feel for what is to come, you will first learn data access from the Excel user interface, followed by using Excel VBA. Once this is accomplished, you will learn the Access user interface and Access VBA. Next comes an introduction to using these concepts with SQL Server and other Office applications. The final chapters in the book will cover more advanced topics on building applications that integrate Excel and Access. Where applicable, code samples will be available for download online at O'Reilly's web site.

As you go through the book, I suggest having sample Excel and Access files that you can use to apply the concepts discussed. If you don't have your own data, use the sample files that accompany the book. You will most likely get more out of the book if you type the code yourself and get a feel for how to use the VBA interface in Excel and Access. But you can certainly also use (or reuse) the code in the sample files without retyping it. However you decide to use the book, the concepts illustrated are focused around solving common problems that come up in a business environment.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 2: Using the Excel User Interface

Content preview·Buy reprint rights for this chapter

I first realized how powerful integrating Access and Excel could be while I worked for a company that calculated incentives. A database housed all of the data required to calculate the incentives. Before I took over the process, a report was printed from the database and rekeyed into an Excel workbook that performed all of the calculations. Eventually we moved from rekeying, to using Microsoft Query to pull data from the database, to finally having the database fill in the Excel workbook. Using an automated process not only saved time, but it also dramatically reduced errors. In the years since, I have found many more opportunities to integrate Access and Excel.

While it is tempting to jump right into using VBA to perform data functions, understanding when and how to use the Excel interface is still very useful and can provide a springboard to using VBA. When you want to use External Data from the Excel user interface , use the Import External Data function under the Data menu. From here, you can open and edit saved queries or create a new query.

External Data refers to any data that does not reside in Excel. Using the Import Data function on the Data menu, you can import entire tables or queries from Access and other databases. You can access this function by going to Data → Import External Data → Import Data. In addition to importing data from a database, you can also use this feature to import text files, XML files, web pages, etc. This is a very simple way to bring in all of the data from a table or query. The nice part about this feature is that you can refresh the data at any time by pressing the refresh button on the External Data toolbar (Figure 2-1). Also, as with any external data range, you can set it up so that any formulas done at a row level will be copied as the data range expands (described later in the chapter).

There are several other features available when working with an External Data range. These are available on the External Data Range Properties , which can be accessed either by right-clicking in the data range and selecting Data Range Properties, or from the External Data Toolbar. Refer to Figure 2-2 to see these properties.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Using External Data

Content preview·Buy reprint rights for this chapter

External Data refers to any data that does not reside in Excel. Using the Import Data function on the Data menu, you can import entire tables or queries from Access and other databases. You can access this function by going to Data → Import External Data → Import Data. In addition to importing data from a database, you can also use this feature to import text files, XML files, web pages, etc. This is a very simple way to bring in all of the data from a table or query. The nice part about this feature is that you can refresh the data at any time by pressing the refresh button on the External Data toolbar (Figure 2-1). Also, as with any external data range, you can set it up so that any formulas done at a row level will be copied as the data range expands (described later in the chapter).

There are several other features available when working with an External Data range. These are available on the External Data Range Properties , which can be accessed either by right-clicking in the data range and selecting Data Range Properties, or from the External Data Toolbar. Refer to Figure 2-2 to see these properties.

Figure 2-1: The External Data toolbar

Figure 2-2: The External Data Range Properties dialog box

While there are many options in the properties dialog, two key ones are "Refresh control " and "Fill down formulas in columns adjacent to data." Under the "Refresh control" section, there is a checkbox for "Refresh Data on File Open." This ensures that anytime the Excel workbook is opened, it will use the most recent data. If you are using a data source that is updated daily, this is probably enough; however, if you are using a data source that is constantly being updated, you can also select "Refresh every" for the amount of time that you want to elapse between each data refresh.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Using Database Queries

Content preview·Buy reprint rights for this chapter

Database Queries are valuable when you need more control over the data that is returned. Here is a quick example scenario where you can use a Database Query; this example uses the Northwind Database that comes with Access. Let's assume that your job is to review orders where the freight cost is over $100. There is a Query already designed in the Northwind Database called Orders Qry where the freight column is defined. You could bring in the entire table and search for records where the freight is over $100, but that would be time-consuming and error prone. It's simpler to make the computer do the work.

Choose New Database Query from the Import External Data submenu of the Data menu to get the screen shown in Figure 2-4. Since we want to use the Northwind Database, select MS Access Database from this dialog box and press OK, making sure that the box at the bottom of Figure 2-4 is checked to have the Query Wizard write the queries. The Northwind Database is in the Microsoft Office Samples Folder, as shown in Figure 2-5. After you select the Northwind Database, you get a list of all the tables and queries available in the database. For this example, you want to select the query called Orders Qry. You can expand a table or query to see all of the available fields, enabling you to select only the fields that you want. For this example, we want all of the fields, so click once on the name of the query and press the > button to place all of the fields in the query (Figure 2-6). To remove any of the fields, click on the field to remove and press the < button. In this case, since we want all of the fields, simply press the Next button.

Figure 2-4: The Data Source selection box used to create a new database query

Figure 2-5: The Select Database dialog box

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Returning a PivotTable to Excel

Content preview·Buy reprint rights for this chapter

Although collecting the data through the query is great, you may want to import data in a more analyzed form. Even if you are unfamiliar with PivotTables , you may find them preferable to Subtotals or any other summarizing capabilities that you currently use. When you use PivotTables, Excel makes it very easy to use their data to create a chart called a PivotChart. While you could always create a PivotTable on your own and select the name of your Data Range as the source, there may be situations when you want to see the PivotTable.

When you have gone through the previous example and are at the Import Data dialog box, click on the hyperlink for "Create a PivotTable report." You are now in Step 3 of 3 in the PivotTable Wizard (Figure 2-11). Before the PivotTable can be created, you need to click the Layout button, which brings up the screen in Figure 2-12. If you are unfamiliar with PivotTables, you can select fields to be used for Pages, Rows, Columns, and Data. For this example, let's assume that you want to see the number of shipments with freight over $100 that were shipped to each country and the total amount of the freight. To do this, click and drag Country to the Row section, and click and drag Freight to the Data section. Notice that Freight defaults to Sum; double-click on the Freight box and change it to the summarize by count option. Then click and drag the Freight field into the Data section, which defaults to Sum again. This gives you the selections for the PivotTable (Figure 2-12). Press OK, and it brings you back to the screen in Figure 2-11. Take the default of putting it into an existing worksheet beginning in cell A3 and press Finish. Your default location may vary, depending on which cell you started in. Just keep in mind that you can change the location of where the data will be placed before pressing Finish.

To add additional functionality, in the screen shown in Figure 2-12, put Country as a Page field and City as a Row field. This allows you to limit the PivotTable to one country in which you could see the cities. You could also make a PivotChart out of these results by right-clicking on the PivotTable and selecting PivotChart from the menu. However, you will notice that the chart does not present very meaningful results because you have both the count and the sum on the same axis. You can fix this by clicking on the drop-down box for data and unselecting either the Count or Sum field.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Using Microsoft Query to Gather Data

Content preview·Buy reprint rights for this chapter

Now that you have tried the Excel user interface, I want to introduce you to the Microsoft Query interface. Use the Microsoft Query interface instead of the Query Wizard when you need more control over the query. For example, you might want to add a calculated field or perform a complex join in your query. Also, while you can create a parameter query with the query wizard, you must edit a parameter query with the Microsoft Query interface. So, let's try a simple example to demonstrate how to change the query to a parameter query. Go back to your Query results from the first example, or go through the steps again (see Figure 2-10). Once you see the results, right-click in the result data and select Edit Query. Get to the final screen and select "View data or edit query in Microsoft Query." You will see the screen in Figure 2-13. In the "Criteria Field and Value" section, select Freight for the field and >100 for the value. To change this to a parameter, replace >100 with >[Amt] (you can use any name that does not represent a column in the Query in brackets). After you click off of that field, it will ask you for the parameter amount. This time, type in 500 for the amount, and press Enter. When you are finished, go to the File menu and select "Return Data to Microsoft Office Excel."

Figure 2-13: Microsoft Query screen

Creating a query as a parameter query is useful for changing the data that you look at regularly; using a parameter query is much easier than continuously editing the query in Microsoft Query. You can bring up the Parameters menu at any time by right-clicking in your data range and selecting Parameters. Excel allows you to choose whether you want to be prompted for the parameter, to use a particular number, or to refer to a cell to obtain the value. If you are going to change the value on a regular basis, I suggest using the option that looks in a cell for a value. This avoids prompting and also allows you to use VBA to refresh the query whenever you change the value of that cell.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Keeping the Query Updated with VBA

Content preview·Buy reprint rights for this chapter

This is an ideal time to introduce you to VBA. In the example above, you might not want to have to continue pressing refresh for your data. You may want the Query to automatically refresh itself with the new parameter each time you change cell B1. If you use VBA for programming Excel, you probably know that worksheets in Microsoft Excel have events that can have code associated with them. Events, as the name indicates, are triggered when certain events happen within Excel. Generally, these events are triggered from some type of user action. The event that you want here is the Worksheet_Change event. To set up Excel to automatically refresh your query each time you change the criteria, from your worksheet, press Alt-F11 to open the Visual Basic Programming Environment. You will see several sections; focus on the Project Explorer (Figure 2-15). If you do not see it, press Ctrl-R. Next, double-click on your current worksheet in the Microsoft Excel Objects list. There should be no code in the code window on the right side of the screen. Going from left to right on the top of the code window, go to the first drop-down box and select Worksheet, and on the second drop-down box, select Change. Excel fills in the opening and closing lines of the procedure; although the opening and closing lines are shown in the following code excerpt, they should not be typed in. Refer to the code below and type it into your procedure (Figure 2-16).

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wks As Worksheet
    Set wks = ActiveSheet
    If Target.Row = 1 And Target.Column = 2 Then
      wks.QueryTables(1).Refresh
    End If
    Set wks = Nothing
    End Sub

Figure 2-15: The Project Explorer treeview

Figure 2-16: Typing in the code, with Project Explorer on the left and the code window on the right

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Next Steps

Content preview·Buy reprint rights for this chapter

Now that you have seen the External Data features available from the Excel user interface and have been introduced to VBA, the next chapter will build on these features and also introduce some functionality that can only be accomplished through VBA. The next chapter will also show you how you can automate the formatting of data once it is extracted. As you go through the next chapter, start to think about the ways that you manipulate the data you bring into Excel today and how the functionality being introduced can be applied to that data to automate or add functionality to a task.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 3: Data Access from Excel VBA

Content preview·Buy reprint rights for this chapter

Up to this point, all interaction with the data source has been through the Excel user interface. I hope that you have seen how effective it can be in certain situations. You have also used VBA to enhance data access from the user interface. VBA gives you the opportunity to take more control of the data; it performs queries that are not possible from the user interface, and you can now automate many operations.

There are numerous situations when VBA is not only the preferred way to obtain data, but the only way. For example, you may run into situations in which you want to control access to the data by the time of day or another variable. Giving the user the ability to pull the data straight from the user interface limits your ability to restrict access to that data. You do not want people running queries that join a table with several hundred thousand customer records and a couple million financial transactions during the middle of the day. At the same time, though, you also cannot shut down the database to block the users' queries when the database needs to be available to perform official transactions. When a developer runs into a situation like this, an easy solution is to write code to pull data from the database while ensuring that the moment the query runs doesn't interfere with performance. The developer can then protect the code so that this restriction cannot be bypassed.

Another situation when it is preferable to use VBA to pull the data is when you need to perform actions based on information at the row level. While you could certainly pull the data in through the user interface and then write code to perform the actions within Excel on another worksheet, that method would result in duplication of data. When I am writing reports where I need to perform calculations at several subtotals, calculate ratios, and/or weighted averages, I control all of that through VBA. The other issue that will become more relevant as you move through this book is the choice between using Excel to pull the data from the data source and using the data source (Access) to automate Excel. This choice makes a large impact on application design.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Writing a Reusable Module for Data Access

Content preview·Buy reprint rights for this chapter

While some people argue that writing a reusable module creates some additional overhead, this is a good way to get introduced to pulling data using VBA. I suggest building a module that returns a database connection when you pass it certain parameters. A module holds procedures and functions and can be saved as a text file, which makes it very easy to use in other applications. While it is certainly easy enough to write the code to access a data source each time you need it, having a module written that you know works can reduce the variables when building an application. Even if you do not use it in production, you can always copy the code from the module to use in future applications. I use this same concept of writing a generic procedure to perform actions like dropping tables, running action queries, and other database tasks that will be done the same way on multiple objects. This way, I only have to write the code once.

The key to using this method is passing the parameters by reference (with ByRef). When you pass parameters to a procedure, you can either pass the value of the parameter or pass a reference to the variable. When you pass the value, the procedure gets the value and cannot change the original value for the calling procedure (provided that it is not a global variable). When you pass the variable ByRef, any action done on that variable in the called procedure is done on that variable in the calling procedure.

For example, let's assume that you have an integer variable x, and you want to pass it to a procedure to perform some math function on it. Then you want to use the result, and you do not need to know the original value later. You could write a function called DoMath and pass it your variable. You would create a variable in your original procedure to hold the value of the function that is returned. The other method is to create a procedure called DoMath but pass the variable x as ByRef. Then when you change the value of x in that procedure, the value of x is also updated in the original procedure. While that is a simplistic example, I hope it explains how this can be useful. In the project in the final chapter, you will see an example of how both of these concepts work in a sample application.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Choosing Between ADO and DAO

Content preview·Buy reprint rights for this chapter

In Chapter 1, I gave a brief description of ADO (including ADOX) and DAO. In this chapter, the biggest differences between the two methods of accessing data come with the use of queries. When you develop an Access Database, you will often have many different types of queries. For example, you might have one query that simply retrieves data (Select Query) or one that appends data from one table into another (Append Query).

When you are dealing with DAO, each type of query is a QueryDef object. Once you have a DAO database connection, you can simply cycle through the QueryDefs collection with a For Each...Next loop and get information about each query; you can then make changes to the queries, if necessary. The QueryDefs collection contains every query in the Access database. When using DAO, you do not need to know what type of query you are dealing with to take actions on it.

This is not as easy a task when using ADO and ADOX. ADOX categorizes queries similarly to the way SQL Server does. Specifically, ADOX considers action queries (Append Queries, Make Table Queries, etc.) to be procedures and select queries to be views. To loop through all of the queries in an Access Database using ADOX, you would cycle through the Procedures and Views collections. The other major difference between ADOX and DAO is that in ADOX, the Views are also part of the Tables collection, while in DAO they are not. The collection of tables in DAO is called the TableDefs collection. A single table is a TableDef.

The difference between accessing a select query as a view and as a table is that if you access a query in the Views collection, you have access to the Command and CommandText. This is what gives you the SQL of the View. When you access a View as a Table, you do not have access to the Command or CommandText.

I have included two samples below that show you how to loop through the queries and tables in an Access Database. Example 3-3 uses ADO, while Example 3-4 uses DAO. When I need to make changes to queries, I tend to use DAO when working with Access, and ADOX when working with SQL Server. The reason why I tend to use DAO when working with Access queries is that on many occasions when I am working with queries and I am writing SQL on the fly, the query may change from a select query to an action query. I find it is easier to just use the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

CopyFromRecordset Versus Looping

Content preview·Buy reprint rights for this chapter

When pulling data from a data source into Excel, you need to decide how you want to process the recordset. For example, if you need all of the fields and records, you have already been introduced to a simple method that does this: the CopyFromRecordset method places the results of a recordset for Excel Range objects in the range in an Excel worksheet. Sometimes, however, you bring in the results of a query but want only a few of the fields. You can accomplish this by looping through the records.

When dealing with ADO and DAO recordsets, there are several methods of record navigation. In most examples, I go to the first record in the recordset and move through the recordset until there are no additional records. You accomplish this by using the MOVEFIRST method of a recordset to go to the first record, and then the MOVENEXT method to move through the recordset. There are several strategies to figure out when you have cycled through all of the records. When you move past the last record, the recordset's EOF flag is set to TRUE. I suggest using a While...Wend loop. Assuming your recordset is a variable named rs, you write the While...Wend loop like this:

    rs.movefirst
    While Not rs.eof
      <Code to work with your Recordset>
      rs.movenext
    Wend

Since you have already been introduced to CopyFromRecordSet, let's take a look at an example of where you would want to loop through a recordset. Consider a table of employee information that has 15 fields. Assuming that you need only three of the fields (Last Name, First Name, and Salary), you have two choices for how to accomplish this.

The first method is to simply write your query to pull in only the fields that you want using the criteria that you want. This sounds easy, but in some cases it might be difficult if you are not familiar with how to write SQL and if you do not have the appropriate access rights to make changes to the database. In that case, you would need to find out the field names, loop through the records, and test for criteria, if necessary. You could also pull the records by position.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Formatting Techniques

Content preview·Buy reprint rights for this chapter

There are essentially two formatting methods, which are on a worksheet using VBA. The first method is to learn where all of the formatting options are and write the code from scratch. This method is most effective when doing the same thing many times (formatting numbers as currency, for example); however, trying to remember how to perform every type of formatting becomes a daunting task. So, this brings us to the second method: using the Macro Recorder .

To access the Macro Recorder from the Excel user interface, go to Tools → Macro → Record New Macro. If all you need is the formatting, pick one cell for each type of formatting that you need to perform. When you are done doing the formatting, press the Stop button to stop recording, or go to Tools → Macro → Stop Recording. Go to the Visual Basic Editor to view the code that the Excel Macro Recorder wrote to perform the formatting. This gives you the syntax needed for your code. If you always have the same number of rows, etc., you can just do all the formatting and then save the macro to run in the future. If you need to customize it, the next couple of examples show you how.

Determining how to refer to your range becomes tricky. For example, let's assume that you want to format a column as currency ($0.00). If you are certain that you want every cell in that column to be currency, you can set your range to be that column. In Excel, there is a property called Columns that is available on a Range object and a Worksheet object. If your range is already defined in a variable called xlrng, and you want to perform formatting on the third column, refer to this column as xlrng.columns(3). If you do this, I suggest having an additional range object that you can refer to. If you have a range object called xlrng2, you can write a line that says set xlrng2 = xlrng.columns(3). I like to do this so that the range properties and methods will be available as I write the code.

To explain that a little, if you have a worksheet that is currently active that you refer to as ActiveSheet, you can write

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Formatting Techniques Example

Content preview·Buy reprint rights for this chapter

So far you've seen how to pull in a recordset and how to do some formatting in Excel. The first couple of chapters show many examples of how to pull in recordsets because pulling is the primary method for bringing in data.

This example brings formatting and recordset pulling together. There is a query in the Northwind database that is called [Sales by Category], which is in brackets because there are spaces in the name. That data is not updated daily, but assume that you have a query named that on your system that is updated daily (or monthly, weekly, etc.). You also have a user who wants to see an Excel report that shows that data and produces subtotals by category. I will show you several methods to give the user the desired report, and you can pick the one that suits you best.

The first choice you have to make is whether to use ADO or DAO. Since Access will be my only source of data, I will use DAO. The next option is to decide where you want to put the data. I am going to default to the active worksheet starting in cell A4. This will give you room to put a title on the report. Once that is done, determine what you want to do with the data. The program offers two options: using the Subtotal feature in Excel or using a PivotTable. You could also use an input box and have the user type in a number, etc. I will use a message box to ask the user whether she wants a PivotTable. If she clicks Yes, it pulls the data and puts in a pivot table. If she clicks No, it pulls the data and uses Subtotals. If she presses cancel, it stops execution. This example also uses a button placed on the worksheet to allow you to run the code.

Your first step is to go into Excel and turn on the Visual Basic toolbar by going to View → Toolbars and checking Visual Basic. On the Visual Basic toolbar, click on the button that says Control Toolbox when you hover over it to bring up a box with the standard controls that you can place on your Excel Worksheets. We want to use the Command button. When you click the Command button box, your mouse cursor changes to a plus sign. Go to the top of your worksheet and click and drag a box to a size that you want.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Summary

Content preview·Buy reprint rights for this chapter

This chapter introduced you to the power of integrating Excel and Access. There is still a lot of ground to cover. At this point, my hope is that you understand how to build a recordset by using ADO and DAO. The rest of the chapter discussion and examples were meant to illustrate additional features already built into Excel. Our code just automated it. A side goal is that you are becoming familiar with the objects used in Excel VBA. When you read Chapter 5 on automating Excel with Access VBA, your familiarity with these objects will greatly increase your understanding of that chapter.

The Northwind database will not be used again until we use SQL Server in Chapter 7. For the rest of the examples, you will need to obtain the sample files available at www.oreilly.com. In these sample files, you will find the Excel example above and the other necessary databases and Excel workbooks, although I encourage you to try to enter the code for yourself, as it will help you get a better feel for VBA.

Figure 3-3: The resulting Excel file when the code in Example 3-5 is run showing subtotals instead of a pivot table, as well as details of the beverages category demonstrating how that works with subtotals

Note that there are appendixes at the end of the book that describe the commonly used Excel and Access objects that you use when doing this type of work. If you need help with VBA, there is a very complete language reference available from O'Reilly called VB & VBA in a Nutshell: The Language.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 4: Integration from the Access Interface

Content preview·Buy reprint rights for this chapter

Using VBA from Access is very powerful and will be covered fully throughout the rest of the book. However, Access has some very powerful features available right from the user interface for importing, exporting, and linking data with Excel. The ability to do this from the Access user interface makes many tasks easier. For example, suppose you have two Excel spreadsheets of customer data, and you want to know which customers are on one sheet but missing from the other. You could try to use Excel's VLOOKUP functions and possibly even write some code in Excel to search the other worksheet. From Access, though, you can simply link the two worksheets and run the Find Unmatched Query Wizard; this technique will be discussed later in the chapter.

There are many other easy-to-use features covered in this chapter. By the end of this chapter, you will know how to do the following things:

• Import data from Excel into a new or existing table
• Link an Excel worksheet or named range and treat it like a table in Access
• Export an Access table to be analyzed with Excel
• Use the Save As feature to export a table or query
• Save a report in an Excel format

Performing these tasks requires only a few mouse clicks. After using these features, you'll begin to see other possibilities for integrating these applications. Also, you can use Access to perform these tasks on DBF files, fixed-width text files, delimited text files, and ODBC data source tables. Our examples, however, will be focused on Excel.

Excel is commonly used to build a workbook that performs a series of calculations and returns a result set for analysis. Whether you think about it that way or not, that boils down to Excel's core functionality. The convenient thing about leaving data in Excel is that the result set changes as you make changes to the data. However, that is also the inconvenient thing about leaving data in Excel. There will be times when you will want to take a snapshot of result data and not want to have it changed by new data. Consider the input parameters in Figure 4-1 and the resulting data in Figure 4-2.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Importing Excel Data

Content preview·Buy reprint rights for this chapter

Excel is commonly used to build a workbook that performs a series of calculations and returns a result set for analysis. Whether you think about it that way or not, that boils down to Excel's core functionality. The convenient thing about leaving data in Excel is that the result set changes as you make changes to the data. However, that is also the inconvenient thing about leaving data in Excel. There will be times when you will want to take a snapshot of result data and not want to have it changed by new data. Consider the input parameters in Figure 4-1 and the resulting data in Figure 4-2.

Figure 4-1: An Excel worksheet providing inputs that drive calculations on the amortization page

Figure 4-2: Result data that changes when you make changes to the inputs on the LoanInformation worksheet

Assume that you want to load that data into a new table in Access, and you don't want it to change. In Access, create a blank database application by going into Access and selecting File → New. Depending on the version of Access, you will either get a brand new empty database or a menu like you see in Figure 4-3. If you see the menu in Figure 4-3, click on Blank Database and it will bring up the dialog box that you see in Figure 4-4. Next, give the database a name.

Figure 4-3: The new database menu that comes up in Microsoft Access 2003 when you select File → New

Figure 4-4: The dialog box that comes up when you want to create a new database, similar to the Save As dialog box that you would see in other Office applications

Unlike Excel, Access continually saves your progress as you update the data. This is helpful in the sense that you don't work all morning and lose your work due to a power outage or a network drive going down. But it also means that you can't do work and later go back to the point of your last save. The result is that in Excel, you can work in a workbook called Book1 which you don't need to save, while in Access a file must be created and saved.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Linking Excel Data

Content preview·Buy reprint rights for this chapter

There are many occasions when you want to work with Excel data as if it were in a table in Access, but you need to keep the source of the data in Excel. (This happens frequently at companies where Access is not on every desktop but everyone has Excel.) I worked with one company who maintained their product list in Excel, although many of the people who needed that information used Access. While you could simply import the data each time you needed it, this could cause problems if you forgot the importing step, causing someone to use old data. Linking to the Excel worksheet instead of importing it allows you to take advantage of the ability to query the data using Access while ensuring that you always have up-to-date information.

The steps for linking versus importing are virtually the same, except that when you right-click on the tables area, you select Link Tables instead of Import. What you have to be careful with and always keep in the back of your mind when linking to a worksheet is that any changes you make will also be made and saved within Excel. Normally, this would be a very good thing, but if you forgot about this feature, you could be very surprised when changes occur. For importing versus linking, I suggest determining how the information will be updated and how important the updates are. Consider the risks of linking and making inadvertent updates. It is easy to forget that you are dealing with data that, in reality, exists outside your application, so you might want to abbreviate a product name in your database and upset the person in charge of maintaining the product list in the Excel workbook.

As mentioned at the beginning of the chapter, it is also helpful to link Excel worksheets when you want to find items that are exclusively in one worksheet. Access provides a Find Unmatched Query Wizard that allows you to easily find items in one list that are not in another. Let's say you have an Excel worksheet with product information and a primary key of a Product Number, and another worksheet with sales information by product number, sale date, and amount sold. If you want to know which products had no sales, link to the two worksheets and run the Find Unmatched Query Wizard on the linked tables. The resulting query result set would give you the answer, and you could build a report using this data.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Using Export and Analyzing It with Microsoft Office Excel

Content preview·Buy reprint rights for this chapter

Now that you have tried to import data, assume you need to go the other way and send data to Excel. This method has far fewer steps than the import method, but before attempting to export data, you need to answer a few questions. First, find out how many rows are being exported. If there are more than 65,536 rows, you need to break the table up into pieces before exporting it. Next, consider the importance of the data format. If the data must be in a particular format, you can export the data, have an Excel report read it, and present it in another sheet. Another option is to use VBA to automate Excel. In this case, neither of these is a problem.

What if you need to put date information into the amortization table and email it to someone as an Excel worksheet? Sure, you could copy and paste from Access into Excel, format it, put in the formulas, etc.—or you could take advantage of the power of a query in the database. Where possible, I attempt to draw comparisons between how you would handle something in Excel versus Access.

In Excel, to add a payment date field that refers to the payment number, write a formula and copy it down to the end of your data. As new rows are added, continue to copy down the formulas. There is nothing stopping you from putting different formulas into every cell or not putting formulas in some cells. This can lead to problems with consistency.

If you contrast that with the Access solution, you see how the database makes this easier. In an Access database, you can write queries that can have calculated fields. If you want to have a field for the date of a payment based on the payment number, you could do so very easily with a calculated field. Since you know that the payment number is in a field named Payment, add a parameter for the begin date to make the calculated field work by placing the name of the parameter you want in brackets ([BeginDate], for example). You can go one step further by going to Query → Parameters when in Design View of the query, where you can declare the parameter and data type. You will be prompted for the parameter either way. The benefit to declaring the parameter is that Access validates that the data type is correct before running the query.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Using Raw Exported Access Data in Excel

Content preview·Buy reprint rights for this chapter

If you need to use information in Excel that comes from Access, you need to first determine if you want to pull the data from the database (covered in Chapters 2 and 3) or if you want to push the data into Excel from the database. Assuming that you want to push the data from Access into Excel, you have to then figure out how you will use the data.

There are no simple right answers (but there are certainly some wrong ones) as to what the best method is. I decide on the option where I have the most control. If I built the database, and I am charged with maintaining it, I would most likely choose to push the data into Excel and would probably opt to automate Excel from Access (covered in Chapter 5). Whatever you choose, keep in mind what you are doing with the data and make sure that the method you choose is easy to maintain. For instance, if you use Excel to pull the data from Access, the feature will often automatically create a named range (see Chapter 2). This can make using the data much more manageable. However, if you need a new Excel workbook each month and a macro can do your work for you within Excel, you can create a blank Excel sheet with your macro, paste in your data, run the macro, and do the processing from Access. In many cases, this will be easier than opening the correct workbook and pulling in the most current data.

I find that when I am trying to share data with other users in the basic database format with no formulas, using the user interface functions from Access is easiest. If, however, I need to manipulate the data in Excel for my own purposes, I generally use Excel to pull the data or Access VBA to automate the process.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Exporting an Access Report to Excel

Content preview·Buy reprint rights for this chapter

Use this feature at your own risk. When you open up an Access report, you can use the Export function from the File menu to save the report in a variety of formats. When you select Export, Access will bring up the Export To dialog box that looks like the typical Save As Windows dialog. You can change the file type by changing the Save As Type drop down box. If you need to keep the formatting and want it to look exactly like it does on the screen, your best bet is to use the Snapshot format. At the time of writing this, users can get the Snapshot viewer free from Microsoft. The other option, outside of Office, is to use a PDF printer to print the document to a PDF file that can be opened with Adobe Acrobat. If you don't have that option, the Snapshot viewer is your only choice if you want it to look exactly the same. These two options are the only ones that preserve controls. This is very important if you have charts that you want to share.

If you just want the basic data to be presented, I suggest using the Export function from the File menu to save it in Rich Text Format. This allows the report to be opened in Microsoft Word. The next choice on my list would be to save it as an HTML file. Exporting a report into an Excel format works, but I have found that the formatting and the layout with the subtotals, etc. are very clunky. So, this is the one area that I suggest that you either automate Excel using the query results directly or use something other than Excel when dealing with reports.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Next Steps

Content preview·Buy reprint rights for this chapter

This chapter covered some very basic functionality that exists in Access to deal with outside data sources, including Excel. Most of the functionality can be applied to other data sources as well. While these features can be very useful, I would guess that after you finish this book you'll use the more advanced functionality of Access VBA for most of your reporting tasks. The features covered in this chapter, therefore, will be used on a more ad hoc basis. Having said that, it is critical to understand how these features work because