Share this Page URL

Chapter 5. Using Visual Basic to Automat... > Converting a Macro to Visual Basic C... - Pg. 138

Using Visual Basic to Automate Your Database 138 Deciding to Use a Macro or Visual Basic The macros you create in Access can perform a number of standard database operations, including importing data stored in a spreadsheet, as you saw in Chapter 4. Later in this chapter, we'll write Visual Basic code to perform the same operation. So, why use Visual Basic when a macro can accomplish the task? In general, using Visual Basic gives you more control than a macro over a database operation that you automate. With Visual Basic, you can define more details about the conditions under which an operation takes place, for example. Here are some specific reasons for using Visual Basic code rather than macros: · Macros are separate objects in a database. If you've associated an event on a form or report with a macro, you need to import the macro as well as the form or report when you want to use the form or report in a different database. Visual Basic code that's associated with an event is part of the form or report, so you have to transfer only the form or report. · In Visual Basic, you can add code that responds to errors resulting from user actions or an unexpected event such as a network failure--something you can't do with a macro. Also, in Visual Basic, you can debug the code you write. We'll describe how to add error-handling code to Visual Basic procedures and how to work with Visual Basic debugging tools later in this chapter. · In a macro, when you provide the name of a database object or file that the macro will act on, the names are fixed and can't be changed when the macro is run. In Visual Basic, you can provide this information at run time, which allows more flexibility. For example, later in this chapter, we'll create a Visual Basic pro- cedure that lets you specify which table to import data into and which spreadsheet to import when the procedure runs instead of hard-coding this information in a macro. · Macros don't provide significant access to your data. You can work directly with data in Visual Basic-- adding records, changing the values of fields, and so on. · Visual Basic provides more ways than macros to manage the logical flow of a database routine that you automate. You'll see examples of some of these structures later in this chapter. · You have greater access to the information you create in Microsoft Word, Microsoft Excel, and other Mi- crosoft Office applications by using Visual Basic. With macros, you can start an application and transfer data, but you don't have access to the features of the application. In the section "Office Automation," later