Programming Microsoft Access 2000 (Microsoft Programming Series)

You can use Access Projects with MSDE or SQL Server to develop client/server applications almost as easily as you develop file server applications. The Access Project interface exposes nine basic application object types. Tables, views, stored procedures, and database diagrams are for the catalog of an MDSE or SQL Server database and are stored in the database file. Forms, reports, data access pages (for Web development), macros, and modules are stored in the .adp file. The Access Project coordinates with its client/server database via an OLE DB connection. These two parts-the database file, which is typically an .mdf file, and the .adp file-jointly comprise the client/server solution.

Connecting an Access Project to a Database

You can connect an .adp file to one of three database types:

• A SQL Server 7 database file on Windows 9x or Windows NT (Service Pack 4 or later)
• An MSDE file on Windows 9x or Windows NT
• SQL Server 6.5 on Windows NT (Service Pack 5 or later)

To monitor or reset the connection to the client/server database, choose Connection from the File menu. In the Data Link Properties dialog box (shown in Figure 12-1 below), select a database server, type of security, and database name. Typically, the database server name is the name of the computer running the server.

Figure 12-1. The Data Link Properties dialog box.

The figure shows a connection to an MSDE database running on a computer with the name CABXLI. Nothing in the dialog box clearly indicates that the database is an MSDE database instead of a SQL Server database. Since the CABXLI computer runs a Windows 9x operating system, it cannot run Windows NT integrated security. The Data Link Properties dialog box uses the default user-specific connectivity information. After the connection to the database server is established, the drop-down list box shows all the databases at the server. After you make these selections, the .adp file populates its table, views, stored procedures, and database diagram collections with objects. These settings persist between instances of an Access Project.

Learning from the NorthwindCS and Pubs Databases

Access 2000 ships with both file server and client/server versions of the Northwind sample application. The client/server version sets up as an Access Project. The Pubs database does not ship with Access 2000, but it has long been the standard SQL Server sample. If you use Access Projects with a SQL Server database manager, this sample will almost surely be available to you. You can open it in an Access Project. Many ADO and other database samples in the Access 2000 online help files specifically reference this database. Therefore, you can enhance your grasp of new database features in Access 2000 by becoming familiar with Pubs.

Using the NorthwindCS Project and database

The NorthwindCS.adp copies to your computer when you install Access Samples. This Access Project parallels much of the functionality and all of the data in the classic Northwind.mdb file. Access 2000 makes both available. To use NorthwindCS, you must either have MSDE installed on your computer or have a connection to a SQL Server 7 database manager because the Northwind database files ship with both of these products.

The first time you run NorthwindCS.adp, a script detects whether you have MSDE on your computer. If you do, the script asks whether you want to load the NorthwindCS database. A positive reply causes the script to load the database and connect the NorthwindCS Project to it. Otherwise, you can attach the NorthwindCS Project to the Northwind database on a SQL Server database manager.

The NorthwindCS Project inserts a special Show Me item on its menu bar. If you choose this item, you see a dialog box (shown in Figure 12-2) that makes available the Access Project demonstration, which explains special Access Project features and SQL Server data types. While Jet and SQL Server data types correspond in some ways, they also differ significantly. For example, the Timestamp data type is unique to SQL Server. SQL Server can also represent Currency and Date/Time values differently than Jet. In addition, SQL Server has explicit Unicode and non-Unicode data types.

Figure 12-2. The NorthwindCS Project's Show Me dialog box.

Another critical difference between Access Projects and traditional .mdb files is in their use of views, stored procedures, and queries. The Show Me dialog box explains how views and stored procedures replace queries in custom applications. Recall that .mdb files enable views and stored procedures against the Jet database engine, but the syntax rules for views in Jet and SQL Server differ. For example, .mdb files let you sort records in a view with the ORDER BY SQL term. Neither SQL Server nor MSDE permits ORDER BY in the SQL for a view. These client/server database managers reserve the ORDER BY phrase for stored procedures.

