Access as an Application Development System

Being able to define exactly what data you need, how it should be stored, and how you want to access it solves the data management part of the problem. However, you also need a simple way to automate all the common tasks you want to perform. For example, each time you need to enter a new order, you don’t want to have to run a query to search the Customers table, execute a command to open the Orders table, and then create a new record before you can enter the data for the order. And after you’ve entered the data for the new order, you don’t want to have to worry about scanning the table that contains all your products to verify the order’s sizes, colors, and prices.

Advanced word processing software lets you define templates and macros to automate document creation, but it’s not designed to handle complex transaction processing. In a spreadsheet, you enter formulas that define what automatic calculations you want performed. If you’re an advanced spreadsheet user, you might also create macros or Visual Basic procedures to help automate entering and validating data. If you’re working with a lot of data, you’ve probably figured out how to use one spreadsheet as a “database” container and use references to selected portions of this data in your calculations.

Although you can build a fairly complex application using spreadsheets, you really don’t have the debugging and application management tools you need to easily construct a robust data management application. Even something as simple as a wedding guest invitation and gift list is much easier to handle in a database. (See the Wedding List sample database included with this book.) Database systems are specifically designed for application development. They give you the data management and control tools that you need and also provide facilities to catalog the various parts of your application and manage their interrelationships. You also get a full programming language and debugging tools with a database system.

When you want to build a more complex database application, you need a powerful relational database management system and an application development system to help you automate your tasks. Virtually all database systems include application development facilities to allow programmers or users of the system to define the procedures needed to automate the creation and manipulation of data. Unfortunately, many database application development systems require that you know a programming language, such as C, or Xbase, to define procedures. Although these languages are very rich and powerful, you must have experience before you can use them properly. To really take advantage of some database systems, you must learn programming, hire a programmer, or buy a ready-made database application (which might not exactly suit your needs) from a software development company.

Fortunately, Access makes it easy to design and construct database applications without requiring that you know a programming language. Although you begin in Access by defining the relational tables and the fields in those tables that will contain your data, you will quickly branch out to defining actions on the data via forms, reports, macros, and Visual Basic.

You can use forms and reports to define how you want to display the data and what additional calculations you want to perform-very much like spreadsheets. In this case, the format and calculation instructions (in the forms and reports) are separate from the data (in the tables), so you have complete flexibility to use your data in different ways without affecting the data. You simply define another form or report using the same data.

When you want to automate actions in a simple application, Access provides a macro definition facility to make it easy to respond to events (such as clicking a button to open a related report) or to link forms and reports together. Access 2007 makes using macros even easier by letting you embed macro definitions in your forms and reports. When you want to build something a little more complex (like the Housing Reservations database included with this book), you can quickly learn how to create simple Visual Basic event procedures for your forms and reports. If you want to create more sophisticated applications, such as contact tracking, order processing, and reminder systems (see the Conrad Systems Contacts sample database), you can employ more advanced techniques using Visual Basic and module objects.

Access 2007 includes features to make it easy to provide access to your data over your company’s local intranet or on the Web. You can share and link to data on a Windows SharePoint Services site. You can also export selected data as a static HTML Web page or link a Microsoft Active Server Page from the Web to your database.

Inside Out-What Happened to Data Access Pages? 

Office Access 2007 no longer supports designing data access pages (DAPs). Usability studies conducted by Microsoft show that DAPs are not a widely used feature within Access, and Microsoft is focusing more of their efforts on Windows SharePoint Services for sharing data in corporate environments. To maintain backward compatibility with previous versions, Access 2007 will continue to support existing .mdb applications that contain DAPs, but you cannot create new data access pages or modify existing pages from within Access 2007.

Access provides advanced database application development facilities to process not only data in its own database structures but also information stored in many other popular database formats. Perhaps Access’s greatest strength is its ability to handle data from spreadsheets, text files, dBASE files, Paradox and FoxPro databases, and any SQL database that supports the ODBC standard. This means you can use Access to create a Windows-based application that can process data from a network SQL server or from a mainframe SQL database.

For advanced developers, Access provides the ability to create an Access application in a project file (.adp) that links directly to SQL Server (version 7.0 and later). You store your tables and queries (as views, functions, or stored procedures) directly in SQL Server and create forms for data entry and reports for data output in Access.