Making Access Project and Data Technologies Choices

• By F. Scott Barker
• Jun 7, 2002
• Sample Chapter is provided courtesy of Sams

This chapter is from the book
F. Scott Barker's Microsoft Access 2002 Power Programming

Before Access 2000, when you created an application, you had to plan whether you wanted to use a Jet (the database engine Access uses natively) or a client/server back end. Depending on the type of back end chosen, you would use various methods to create the application. Although this is still the case, you now have more choices to make when starting on a project. Now, in addition to the back-end choice, you must decide what type of database container to use: the traditional MDB or ADP.

The other choice to make is the method of accessing the data while working in VBA: DAO (Data Access Objects), which has been used since Access 2, or ADO (ActiveX Data Objects), the data-access technology now used throughout a number of Microsoft products, including Visual InterDev. You now have an additional choice of XML (Extensible Markup Language), which transfers data between systems and applications.

Helping you decide which technology to use in which case is what this chapter is all about. When you first look at the choices, it's hard to see when one should be used over another.

Using Microsoft Database Versus Access Database Project

The choice of whether to use MDB versus ADP actually follows the Jet versus client/server choice pretty closely. ADP, used as a front end for SQL Server, contains tools for editing not only Access objects such as forms and Data Access Pages, but also views and stored procedures.

Microsoft also provides a desktop solution to use with the ADP, known as the Microsoft Data Engine (MSDE). This is aimed at small workgroup applications. Generally, you want to use an ADP with MSDE when you think you will be moving your application to the full functionality of SQL Server 7/2000.

Looking at the Objects Used in Each

Your standard MDB contains the usual objects, plus the Data Access Pages (DAPs) available as of Access 2000. Data Access Pages let you create an application in Access, and then move it over to the Web. You can read more on DAPs in Chapter 12, "Working with Data Access Pages."

Figure 3.1 shows the standard Access Database window displaying the Northwind sample database that comes with Access.

Figure 3.1 Here is the good old MDB you've come to know and love.

In Access 2002, you can choose to work with Access 2000 or 2002 file formats (notice the title bar in Figure 3.1). Both Northwind.mdb and NorthwindCS.adp are provided in the Access 2000 file format.

You can take the same Northwind database and upsize it to SQL Server and an ADP by choosing Database Utilities, Upsizing Wizard from the Tools menu while in the Database window. (If you decide to do this, you must have the MSDE installed and running or be connected to SQL Server. For more information on how to do this, see Chapter 24.)

After the upsizing is done, you see the ADP in Figure 3.2, which, although it looks similar to the standard MDB, has different objects. Table 3.1 lists the objects found in each and how they correspond with each other.

Figure 3.2 In a ADP, the objects in Tables, Queries (now consisting of views, functions, and stored procedures), and Database Diagrams are actually stored in the back end.

Table 3.1  Comparing Objects Between MDB and ADP