If the Show Me dialog box fails to address your needs completely, try the Access online help. Open the Office Assistant and ask for help on "Work with a Microsoft Access Project." You can get additional online help for Access Projects by opening an Access Project table in Design view and pressing F1. Close the Microsoft Access Help window before pressing F1 to enter another part of the help system with a lead heading of "Working with Access Project Components." This area of online help also includes details on Transact-SQL and SQL Server error messages. Finally, consider using SQL Server Books Online. (You need a computer with the full version of SQL Server 7 to tap this resource.)

Using Pubs with Access Projects

If you've ever worked with ODBCDirect or developed a custom remote database solution with Access, you've probably spent some time with the Pubs database. You can build on and expand that knowledge with Access Projects. Recall that you need a copy of SQL Server to work with Pubs. You can run the database in either SQL Server 7 or 6.5 with an Access Project.

To work with Pubs from an Access Project, you need an .adp file that points to Pubs. If a disconnected .adp file is not already available, you can create one by creating a new Access Project and then dropping its database. Then open the Data Link Properties dialog box and point the disconnected Access Project at the Pubs database.

To create a new .adp file, start Access and choose to create a new database selecting the Access Database Wizards, Pages, And Projects option. This opens the New dialog box. (Alternatively, you can click New on the Database toolbar.) Click on the General tab and then double-click Project (New Database). In the File Name text box, type a name for your new .adp file. Then click Create. This launches the Microsoft SQL Server Database wizard (shown in Figure 12-3), which contains the name of the local server (CABXLI in this example) and a suggested name for the database based on your filename entry. You must enter the user account information (an appropriate user ID and password-sa and blank usually work). Click Next and then Finish to complete the process. Once the Access Project is available, disconnect it from its data source by dropping its database: Choose Tools-Database Utilities-Drop-SQL Database. Finally, open the Data Link Properties dialog box for the disconnected Access Project. Enter information that points to the project file at the Pubs database on your SQL Server.

Figure 12-3. The opening screen of the Microsoft SQL Server Database wizard.

NOTE

After you create an Access Project linking to the Pubs database, you can mine the database for interesting samples of SQL Server technology that will let you refine your own tools. Figure 12-4 below shows the four stored procedures in Pubs with the byroyalty procedure open. This sample returns the author IDs for any authors with a royalty percentage agreement that matches the one input in response to the parameter query. The Mybyroyalty stored procedure is an extension of the initial one that returns the authors' first name, last name, and phone number.

Figure 12-4. The stored procedures in the Pubs database.

Recovering SQL Server databases

SQL Server is an industrial-strength database, but it is possible to damage its system so that you lose the ability to work with your data. For example, a media failure can corrupt the master database file. With very severe damage, you might not be able to start SQL Server, so you have to uninstall SQL Server or MSDE. You can choose MSDE-Uninstall from the Start menu or the Control Panel to remove the faulty installation. This removes all system databases, such as the master database, but it leaves any user databases, such as NorthwindCS.

As you begin working with MSDE or SQL Server, you might repeatedly install your client/server database manager to gain familiarity with the process. After reinstalling SQL Server or MSDE, copy over your new master with a recent backup of the master database or rebuild your master so that it recognizes your previously existing databases. Either action will permit you to connect with your previously existing user databases.

Keeping a current copy of the master database is a good idea because it can simplify connecting a fresh installation of SQL Server with your previously existing databases. Back up your master database after creating or deleting new user databases or login accounts. Adding a new user to a database does not modify the master because security information, such as user accounts, go into the database file.

Rebuilding the master involves renaming your old user database files and creating placeholder databases with the same names as your previously existing databases. Create new Access Projects for every database file that you need to recover. As you create a new Access Project, edit the name in its Microsoft SQL Server Database wizard dialog boxes to one of the database files you want in your new master. After you build a new Access Project for each database file you want to recover, copy the renamed old user database files over the newly created ones. This updates your master so that it knows about the user database files from a prior installation, and it copies each old file to a name that the new master knows.