Understanding the Place of Access in Office

Welcome to Automating Microsoft Access with VBA. In this book, you'll learn how to make your Access databases much more than just a convenient place to keep track of information. Visual Basic for Applications VBA is the core automation language that's built in to every copy of Microsoft Access. You'll see how you can speed up data entry, perform complex business processes, and even send data to other Windows applications. Even if you have no prior programming experience, by the end of the book you'll be writing code with the pros.

Before we dive into automating Access, though, we're going to take a few pages to understand the place of Access and VBA in the office automation landscape. That's what this chapter is about. Although VBA and Access are both popular and versatile tools, they're not the perfect tool for every job, and you need to be aware of the alternatives.

Presumably you're using (or thinking about using) Access to store information. But Microsoft Office 2003 offers three different applications that are suitable for storing information:

• Microsoft Office Access 2003

• Microsoft Office Excel 2003

• Microsoft Office OneNote 2003

Your first task is to choose the appropriate application for your own information storage needs.

Choosing Between Access and Excel

The most difficult choice for many users is whether to use Excel or Access for their business information.

Spreadsheets have been around since before most of us were working with computers, and they offer a familiar and accessible interface for storing information. Access, on the other hand, can be a bit harder to approach; there's a certain feeling that databases are harder to understand than spreadsheets. Microsoft encourages this separation by not including Access in the least-expensive versions of the Office suite.

Although it's true that both Access and Excel can store information in tables with rows and columns (see Figure 1.1), there are serious differences between the two. Understanding these differences will help you decide which application is right for your information.

Figure 1.1. Tables in Access and Excel.

The big advantage that Excel has over Access is the relative simplicity of its interface. Excel is a large and complex application, but it only stores things in one place: on worksheets in a workbook. By contrast, when you first open Access, you're faced with the database window, and no obvious place to type in your data. The many object types in Access (tables, queries, forms, reports, and so on) can also be overwhelming for beginners.

But Access offers a big advantage over Excel after you get past that initial learning curve: It takes better care of your information. For example, if you tell Access that a particular column in a database table will only contain dates, it won't let you type a customer name in that column by mistake. Excel, on the other hand, will let you type pretty much anything anywhere.

Access also understands the concept of relations between data. For example, you can tell Access what the connection is between a table of clients and a table of projects. This lets Access enforce business rules, such as the rule that each project must belong to a customer.

NOTE

Throughout this book, we'll assume that you already have a good understanding of the Access user interface and the basics of building a relational database. If you need to brush up on these subjects, we suggest reading Absolute Beginner's Guide to Microsoft Office Access 2003, by Mike Gunderloy and Susan Sales Harkins (Que, 2004).

Choosing Between Access and OneNote

Microsoft also released another information-storage application with Office 2003: Microsoft Office OneNote 2003, shown in Figure 1.2. Although aimed mainly at Tablet PC users, it also offers an interesting alternative for data storage on the desktop.

Figure 1.2. OneNote 2003 offers freeform information storage.

OneNote is designed to offer a flexible way to record and organize all sorts of information. You can type in it, hand-write notes, paste or drag in pictures and other rich content, and even make audio recordings a part of your notes. But it imposes even less structure on your information than Excel does. If you're having trouble figuring out how something could fit into Access or Excel because it's too unstructured, then you should take a look at OneNote as an alternative.