Chapter 12: Using Access to Build SQL Server Solutions: Part II

Microsoft Access projects are impressive development tools for at least two key reasons. First, they expose many of the most powerful features of Microsoft SQL Server. Also, Access projects facilitate the application of many of the most powerful Access development capabilities to SQL Server databases. Chapter 11 focused on the user interface (UI) and database objects, such as tables, views, stored procedures, and user -defined functions. These topics are all server-side objects in a client/server solution supported by Access projects.

This chapter continues the saga of how to build client/server solutions with Access projects. However, this chapter switches the spotlight to the client-side tools. Several early sections in the chapter examine how to use forms with SQL Server record sources. These are followed by another section on building solutions with reports. Do not feel that you are limited to the form and report techniques covered in this chapter. In fact, most techniques for applying forms and reports in Access database files also apply in Access projects; this chapter describes selected differences. This capability to use forms and reports means that you can refer back to coverage of these topics in earlier chapters (see especially Chapter 5) for more ways to create SQL Server solutions with Access.

Creating a Form Based on a Table with One Click

One of the most remarkable features of Access projects is the ease and power that they bring to developing form-based solutions for SQL Server databases. For example, the AutoForm Wizard enables you to create a form based on a table with just a single click. When working with the AutoForm Wizard, remember that you do not select Forms in the Objects bar. Instead, you select a class, such as Tables. Then you highlight the database object to serve as the source for the form and click the AutoForm button on the toolbar.

A single click of the AutoForm Wizard creates a form that's bound to the table. If the table has a parent-child relationship with another table, the wizard automatically creates a form that has a main form control for the parent record and a subform control for its child records. If the table for the subform also has a parent-child relationship with a third table, the wizard automatically creates subdatasheets for the rows to represent the child records of the subform's parent records.

The automatic capturing of subform and subdatasheet relationships in Access 2003 is an improvement upon the first Access projects in Access 2000. The earlier version of Access allowed you to manually construct subforms, but it did not construct them automatically. In addition, Access 2000 did not manage subdatasheet relationships. However, Access 2003 and Access 2002 manage subdatasheets for tables and use that information when constructing forms.

All the samples in this chapter use the Chapter12.adp file, which needs to connect to the Chapter12SQL database on a SQL Server instance. The companion materials for this chapter include an .mdf file to enable installing the Chapter12SQL database on a SQL Server instance available to you. Detailed instructions for initially running Chapter12.adp and installing the Chapter12SQL database on your local SQL Server accompany this chapter in the "Opening an Access Project for an .mdf File" sidebar. You can adapt these instructions for moving a database between any two servers, such as the one in your office to the one in your client's office. To create a copy of an .mdf file from the current database to which an Access project connects, choose Tools, Database Utilities, Copy Database File. If your current database is Chapter12SQL, this creates a file named Copy of Chapter12SQL_dat.mdf. Rename and copy the resulting .mdf file for the database as your needs dictate .

Opening an Access Project for an .mdf File

Recall from Chapter 11 that an .mdf holds all the database objects, including their data, in a SQL Server database. This feature makes it easy to open an Access project on an .mdf file, such as the one in the companion content for this chapter (Chapter12SQL_dat.mdf). The Access project can be a new project with no client-side objects or an existing one with client-side objects, such as Chapter12.adp. When using a new project, the process is a little unusual because you start by clicking the link for a new Project using existing data ”yet, you create a new database on the server to which you connect. If you have an existing Access project with client-side objects, then you can just open the project. In either case, you need to open the Data Link Properties dialog box with the File, Connection command.

To work with the samples in this chapter, attach the Chapter12SQL database to your local SQL Server through the Data Link Properties dialog box for the Chapter12.adp file. Designate (local) as the server name. Choose the Use Windows NT Integrated security radio button. Do not select a database on the server because the one you want is not there the first time that you open Chapter12.adp. Instead, choose the Attach a database file as a database name option. Then type the name Chapter12SQL in the text box below the option. In the Using The Filename box, enter the path for the Chapter12SQL_dat.mdf; for example, C:\Access11Files\Chapter12\Chapter12SQL_dat.mdf. If your files are properly specified and your Windows login has permission to create a database on the local server, clicking the Test Connection button will confirm that your connection succeeded. Click OK to close the Data Link Properties dialog box, and click OK again to connect Chapter12.adp to Chapter12SQL on your local database server. When you subsequently open the Chapter12.adp file, it will connect to the Chapter12SQL database on your local server.

Once you create a new database on your local server, your local SQL Server instance will create a companion file, Chapter12SQL_log.ldf for your .mdf file. The SQL Server instance will actively manage the .mdf and .ldf files so they will not be available for use by other processes as long as the files stay attached to the SQL Server instance and the instance remains running.

 

Using the AutoForm Wizard with an Unrelated Table

The main sample database for this chapter (Chapter12SQL) includes a subset of database objects from the NorthwindCS database, as well as other database objects created specifically for this chapter. One of these objects is the Order Details table. This table contains a row with five columns of information. The OrderID column value repeats for as many rows as there are line items in the order denoted by the order ID. Figure 12-1 shows a form based on the Order Details table. You can generate a form like this by selecting the Order Details table in the Database window and clicking the AutoForm tool. The form permits users to browse, update, add, and delete records from the underlying table. You can save that form by clicking File-Save. Then type a name for the form (such as frmOrderDetails) in the Save As dialog box and click OK.