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 has also come up with a desktop solution to use with the ADP, known as the Microsoft Data Engine (MSDE). This is generally 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.
Tip
You can also use the MSDE to test your ADP and then easily modify the connection string to hook you up to the production database. Chapter 25, “Developing SQL Server Applications by Using ADP,” covers more about MSDE.
Looking at the Objects Used in Each
In your standard MDB are the usual objects, plus the new Data Access Pages (DAPs) now available in 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 Pages.”
Figure 3.1 shows the standard Access Database window displaying the Northwind sample data base that comes with Access.
Figure 3.1. Here is the good old MDB you've come to know and love, with a couple additions.
Note
New features found in both ADP and MDB are Data Access Pages and the new Outlook style toolbar. The new Groups feature (refer to Figure 3.1) is great when you're working on one area of a project and want to group multiple objects for development purposes.
You can take the same Northwind database and upsize it to SQL Server and an ADP. You can do this by choosing Database Utilities, Upsizing Wizard from the Tools menu while at the Database window.
Note
If you decide to do this, you will have to have the MSDE installed and running or be connected to SQL Server. For more information on how to do this again, turn to Chapter 25.
After this is done, you see the ADP in Figure 3.2, which although looks similar to the standard MDB, has different objects.
Figure 3.2. The objects in Tables, Views, Database Diagrams, and Stored Procedures are actually stored in the back end.
Table 3.1 lists the objects found in each and how they correspond with one another.
| MDB | ADP |
|---|---|
| Table | Table[*] |
| Relationship | Database Diagram[*] |
| Select Query | View[*] |
| Action Query | Stored Procedure[*] |
| Parameterized Query | Stored Procedure[*] |
| Form | Form |
| Report | Report |
| Data Access Page | Data Access Page |
| Macro | Macro |
| Module | Module |
[*] Stored in the back-end database on the server.
How you design your application also varies with which front-end type you use.
Tip
Stored procedures can actually buy you everything that views can and more, although they're a little harder to create. One drawback to views is the inability to specify ordering. You can use the View Designer to create a SELECT statement and then copy the SQL text into the stored procedure editor, where you can add parameters and ordering. For more on this, check out Chapter 25.