1.4. Tackling Projects

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.

The next step is to determine whether there will be an end product and what it will look like. In a project about sales data, the end product for a salesperson might be an Access report used as an invoice for the customer. The end product for a sales analyst might be a report in Excel with a pivot table and pivot chart. In cases when there is no end "product," you would want to define what actions you want to accomplish. Examples include accumulating data, updating data, and transmitting data.

Once you reach conclusions about which application will be automating the other, what information you need, and how it will be communicated, you are ready to take the first steps in designing the user interface. This might seem premature, but it is a good idea to prepare a prototype to ensure that you capture the necessary information. This prototype will be a work in progress and may change during the writing of the code (if you are using VBA).

This user interface is probably going to be an Access form, an Excel user form, or an Excel worksheet with protection enabled to only allow data entry into specific cells. If you don't do this, you will end up writing your VBA project twice the first time to make sure your code works, and the second time to change the references to your user interface. You can do this if it makes you more comfortable, but it will cost you some programming time. Another option is to write your procedures to accept parameters, allowing you to test the procedures and then call them with your user interface. This also makes it easier to reuse code where it makes sense to do so.

When you have thought through your user interface, your next step is to write the code. It is very helpful if you know what the results should be for a few simple data points so that you can effectively test the application. As you write the code for your first couple applications, keep an eye out for recurring items. For example, if you find yourself writing multiple lines of code to set up an Excel reference from Access, you can save that code somewhere and copy and paste it into applications as you need it. Another thing to keep in mind as you write code is to watch out for what might change in the future. For example, if you have some code that builds a 35-line report in Excel with formulas and subtotals, you might note that it is likely that this report could expand or contract in the future. You can prepare for that now by creating a table that holds the necessary data and allows you to change the report without rewriting any code.

The example steps above are simplified, but regardless of the complexity of your project, these steps will need to take place at some point if you want your integration project to be successful.