Chapter 3: Building Microsoft Access 2000 Client/Server Applications

Access 2000 Project Guide
Chapter 3: Building Microsoft Access 2000 Client/Server Applications

Mark Roberts and Acey J. Bunch
Microsoft Corporation

February 2000

Applies To: Microsoft Access 2000

Summary: Last in a series of three articles about developing client/server solutions by using Microsoft Access 2000 project files and tools. This article provides an overview of how to build forms and reports in a client/server environment, how to write the programming code that glues it all together, and how to administer and deploy your application. It will use the State University sample application to demonstrate many of the concepts discussed. (39 printed pages)



Click here to download this sample from the Downloads Center.

Contents
Introduction
Creating and Using Forms
Creating and Using Reports
Programming in Access Projects
Administration in Access Projects
Managing Security
Deploying Access Projects
Summary

Introduction
This is the last in a series of three articles that describe how to develop client/server applications by using Microsoft Access projects. This article discusses creating forms and reports that will be used to work with data from a Microsoft Data Engine (MSDE) or Microsoft SQL Server database, how to write programming code to interact with the database, and how to administer and deploy your application. The State University sample application will be used to demonstrate all topics covered.

At this point, we'll assume that you have at least created the State University Access project file, built all of the tables and created the relationships between them, and imported the sample data that we have provided, or have downloaded and installed the completed sample application and are following along with it.

Creating and Using Forms
One of the great things about Access projects is that they allow you to natively build applications against SQL Server or MSDE databases by using all the skills that you use to build applications against the Microsoft Jet database engine. Building a form is almost no different than building its Access database (.mdb) counterpart, and using the form is practically the same as well. However, there are a few things that are different, and these differences will be discussed in detail in the following sections.

To get started with using forms in Access projects, let's go ahead and create the form that will interact with the State University Instructors table.

Note We have created a basic form that you can use as a template for creating the State University forms. This form is named "Template" and can be found in the Template.mdb file. It contains an empty detail area and is not connected to any particular record source, but it does have the standard header graphics used for all of the State University forms. To use this template form, import it from Template.mdb into the StateU.adp file that you have created by pointing to Get External Data on the File menu, and then click Import. In the Import Objects dialog box, click the Forms tab, and click Template, and then click OK.


Create the Instructors Form

In this exercise, we will create the Instructors form, which is based solely on the Instructors table.

In the Database window, click Forms under Objects.
Create a copy of the Template form by using the Copy and Paste commands on the Edit menu. When prompted, name the form Instructors.
Select the newly created Instructors form, and then click Design on the Database window toolbar.
On the View menu, click Properties to open the Instructors form's property sheet.
In the Record Source property box, click the drop-down arrow and select Instructors as the record source. This will cause the Instructors field list to be automatically displayed.
In the Instructors field list, click the InstructorID field, hold down the SHIFT key, and then click the Email field to select all fields in the field list.
Drag all the fields from the field list to the Detail section of the Instructors form.
Arrange the fields in any way you like and, when done, close the Instructors form, and save your changes.
Display the newly created Instructors form by selecting the form in the Database window and then clicking Open on the Database window toolbar. Your form should look similar to the one shown in Figure 3.1.


Figure 3.1. The Instructors form

Quite easy, isn't it? Just like an Access database!ALink("ver, as mentioned in "Chapter 1: Understanding Microsoft Access 2000 Client/Server Development," there are some differences between forms in Access project (.adp) files and forms in Access database (.mdb) files. The next few sections will discuss these differences.

The Navigation Buttons
The most immediately noticeable difference is in the navigation buttons at the bottom of a form when it is opened for viewing. In addition to the standard buttons for navigating to the first, previous, next, and last records, there are two new buttons: Cancel Query and Maximum Record Limit, which are shown in Figure 3.2.

The Cancel Query button
The Cancel Query button is used to cancel the retrieval of records from the server, which is useful if the retrieval is taking too much time. The effect of clicking this button is that you will have an incomplete set of records available in the form. For example, let's say you open a form that is based on a table that contains 1,000 rows of data. If you click the Cancel Query button after retrieving the first 500 records, only those first 500 records will be available in the form, which means that clicking the Last Record button will take you to record 500, not record 1,000. To retrieve the remaining records, you will need to refresh or requery the form's recordset, which starts the retrieval process anew and retrieves all 1,000 records. To refresh a form's recordset, click Refresh on the Records menu.

To hide the Cancel Query button, you must hide the entire navigation bar by setting the form's NavigationButtons property to No (false).

The Maximum Record Limit button
The Maximum Record Limit button allows you to set the maximum number of records that Access will attempt to retrieve when it opens or refreshes a form's recordset. The default is 10,000, but you can, of course, change this setting to any value between 1 and 1,000,000. Setting the value to 0 or anything over 1,000,000 effectively sets the maximum to "no limit."

Note You should use the "no limit" setting judiciously. On extremely large recordsets, having no maximum record limit could slow down your application a great deal, and your application would generate an excess amount of network traffic.
To change the maximum record limit at run time, click the Maximum Record Limit button and a dialog box will be displayed, as shown in Figure 3.2.



Figure 3.2. The Maximum Record Limit dialog box

You can then either enter a value in the Max Records box, or you can use the slider control to select a value. Clicking anywhere outside the Maximum Record Limit dialog box will close it and save the values you entered.

Note Changing a form's maximum record limit value at run time causes the form to refresh its recordset and display the first record in the recordset.
You can change the maximum record limit value at design time by setting the form's MaxRecords property in the property sheet.

You can also set a global maximum record limit by typing a value in the Default max records box on the Advanced tab of the Access Options dialog box (Tools menu). However, this global limit will apply only to forms created after you have specified the global limit, and you can override this limit for any form by setting the form's maximum record limit as described previously.

To hide the Maximum Record Limit button, set the form's MaxRecButton property to No (false).

Why are the new navigation buttons important?
These new navigation buttons are important because SQL Server and MSDE are database server engines that run outside Access itself. Therefore, when you try to retrieve data from the server, your request is sent across the network to the database server, the database server fulfills the request, and then the data is sent back across the network to your Access application. All of this network traffic takes time and affects the performance of your application. To enhance performance when a form or table is opened, Access makes a single request to the server and then locally caches all of the data it retrieves in the form of a snapshot, or cached recordset.

These snapshots also allow forms to be displayed before all of the records have actually been brought down to the local computer. In addition, by allowing you to specify the maximum number of records brought down from the server, Access gives you greater control over how your users will interact with your data, and limits the demands that they will be placing on your database server and your network. All of these features improve the performance of your application.

For more information about the new navigation buttons, type control the number of records in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.

Client/Server Form Properties
Since an Access project uses SQL Server or MSDE as its database engine, it makes sense that some additional properties would need to be added to its forms. There are seven new properties in an Access project form, and one existing property that uses different values than are used in an Access (.mdb) database. The new properties are InputParameters, MaxRecButton, MaxRecords, ResyncCommand, ServerFilter, ServerFilterByForm, and UniqueTable. The RecordsetType property is not new, but its possible values differ when this property is used in an Access project form. The new properties are available in the property sheet for a form, as shown in Figure 3.3.



Figure 3.3. The Form property sheet showing additional properties for Access projects

The following sections outline these additional properties and what they can be used for.

RecordsetType
The RecordsetType property specifies the kind of recordset that is associated with a form. In Access databases, the recordset type can be set to Dynaset, Dynaset (Inconsistent Updates), or Snapshot. But in Access projects, the recordset type can be set to Snapshot or Updatable Snapshot (the default). If the RecordsetType property is set to Snapshot, none of the bound fields on the form can be edited. However, if the RecordsetType property is set to Updatable Snapshot, the bound fields can be edited. For more details about updatable snapshots and updating data in general, see "Updating Data" later in this chapter.

For more information about the RecordsetType property, type RecordsetType in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.

MaxRecButton
As mentioned previously in the "The Maximum Record Limit Button" section, the MaxRecButton property specifies whether the Maximum Record Limit button is displayed on a form. Setting this property to No (false) hides the button. If you hide the Maximum Record Limit button, the only way a user will be able to see more than 10,000 records at a time is to change the Default max records value on the Advanced tab of the Options dialog box (Tools menu).

For more information about the MaxRecButton property, type MaxRecButton in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.

MaxRecords
The MaxRecords property sets the maximum number of records that will be returned to the client. Setting this property overrides the Default max records value on the Advanced tab of the Options dialog box (Tools menu). If you set a value for the MaxRecords property in programming code, you will need to call the form's Requery method to refresh the recordset.

For more information about the MaxRecords property, type MaxRecords in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.

ServerFilter
The ServerFilter property filters the data that is retrieved from the database server and displayed in the Access project form. You use the same syntax for the ServerFilter property as you do to specify criteria in the WHERE clause of an SQL statement. For example, you could restrict the number of rows retrieved in the Instructors form by setting its ServerFilter property value to something like the following:

Copy Code
State='NV'

In this case, only instructors who live in Nevada would be displayed in the Instructors form. To set the ServerFilter property in code, use the following syntax:

Copy Code
Me.ServerFilter = "State='NV'"
Me.Requery

Note the call to the form's Requery method, which causes the form to refresh its underlying recordset. This call is necessary because the filter does is not automatically applied after you set the ServerFilter property.

Note The ServerFilter property has no effect on forms that use a stored procedure as their record source.
For more information about the ServerFilter property, type ServerFilter in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.

ServerFilterByForm
The ServerFilterByForm property specifies whether the form is opened in Server Filter By Form mode. If you set this property to Yes, the form is opened in Server Filter By Form mode, which means that no rows are retrieved from the form's record source when the form is first opened. This mode allows you to enter the filter criteria in the form's fields. You then apply the filter by clicking the Apply Server Filter button on the toolbar. To set the ServerFilterByForm property in programming code, use the following syntax:

Copy Code
Me.ServerFilterByForm = True

Note The ServerFilterByForm property has no effect on forms that use a stored procedure as their record source.
For more information about the ServerFilterByForm property, type ServerFilterByForm in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.

UniqueTable
The UniqueTable property specifies which table can be updated when the form is bound to a multiple-table view or stored procedure. If you need to bind the fields in the form to a record source that gets its data by joining tables, you need to tell Access which of the tables contains the unique rows for the record source. The unique table will be the table that is on the "many" side of the relationship, and once the UniqueTable property is set, only the values in the unique table can be updated. This property is discussed in more detail in "Working with Updatable Snapshots" later in this chapter.

Note Views and stored procedures are discussed in "Additional Record Sources" later in this chapter.
For more information about the UniqueTable property, type UniqueTable in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.

ResyncCommand
The ResyncCommand property specifies an SQL statement that is used by Access to update the fields on the "one" side of the relationship when the record source for a form is based on a multiple-table view or stored procedure. This property works in conjunction with the UniqueTable property, which establishes the updatable table on the "many" side of the relationship. To use the ResyncCommand property, you must construct an SQL statement that is identical to the one used for your form's record source, except that you must add a new WHERE clause that specifies the key columns of the table specified in the UniqueTable property with question marks ("?") in place of a specific value or parameter. The purpose of the ResyncCommand property is to determine how Access retrieves updated data after the data has been saved to the server database.

For example, let's suppose that you wanted to build a form in the State University database that displays all the courses along with information about the assigned instructors. To do this, you could write a stored procedure with an SQL statement similar to the following:

Copy Code
SELECT A.FirstName, A.LastName, B.*
FROM Instructors AS A RIGHT OUTER JOIN Courses AS B
ON A.InstructorID = B.InstructorID

If you create a new form that uses this stored procedure as its record source, because this statement involves a join, you will need to set the form's UniqueTable property to the Courses table. Because Access allows you to update fields only in the table specified by the UniqueTable property, when you open the form, you'll notice that you can modify only fields that come from the Courses table, not those that come from the Instructors table. Additionally, if you try to change the value of the InstructorID field, you'll notice that the change was accepted, but when you refresh or save the record, the instructor's first and last names are not updated to reflect the change. This behavior occurs because you also need to tell Access how to make the updates to the form by setting the ResyncCommand property to the SQL statement that specifies your record source with an additional WHERE clause that specifies the key field from the Courses table (CourseID) equal to a "?", as shown in the following statement:

Copy Code
SELECT A.FirstName, A.LastName, B.*
FROM Instructors AS A RIGHT OUTER JOIN Courses AS B
ON A.InstructorID = B.InstructorID
WHERE B.CourseID = ?

Note The CourseWithInstructorInfo form in the State University sample database demonstrates the concepts discussed in this section.
For more information about the ResyncCommand property, type ResyncCommand in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.

InputParameters
When you use an SQL statement or stored procedure that requires one or more input parameters as the underlying record source for a form, you must set the InputParameters property to the field that contains the value that is used to filter the records returned to the form. Alternatively, you can specify text in the field to prompt the user to enter a value.

Note Additional Access project form record sources are discussed in "Additional Record Sources" later in this chapter.
When you are using an SQL statement as the form's record source, the syntax for the InputParameters property is as follows:

Copy Code
FieldName DataType=[PromptingText]|FormFieldName

FieldName is the name of the field in your SQL statement that you are using as the parameter, and DataType is the data type of the parameter. You have the choice of either specifying prompting text that asks the user to enter the parameter value, or you can specify a field on the form (FormFieldName) as the parameter value.

For example, let's say that you want to create an Instructors form that prompts the user for the InstructorID value and then displays only that one record in the form. To do this, you would set the form's RecordSource property to the following SQL statement:

Copy Code
SELECT *
FROM Instructors
WHERE InstructorID = ?

Then you would set the form's InputParameters property to the following:

Copy Code
InstructorID int=[Enter the InstructorID:]

When the user first opens the form, the message box shown in Figure 3.4 will be displayed.



Figure 3.4. The Enter Parameter Value dialog box

Note The Instructors (SQL) form in the State University sample database demonstrates how to use an SQL statement with parameters as a record source.
When you are using a stored procedure as the form's record source, the syntax that is used in the InputParameters property is as follows:

Copy Code
ParameterName DataType=[PromptingText]|FormFieldName

ParameterName is the name of the actual parameter used in the stored procedure, including the @ prefix symbol, and DataType is the data type of the parameter. As when you are using an SQL statement as the form's record source, you have the choice of either specifying the prompting text that asks the user to enter the parameter value, or you can specify a field on the form (FormFieldName) as the parameter value.

For example, let's say that you want to create an Instructors form that prompts the user for the InstructorID value and then displays only that one record in the form. To do this, you would set the form's RecordSource property to the name of the stored procedure that retrieves the instructor name, which in this case is GetInstructor. The GetInstructor stored procedure is as follows:

Copy Code
CREATE PROCEDURE GetInstructor

(
@InstructorID int
)

AS

SELECT *
FROM Instructors
WHERE InstructorID = @InstructorID

RETURN

Then you would set the form's InputParameters property to the following:

Copy Code
@InstructorID int=[Enter the InstructorID:]

Note The Instructors (Stored Procedure) form in the State University sample database demonstrates how to use a stored procedure with parameters as a record source.
For more information about the InputParameters property, type InputParameters in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.

Updating Data
Related to how Access uses snapshots to cache client-side recordsets is the question of how updates to the data are handled. The update process is managed through the use of an updatable snapshot, which is simply a local copy of the recordset that can be updated. The following sections discuss various issues involving the use of updatable snapshots.

General rules for updating data
The way Access updates data in an Access project differs in a number of ways from the way it updates data in an .mdb file.

The following rules apply when you are updating data from an Access project:

To update any column (including the primary key column) in a single table or base table of a join, you must have a unique column or combination of columns defined as a PRIMARY KEY constraint, a UNIQUE constraint, or any unique index on that table.
You can update data based on a one-to-many join in a form, but only for columns from the "many" side of the join. When you open a view or run a stored procedure that contains a join in Datasheet view, the data is read-only. However, you can update data from a form, even if it has a view or stored procedure as a record source, if you set the form's UniqueTable property (see the following section, "Working with Updatable Snapshots").
To update a view or stored procedure, you must have UPDATE, INSERT and DELETE permissions on the base table(s) referenced in the view or procedure, as well as SELECT permission on the view or EXECUTE permission on the procedure.
You can't update data derived from a self-join (such as the relationship between all employees [EmployeeID] and the managers they report to [ReportsTo] in the Employees table in the Northwind SQL Access project file [NorthwindCS.adp] sample application) under any circumstances.
Additionally, the following rules apply to updating data in both Access databases (.mdb files) and Access projects:

You can't update a form or datasheet if the SQL statement underlying the record source contains a GROUP BY or COMPUTE clause.
You can't update columns based on expressions (such as
"price * quantity AS extended_price"
).
You can't update a form or datasheet based on a many-to-many join.
Working with updatable snapshots
For an updatable snapshot that has been created for a one-to-many join operation (whether in a view, row-returning stored procedure, or SQL statement), an Access project supports inserting, deleting, and updating data only in the columns of the table that is specified by using the UniqueTable property of a form. The UniqueTable property specifies the table in the join whose rows each correspond to, at most, one row in the set of rows returned for the joined tables. For example, in a view that joins the Customers and Orders tables, Orders is the unique table because one row from the Orders table corresponds to, at most, one unique row in the view. By contrast, one row in the Customers table can correspond to multiple rows in the view. Put more simply, the UniqueTable should specify the "many" side of a join, and only that table's columns will be updatable; Access doesn't allow you to update columns on the "one" side of the join.

You can see this behavior by opening the Northwind SQL Access project file sample application (NorthwindCS.adp), selecting the view named Alphabetical List of Products, and then clicking the AutoForm button on the toolbar to create a simple form. At this point, none of the columns on the form are editable and the New Record button is disabled. To make the form updatable, you must specify which of the tables in the view is the unique table. The Alphabetical List of Products view joins the Categories and Products tables, and the Products table is the "many" side of the join, so it should be designated as the unique table. To do this, switch the form to Design view, display the form's property sheet, and on the Data tab, set the UniqueTable property to the Products table. When you switch the form back to Form view, all columns except the CategoryName column (the only column displayed from the Categories table) are editable, and you can now add or delete rows.

In an updatable snapshot, you see current values in an edited or newly inserted row only after it has been successfully saved. For example, the Orders table in the Northwind SQL Access project file sample application contains an identity column as its primary key. When you insert a row into this table, the new OrderID value appears after you save the row or move to another row (which implicitly saves the row). Similarly, in a form based on a view joining Customers and Orders, if you update the OrderID column to a different CustomerID column value, the Address column will not show the address of the new customer until after the row has been saved.

Additional Record Sources
In addition to using a table as a record source as we did in the first exercise, you can also use a view or a stored procedure. Both views and stored procedures can be developed within the Access environment, but they are stored in the MSDE or SQL Server database file (.mdf) on the server, not in the Access (.adp) file.

Note It is also possible to specify or change the record source for a form in Visual Basic for Applications (VBA) code by setting the form's Recordset property to point to an ActiveX Data Objects (ADO) Recordset object. For information about how to do this, see "The Form Recordset Property" later in this chapter.
Views
As mentioned in "Chapter 1: Understanding Microsoft Access 2000 Client/Server Development," a view is similar to a SELECT query in a Microsoft Jet database application in that it is a result set of data retrieved from one or more tables. When you bind a form to a particular view, the result set is updatable. Using a view allows you to filter particular columns from the result set so that you only access the columns that you need to. It also provides a level of abstraction from the base tables within the database, thus allowing you to set permissions so that the only way to gain access to the base tables is through views.

To create a view, you use the Query Designer by clicking Views under Objects in the Database window and then clicking New. The Query Designer in Access projects has a very similar look and feel to the query Design window in Access databases. The Query Designer allows you to specify the tables and views you want to build a view from, the fields from those sources, and additional criteria to further refine the results returned. In addition, if you open the SQL pane by clicking the SQL button on the toolbar, you can see the SQL statements generated by the Query Designer, or you can enter SQL statements directly instead of building the view visually.

Note For a look at the Access project Query Designer, see Figure 1.5 in "Chapter 1: Understanding Microsoft Access 2000 Client/Server Development."
For more information about views, type Working with Views in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search, or see SQL Server Books Online January 2004 Update, and search for views, overview on the Index tab.

Note The material in SQL Server Books Online applies to MSDE as well as SQL Server, even though SQL Server Books Online only occasionally mentions MSDE.
Stored procedures
Stored procedures are perhaps one of the most powerful features of SQL Server and MSDE databases. They allow you to use a mixture of ANSI-92 SQL syntax, control-of-flow programming statements, and built-in functions to build precompiled procedures that are stored in the server database. This allows you to encapsulate some of the more database-intensive programming logic at the server so that your client application can simply manage calling the stored procedures, without having to be involved in how the results are produced.

You have already seen some stored procedure programming in "Chapter 2: Implementing Microsoft Access 2000 Client/Server Databases," when we created triggers, and earlier in this chapter when we used a stored procedure as the record source for a form. In "Transact-SQL Programming in Access Projects" later in this chapter, we will take a closer look at some of the things that you can do with Transact-SQL and stored procedures.

For more information about stored procedures, type stored procedures in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search, or see SQL Server Books Online and search for stored procedures, overview on the Index tab.

Creating the Courses Form
Now that we have discussed the different types of record sources that can be used for Access project forms, let's apply this knowledge to the State University sample application.



Create the Courses Form

In this exercise we will build a view to the Courses table, and then build a form that uses that view as its record source.

In the Database window, click Views under Objects, then click New to open the Query Designer.
Click the SQL button on the toolbar to open the SQL pane, and then enter the following SQL statement:
Copy Code
SELECT *
FROM Courses

Close the Query Designer and save the view as vwCourses.
In the Database window, click Forms under Objects, and then make a copy of the Template form and rename it Courses.
Select the newly created Courses form, and click Design on the Database window toolbar.
On the View menu, click Properties to open the property sheet for the Courses form.
In the Record Source property box, click the drop-down arrow and select vwCourses. The Courses field list will automatically be displayed.
In the Courses field list, click the CourseID field, hold down the SHIFT key, and then click the EndTime field to select all fields in the field list. Drag all the fields from the field list to the Detail section of the Courses form.
Arrange the fields in any way you like and when done, close the Courses form and save your changes.
You can display the newly created Courses form by selecting the form in the Database window and clicking Open on the Database window toolbar. Your form should look similar to Figure 3.5.



Figure 3.5. The Courses form

Note The Courses form above shows the use of combo boxes for the Instructors and Quarter fields. You can do this by using the same techniques you would use when creating combo boxes in Access (*.mdb) databases.
Creating and Using Reports
Similar to forms, reports in Access projects work the same as when they are in an Access (*.mdb) database, but there are two additional report properties that you can use to filter the data that is displayed in the report. In addition, you can dynamically alter the appearance of your report by using a new feature of Access 2000 called conditional formatting.

Client/Server Report Properties
In a client/server application, you will want to reduce network load and handle the majority of the data processing on the server. Therefore, you should filter the data displayed in reports as much as possible, thereby reducing network traffic and improving performance. To achieve this goal, you can use the InputParameters and ServerFilters properties of Access project reports. These two properties are available in the property sheet for a report, as shown in Figure 3.6.



Figure 3.6. The Report property sheet showing additional properties for Access projects

The following sections outline these properties and what they can be used for.

InputParameters
The InputParameters report property behaves the same as the InputParameters form property. It allows you to prompt the user for information that you can use to filter the result set that is returned by the server and displayed on the report. The syntax used for the InputParameters report property is the same as the syntax used for the InputParameters form property. For details about how to use this property, see "InputParameters" under "Client/Server Form Properties" earlier in this chapter.

ServerFilter
The ServerFilter report property behaves the same as the ServerFilter form property. It allows you to filter the data that is retrieved from the database server and displayed in the Access project report. You use the same syntax for the ServerFilter property as you do to specify criteria in the WHERE clause of a SQL statement. For details about how to use this property, see "ServerFilter" under "Client/Server Form Properties" earlier in this chapter.

Using Conditional Formatting
A great new feature of Access 2000 that is available in both Access databases and Access projects is conditional formatting. This feature allows you to specify expressions that are evaluated against the data in your report and, based on the outcome of those expressions (or conditions), to dynamically alter the appearance of the data. Conditional formatting can be set based on:

The value in a control
An arbitrary expression that references another control
A user-defined VBA function
To use conditional formatting, select the controls on your report that you want to be dynamically altered, and then click Conditional Formatting on the Format menu. This will display the Conditional Formatting dialog box, as shown in Figure 3.7.



Figure 3.7. The Conditional Formatting dialog box

In the above dialog box, you specify the criteria to be used in your condition, and the formatting that will take place once the condition is met. In the following exercise, "Creating the Student Transcript Report," we will build a condition for a report.

For more information about conditional formatting, type conditional formatting in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.

Creating the Student Transcript Report


Create the Student Transcript Report

In this exercise we will build a very simple Student Transcript report.

In the Database window, click Reports under Objects, and then click New. In the New Report dialog box, select Design View, and then click OK.
On the View menu, click Properties to open the report's property sheet.
Set the RecordSource property to the CourseEnrollment stored procedure. The CourseEnrollment field list will automatically be displayed.
Set the InputParameters property to:
Copy Code
@StudentID int = [Enter the StudentID:]

Set the Caption property to Student Transcript, and then close the property sheet.
Drag the CourseID, Name, Quarter, and Year fields from the field list to the Detail section of the report.
Delete the labels for the fields and create new labels for them in the Page Header section of the report.
Create a text box in the Report Header section of the report and set its ControlSource property to:
Copy Code
=[FirstName] & " " & [LastName]

Add any other items (such as footers) that you may need.
Select all of the controls in the Detail section of the report, and then click Conditional Formatting on the Format menu.
In the Conditional Formatting dialog box, set Condition 1 to Expression Is, and then type the following expression in the text box:
Copy Code
[Year]>=Format(Date(),'yyyy')

Set the formatting of the condition to bold by clicking the Bold button, and then click OK to save the conditional formatting.
Close and save the report as Student Transcript.
To test your report, select the report in the Database window and then click Preview on the Database window toolbar. When prompted, enter a student ID. Your report should be similar to the one shown in Figure 3.8.



Figure 3.8. The Student Transcript report

Programming in Access Projects
This section provides an overview of the Transact-SQL language, the native SQL dialect of SQL Server and MSDE, as well as the various Automation programming models you can use to work with application and data objects in an Access 2000 client/server application by using VBA. It is beyond the scope of this article and the State University sample application to provide extensive detail, but we will point out many of the issues that are unique to programming in Access projects, and provide references to important sources of additional information.

Transact-SQL Programming in Access Projects
The Transact-SQL language is the native SQL dialect used by both SQL Server and MSDE. SQL is a standard interactive and programming language for working with relational databases. Standards for SQL have been defined by both the American National Standards Institute (ANSI) and the International Standards Organization (ISO). Transact-SQL supports the Entry Level of ANSI SQL-92, the latest SQL standard (published in 1992), and also contains proprietary extensions to the standard language. For more information about the SQL standard, see the JCC SQL Standards Page.

Transact-SQL is a powerful database programming language that can be used to perform a broad array of tasks in SQL Server and MSDE, but its primary functions fall into three areas:

Data definition language (DDL)—Statements that are used to create the objects that contain and organize relational data: tables, indexes, and relationships.
Data manipulation language (DML)—Statements that are used to retrieve, add, modify, and delete data in the relational database tables
Administration—Statements that are used to define access control by creating security groups and user accounts and defining their differing levels of access to data in the database. Transact-SQL can also be used to automate tasks such as backing up, dropping (deleting), or restoring databases, configuring replication, and other administrative tasks.
Access itself and the OLE DB components it uses to connect to SQL Server and MSDE databases use Transact-SQL to drive most interactions with the database. Users and developers can enter Transact-SQL code to work with SQL Server and MSDE databases from Access projects in the following ways:

In the SQL pane of the Query Designer to create views.
In the Stored Procedure Designer to create stored procedures.
In the Triggers for Table: TableName dialog box to create triggers.
In the RecordSource property of a form or report to specify its underlying data source.
In ADO code and in code for Automation object models that can use Transact-SQL to perform DDL, DML, and administrative functions.
Note Depending on where you write the Transact-SQL code and what database objects it is working against, there are limitations on what Transact-SQL statements you can use. For example, when you create triggers, you cannot use statements that alter, create, or drop (delete) database objects. For information about the appropriate Transact-SQL code for a given task, see SQL Server Books Online.
Many other operations in Access projects generate and use Transact-SQL code to work with the database, such as when you use the tools for designing tables, views, and database diagrams. Similarly, when users work with reports, datasheets, forms, and data access pages, Transact-SQL is used to display, add, modify, or delete records.

There are many examples of writing Transact-SQL statements throughout this series of articles. The Microsoft Access Help system provides an alphabetic reference of Transact-SQL keywords and SQL Server/MSDE error messages. To read these topics, you must display an Access Help topic, click the Show button, and then click the Contents tab. The location of these topics in the table of contents varies depending on whether you've installed the Office Update Answer Wizard Extension for Office 2000 from http://officeupdate.microsoft.com/2000/downloadDetails/ouawe.htm.

If you haven't installed the Answer Wizard extension, only three books are displayed in the table of contents, and you can click the Transact-SQL Help book or the SQL Server Error Messages Help book. If you have installed the Answer Wizard Extension, additional sets of Help topics are available, and the Transact-SQL and error messages topics are somewhat buried. To locate these topics, scroll down and click the Working with Microsoft Access Projects book, and then click the Working in Microsoft Access Projects book, and finally click either the Transact-SQL Help or SQL Server Error Messages Help books.

The Transact-SQL and error messages Help topics are actually a subset of the much more extensive SQL Server reference called SQL Server Books Online, which includes additional information about using Transact-SQL. SQL Server Books Online is installed with SQL Server itself, but you can download it from SQL Server Books Online January 2004 Update. You can also read SQL Server Books Online on the Web in the MSDN™ Library as part of the Data Access Services SDK. In the Data Access Services SDK, SQL Server Books Online is called the Microsoft SQL Server Programmer's Toolkit.

VBA Programming in Access Projects
VBA programming tasks in Access projects falls into three general areas that relate to the fact that you are working with a client/server application:

Data access programming—Working with the data objects that are part of the server-side of your application; that is, the database itself: tables, views, and stored procedures.
Access-specific programming—Working with objects that are part of the client side of your application; that is, the objects that are actually stored in your .adp file: forms, reports, links to data access pages, macros, and modules. The Access programming model also provides ways of working with the connection Access itself uses to read and work with data, and provides ways of retrieving information about the objects that contain data, but does not provide ways of working directly with the data those objects expose.
Server-management programming—Working with the server itself to perform administrative tasks such as backing up and restoring databases, modifying security settings, and managing replication tasks.
Data access programming
VBA data access programming in Access projects can be accomplished by using two programming models:

ActiveX Data Objects (ADO)
ActiveX Data Objects Extensions for DDL and Security (ADOX)
The following sections briefly describe these programming models and provide pointers to additional resources.

ADO

ADO is a data access interface used to communicate with OLE DB-compliant data sources, such as SQL Server and MSDE. Data applications can use ADO to connect to, and retrieve, manipulate, and update data from SQL Server and MSDE. The most common operations when you are using ADO to work with SQL Server and MSDE databases are:

Opening connections to MSDE and SQL Server databases.
Opening and working with records by using Recordset objects.
Executing parameterized stored procedures by using Command objects.
Executing ad-hoc Transact-SQL statements by using Command objects.
Sinking to ADO events.
Handling ADO errors by using the Errors collection.
The "Access-Specific Programming" section later in this chapter demonstrates some features of ADO that are specific to objects in the Access programming model.

For information about using ADO with SQL Server and MSDE, see the "ADO and SQL Server" book, which is contained in the "Building SQL Server Applications" book in SQL Server Books Online.

For reference information about ADO, see the ActiveX Data Objects Help file, which by default is located at C:\Program Files\Common Files\System\ado\ado210.chm.

For a general introduction to ADO, see Chapter 14, "Working with the Data Access Components of an Office Solution" in the Microsoft Office 2000/Visual Basic Programmer's Guide.

For information about technical issues, known bugs, and limitations of ADO and other parts of the MDAC, see the MDAC Readme file, which by default is located at C:/Program Files/Common Files/System/ado/MDACReadme.htm.

ADOX

ADOX is an extension to the ADO objects and programming model. It exposes additional objects for creating, modifying, and deleting schema objects, such as tables and procedures. It also includes security objects to maintain user accounts and groups and to grant and revoke permissions on objects. However, the Microsoft OLE DB Provider for SQL Server does not support many ADOX features. The unsupported features are listed below by object or collection:

Catalog object: Create method.
Tables collection: Properties for existing tables are read-only (properties for new tables can be read/write).
Views collection: Not supported.
Procedures collection: Append method, Delete method, Command property.
Keys collection: Append method, Delete method.
Users collection: Not supported.
Groups collection: Not supported.
Because ADOX is not well supported against SQL Server and MSDE databases, as an alternative you can perform a more complete set of DDL and administration programming tasks by executing Transact-SQL statements against ADO Command objects, or by using the SQL Distributed Management Objects (SQL-DMO) programming model.

For reference information about ADOX, see C:\Program Files\Common Files\System\ado\ado210.chm.

For information about technical issues, known bugs, and limitations of ADOX and other parts of the MDAC, see the MDAC Readme file, which by default is located at C:/Program Files/Common Files/System/ado/MDACReadme.htm.

For reference information about using SQL-DMO, see the "Developing SQL-DMO Applications" book, which is contained in the "Building SQL Server Applications" book in SQL Server Books Online.

Access-specific programming
This section provides an overview of the new objects, properties, and methods that were added to the Microsoft Access 9.0 object library to allow you to work with an Access project. For complete documentation of the Access object model, search Microsoft Access Visual Basic Reference Help for the item you want information about.

Creating, opening, and closing an Access project

While you typically create, open, and close an Access project by using the Access user interface, the Access 2000 object model has been extended to allow you to automate these operations from any application that supports VBA, or from an add-in or wizard. You use the OpenAccessProject, NewAccessProject, or CreateAccessProject methods to open or create an Access project file. The NewAccessProject method creates a new Access project file and causes it to become active in the current instance of Access, whereas the CreateAccessProject method simply creates an Access project file on disk. You use the OpenAccessProject method to open an existing Access project file, and the CloseCurrentDatabase method to close the current Access project file.

The following code sample from the modADO module in the State University sample application is designed to work in any Office application and demonstrates how to create and close a new Access project file.

Copy Code
Sub CreateADP()
Dim appAccess As Variant

' Create new instance of Microsoft Access.
Set appAccess = CreateObject("Access.Application.9")

appAccess.Visible = True

' Open database in Microsoft Access.
appAccess.NewAccessProject "c:\new.adp"

' Switch to the new instance of Access to view
' the new project file.
Stop

' Close open database.
appAccess.CloseCurrentDatabase

' Delete file because this is a demonstration.
Kill "c:\new.adp"

' Close and destroy instance of Application object.
appAccess.Quit
Set appAccess = Nothing
End Sub

Once you create and open a new Access project, you use other methods of the Application object to create new Access objects. For example, you use the CreateForm and CreateControl methods to create forms and controls on forms. You use the CreateReport and CreateReportControl methods to create reports and controls on reports.

For information about creating wizards and add-ins, see "Chapter 11: Add-ins, Templates, Wizards, and Libraries" in the Microsoft Office 2000/Visual Basic Programmer's Guide.

Working with objects in an Access project

In the current and previous versions of Access, you can use the Data Access Objects (DAO) Container and Document objects and their methods and properties to get information about forms, reports, macros, tables, fields, relationships, and queries in an Access database (.mdb). You can't use DAO to interact with the objects in an Access project file, however, Access 2000 has two new objects—CurrentData and CurrentProject—that contain collections of AccessObject objects, which are used in place of the Container and Document objects available through DAO in previous versions.

Access 2000 uses the CurrentData object to store collections of AccessObject objects that are administered by the database engine (MSDE or SQL Server); for example, database diagrams, stored procedures, tables, and views. Information about each collection of objects is stored in a collection, where each object is represented as an AccessObject object. For example, information about tables is contained in the AllTables collection and information about views is stored in the AllViews collection. To access the CurrentData object, you use the CurrentData property of the Application object.

Note AccessObject objects contain information about the objects that contain data, but do not provide access to the data itself. To work with data, you must use ADO. For more information about using ADO to work with data, see "Data Access Programming" earlier in this chapter.
These collections of AccessObject objects also allow you to work around certain limitations of ADOX. For example, when you are using the ADOX object library against the Microsoft OLE DB Provider for SQL Server (SQLOLEDB), you can't access information from the Views collection of the Catalog object. In an Access project, you can work around that limitation by using the AllViews collection of the CurrentData object, as demonstrated by the following code sample from the modADO module in the State University sample application.

Copy Code
Sub ListViews()
Dim objView As AccessObject

For Each objView In CurrentData.AllViews
Debug.Print objView.Name
Next

Debug.Print "Total Views: " & CurrentData.AllViews.Count
End Sub

For more information about using ADOX, see "Data Access Programming" earlier in this chapter.

You use the CurrentProject property of the Application object to get information about the Access-specific objects in an Access project file, such as data access pages, forms, macros, modules, and reports. The CurrentProject property returns the CurrentProject object, which contains collections of AccessObject objects that provide information about the Access-specific objects in the Access project. For example, the AllForms collection contains information about all the forms in a database, and the AllReports collection contains information about all the reports in the database. In addition to these collections, the CurrentProject objects also contains the name, path, and connection information for the Access project file itself.

An AccessObject object exposes the following properties that you can use to get information about an object: IsLoaded, Name, Parent, Properties, and Type. These properties are described in the following table.

Table 3.1. AccessObject object properties for the CurrentProject object

AccessObject property Description
IsLoaded A Boolean value indicating whether the object is currently loaded. This property is True when an object is open in any view.
Name A String value representing the name of the object.
Parent Returns the parent object for the specified object. For example, the parent of an item in the AllForms collection is the AllForms collection object. The parent of the CurrentProject object is the Application object.
Properties Returns an AccessObjectProperties collection, which contains all the custom properties associated with a particular AccessObject object. The Properties collection can store String or Long Integer values only.

For example, in previous versions of Access, if you wanted to display a list of all reports in an Access database (.mdb), you would use the Containers("Reports") collection in DAO to retrieve the names of the reports. To populate the lstReports control on the Reports form in the State University sample application, we used the AllForms collection of the CurrentProject object, as demonstrated by this event procedure in the form's Load event:

Copy Code
Private Sub Form_Load()
Dim strFill As String
Dim objReport As AccessObject

' Build a semicolon delimited string that contains
' a list of all the reports in the current project.
For Each objReport In CurrentProject.AllReports
strFill = strFill & objReport.Name & ";"
Next

' Make sure the RowSourceType property of the control
' is set to Value List.
lstReports.RowSourceType = "Value List"

' Fill the list by setting the RowSource property to strFill.
lstReports.RowSource = strFill

' Move focus to the list box.
lstReports.SetFocus

' Select the first item in the list box by default.
lstReports.Selected(0) = True
End Sub

Using the Connection property of the CurrentProject object in an Access project

When you use the Connection property of the CurrentProject object in an Access database (.mdb), Access returns a connection that uses the Microsoft Jet 4.0 OLE DB Provider. However, when you use the Connection property of the CurrentProject object in an Access project (.adp), Access doesn't return a direct connection to the SQL Server database. Instead it uses the Microsoft Data Shaping Service for OLE DB, which is an OLE DB service component that runs on top of a data provider. For Access project files, the data provider that is used in conjunction with the Microsoft Data Shaping Service for OLE DB is the Microsoft OLE DB Provider for SQL Server. This provider is required because updates to data in forms in an Access project require the Shaping Service. This provider is also required if you use the Recordset property of a form in an Access project to programmatically specify the form's data source. For more information about using the Recordset property of a form in an Access project, see "The Form Recordset Property" later in this chapter.

The fact that the Connection property returns a Connection object that uses the Microsoft Data Shaping Service for OLE DB does have certain consequences:

Recordset objects always use the client-side cursor engine
(CursorLocation=adUseClient)
. Even if you set the CursorLocation property of a Recordset object to adUseServer prior to creating a Recordset object by using the Execute method of a Connection object, the Execute method of a Command object, or the Open method of a Recordset object, you will always get a client-side cursor.
Note For more information about cursors and cursor engines, see Chapter 14, "Working with the Data Access Components of an Office Solution" in the Microsoft Office 2000/Visual Basic Programmer's Guide.
Recordset objects always use a Static cursor
(CursorType=adOpenStatic)
, regardless of what you set the CursorType property to when you are opening the Recordset object.
You can set the LockType property of a Recordset object to the adLockOptimistic, adLockBatchOptimistic, or adLockReadOnly constants. If you set the LockType property to adLockPessimistic, the setting is converted to adLockOptimistic. As with Recordset objects created with other connections, if you don't specify a setting for the LockType property, the property will be set to adLockReadOnly by default.
A Connection object established by using the Microsoft Data Shaping Service for OLE DB doesn't support all the ADOX operations that a Connection object established by using a direct connection through the Microsoft OLE DB Provider for SQL Server supports. In particular, operations on the Properties collection of the Column object are not supported.
Note The ADOX Views, Users, and Groups collections are not supported against SQL Server when you use connections through either the Microsoft Data Shaping Service for OLE DB or the Microsoft OLE DB Provider for SQL Server.
The Properties collection of a Connection object doesn't support the same properties as a direct connection. For example, the DBMS Version property is not supported.
If the CommandType property of a Command object isn't specified, the connection will not try to open the Command object as a table if the CommandText property doesn't refer to a stored procedure. You can avoid this problem either by specifying an SQL statement to open the table (for example,
SELECT * FROM Students
), or by explicitly specifying the CommandType as adCmdTable when you are opening the Command object, as shown in the following procedure from the modADO module in the State University sample application:

Copy Code
Sub OpenCurrentProjectConnection()
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command

' Specify properties of the Command object.
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "Students"
.CommandType = adCmdTable
End With

' Open the Recordset object by using the Command object.
Set rst = cmd.Execute

' Display connection string.
Debug.Print rst.ActiveConnection

' Close and destroy objects.
rst.Close
Set cmd = Nothing
Set rst = Nothing
End Sub

You can also avoid this and any other limitations that you might encounter when you are using the Microsoft Data Shaping Service for OLE DB by creating a separate connection that doesn't use the Shaping Service. You can create a separate connection by using the BaseConnectionString property of the CurrentProject object. The BaseConnectionString property returns a connection string that uses only the Microsoft OLE DB Provider for SQL Server (Provider=SQLOLEDB.1). The following procedure from the modADO module in the State University sample application shows one way to open a Recordset object by using the BaseConnectionString property:

Copy Code
Sub OpenBaseConnectionString()
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command

' Specify properties of the Command object.
With cmd
.ActiveConnection = CurrentProject.BaseConnectionString
.CommandText = "Students"
End With

' Open the Recordset object by using the Command object.
Set rst = cmd.Execute

' Display connection string.
Debug.Print rst.ActiveConnection

' Close and destroy objects.
rst.Close
Set cmd = Nothing
Set rst = Nothing
End Sub

For more information about the features of the Microsoft Data Shaping Service for OLE DB, search ADO Help for "data shaping."

The Form Recordset property

In an Access project file, you can use the Recordset property of an Access Form object to specify an ADO Recordset object that will be used as the form's record source. If you want the record source to be updatable, you must do the following three things:

You must specify that the Connection object that is used to open the Recordset object is using the Microsoft Data Shaping Service for OLE DB provider. To do this, you must set the Provider property in the connection string that is used to open the Connection object to MSDataShape.
You must specify that the Data Provider property in the connection string is using the Microsoft OLE DB Provider for SQL Server (
Data Provider=SQLOLEDB
).
You must set other connection string properties as described in Table 3.2.
Table 3.2. Connect string property settings for an Updatable record source

Connection string property Description
Provider This property must be set to MSDataShape to specify the Microsoft Data Shaping Service for OLE DB.
Data Provider This property must be set to SQLOLEDB to specify the Microsoft OLE DB Provider for SQL Server. If you use any other OLE DB provider, it will return a read-only connection.
Data Source This property specifies the server name, which is the same as the network name of the computer on which SQL Server is running. To view this name, open Windows Control Panel, click the Network icon, and read the value in the Computer Name box on the Identification tab. If you are working with a server that is installed on the same computer as the one running your Access project, you can set the Data Source property to (local).
Initial Catalog This property specifies the name of the database to open.
User ID If you are using SQL Server Authentication, use this property to specify the user account name (such as sa, the default user account). Don't set this property if you are using Windows NT Authentication.
Password If you are using SQL Server Authentication, use this property to specify the password for the user account specified with the User ID property. Don't set this property if you are using Windows NT Authentication.
Trusted_Connection If you are using Windows NT Authentication instead of SQL Server Authentication, set this property to Yes.

To demonstrate how to use the Recordset property to bind the recordset of a form to an ADO Recordset object in an Access project, let's create a form in StateU.adp that will display data from the Customers table in the back-end database for the Northwind Traders sample application.

Note The following exercise assumes that you have installed the Northwind Traders sample Access project (NorthwindCS.adp) and that it is available on your local server. For information about installing the Northwind Traders sample Access project, see "Installing the Northwind Traders Sample Microsoft Access Project" in "Chapter 1: Understanding Microsoft Access 2000 Client/Server Development."


Create the Customers Form

In the Database window, click Forms under Objects.
Make a copy of the Template form and name it Customers (Recordset Property).
Select the newly created Customers form and click Design on the Database window toolbar.
On the View menu, click Toolbox, and then use the toolbox to add four text boxes to the form.
Click the first text box, and then click Properties on the View menu.
Set the Name property of the control to txtContactName. Repeat this process for the remaining text box controls, setting their Name properties to txtCompanyName, txtAddress, and txtCity.
Set the Caption property of each label control to correspond to the text box control it is attached to: Contact Name:, Company Name:, Address:, and City:.
To enter code behind the form, click Code on the View menu.
In the Object Box in the Code window, click Form to create an empty Form_Load event Sub procedure. Paste the following code in the Sub procedure:
Copy Code
Private Sub Form_Load()
Dim rst As ADODB.Recordset
Dim strConnect As String
Dim strSQL As String

' Create connection string to connect to NorthwindCS database
' using the Microsoft Data Shaping Service for OLE DB provider.
strConnect = "Provider=MSDataShape;Data Provider=SQLOLEDB;" _
& "Integrated Security=SSPI;Data Source=(local);" _
& "Initial Catalog=NorthwindCS"

' Create SQL statement to select customers from Spain.
strSQL = "SELECT ContactName, CompanyName, Address, " & _
"City FROM Customers WHERE (Region = 'SP')"

' Initialize and open the Recordset object.
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = strConnect
.LockType = adLockOptimistic
.Open strSQL
End With

' Set form's Recordset property to use the new Recordset
' object.
Set Me.Recordset = rst

' Set the ControlSource property of the controls on
' the form to bind them to fields in the Recordset object.
txtContactName.ControlSource = "ContactName"
txtCompanyName.ControlSource = "CompanyName"
txtAddress.ControlSource = "Address"
txtCity.ControlSource = "City"
End Sub

Close the Code window, and then save your changes.
To run the form, click Form View on the View menu.
The code added to the Load event of the Customers form in step 9 opens an ADO Recordset object by passing a connection string (this string must specify the Microsoft Data Shaping Service for OLE DB provider) to the ActiveConnection argument of the Open method. The code then assigns the Recordset property of the form to that Recordset object and binds the text box controls on the form to the fields in the Recordset object. Because the Recordset object was opened by using the Microsoft Data Shaping Service for OLE DB provider, the form's recordset is updatable, allowing you to add, delete, or modify the displayed records.

As noted earlier in this chapter in "Using the Connection Property of the CurrentProject Object in an Access Project," a connection established through the Microsoft Data Shaping Service for OLE DB always uses the client-side cursor engine (
CursorLocation=adUseClient
) and returns a Static cursor (
CursorType=adOpenStatic
). Additionally, you can set the LockType property to one of these constants only: adLockOptimistic, adLockBatchOptimistic, and adLockReadOnly. Setting the property to adLockPessimistic will return adLockOptimistic.

Note If you want to assign an updatable Recordset object connected to the current database to a form's Recordset property, you can open the Recordset object by using the Connection property of the CurrentProject object, as shown in the following line of code:
Copy Code
rst.ActiveConnection = CurrentProject.Connection

This code will work correctly because the Connection property will return a connection string that specifies the Microsoft Data Shaping Service for OLE DB provider.
For more information about opening ADO Recordset objects, see Chapter 14, "Working with the Data Access Components of an Office Solution" in the Microsoft Office 2000/Visual Basic Programmer's Guide.

Server-management programming
To use VBA to perform server-management tasks against SQL Server and MSDE, you use the SQL Distributed Management Objects (SQL-DMO) programming library. SQL Distributed Management Objects (SQL-DMO) is a collection of objects for database and replication management.

With SQL-DMO, you can automate:

Repetitive or commonly performed SQL Server and MSDE administrative tasks, such as backing up and restoring databases.
SQL Server and MSDE object creation and administration.
Creation and administration of SQL Server Agent jobs, alerts, and operators.
SQL Server and MSDE replication installation and configuration.
For reference information about using SQL-DMO, see the "Developing SQL-DMO Applications" book, which is contained in the "Building SQL Server Applications" book in SQL Server Books Online.

Administration in Access Projects
For the most part, administering an Access client/server application is no different from administering any other SQL Server or MSDE application. The primary difference hinges on what administrative tools are available to you:

If you have an installation of MSDE only, and you do not have an installation of SQL Server available to you, your choice of tools for administering the application is limited to the small set of tools installed with MSDE, those available in the Access user interface, or what you create yourself by using Transact-SQL or SQL-DMO code.
If you have an installation of SQL Server, you have a broader and more powerful selection of tools, the primary one being Enterprise Manager. In addition to working with SQL Server databases, Enterprise Manager can also be used to perform administrative tasks against MSDE databases.
The following sections provide an overview of the administrative tools available if you have an installation of MSDE and Access 2000 only. For information about Enterprise Manager and other tools installed with SQL Server, see SQL Server Books Online.

Administrative Tools
From a Microsoft Access project, you can perform the most common administrative tasks (such as backing up or restoring a database) on an MSDE database that is running on a local computer by using several database-utility commands that are available in the Access 2000 user interface. However, you cannot use these commands unless you have also installed MSDE on your computer.

The following table summarizes the commands available for performing administrative tasks against a local database in the Access 2000 user interface.

Table 3.3. Available administrative tasks for local databases

Administrative task Command
Back up your MSDE database Backup (Tools menu, Database Utilities)
Restore your MSDE database from a previous backup Restore (Tools menu, Database Utilities)
Delete your MSDE database Drop SQL Database (Tools menu, Database Utilities)
Manage replication configuration Synchronize Now (Tools menu, Replication)
Create Publication (Tools menu, Replication)

Pull Subscription (Tools menu, Replication)

Push Subscription (Tools menu, Replication)

Subscription Properties (Tools menu, Replication)

Publisher Properties (Tools menu, Replication)

Resolve Conflicts (Tools menu, Replication)

The Create Publication, Pull Subscription, Push Subscription, Subscription Properties, and Publisher Properties commands all start wizards that help you configure the publications and subscriptions between your MSDE server and other SQL Server or MSDE servers. These wizards have help information in their user interfaces or Help buttons that provide additional information. You do not use the Synchronize Now and Resolve Conflicts commands until after you have configured your subscriptions or publications.


In addition to the tools available through Access, when you install MSDE, it installs another set of tools that are available on the MSDE submenu (Windows Start menu, Programs submenu). The following table summarizes these tools and the administrative tasks you can perform with them.

Table 3.4. Additional administrative task tools provided by MSDE

Administrative task Tool
Specify the default network library and other configuration settings that are used by the client computer to communicate with SQL Server or MSDE installations on other computers. Client Network Utility
For more information about using this tool, click the tab you want to work with and then click the Help button on the tab.

Specify the network libraries that are used by the local SQL Server or MSDE to communicate with client computers. Server Network Utility
For more information about using this tool, click the tab you want to work with and then click the Help button on the tab.

Import, export, or transform data between SQL Server and MSDE installations, or between a variety of other data formats, such as Access, dBASE, Microsoft FoxPro, and Paradox databases, or Microsoft Excel and text formats. Import and Export Data
This command starts the Data Transformation Services Wizard, which has help information in its user interface. For additional information when you are using the Column Mappings and Transformations dialog box in this wizard, click the Help button. For additional information about the Data Transformation Services Wizard, see SQL Server Books Online and search for Data Transformation Services, overview on the Index tab. To see an example of importing data from an Access database into SQL Server or MSDE, see "Importing Data with the Data Transformations Services Wizard" in "Chapter 2: Implementing Microsoft Access 2000 Client/Server Databases."

Start, stop, or pause your MSDE server. Server Manager
This command starts the SQL Server Service Manager. The Server box shows the name of your local installation of MSDE (which under Windows 95 or 98 and Windows NT 4.0 is identical the Computer Name available from the Network icon in the Control Panel, and under Windows 2000 is available from the System icon in the Control Panel). You use this tool to start, stop, or pause the services that are part of MSDE, the most important being the database server itself, which is identified as MSSQLServer in the Services box. You can also select the Auto-start service when OS starts check box to make sure that MSDE is running whenever your computer starts up.


Managing Security
The security system in SQL Server and MSDE uses a system of user and group accounts, which can be defined in three ways: by integrating existing Windows NT or Windows 2000 security accounts, by creating native SQL Server security accounts, or by using a combination of both systems. These users and groups are then assigned permissions that define what data objects the user has access to, and what the user is allowed to do with those objects. This security system has much in common with the architecture of the user-level security system that is used in Access databases (.mdb), but it also has significant differences (such as the ability to integrate the use of Windows security accounts).

The Access 2000 user interface provides you with the same set of security tools that are available in SQL Server Enterprise Manager. To use these tools, open an Access project that is connected to the MSDE or SQL Server database you want to work with, point to Security on the Tools menu, and then click Database Security. Even though these security tools are invoked in a different way than are the security tools available in Enterprise Manager, they have exactly the same functionality. For a detailed discussion of the SQL Server security system and how to configure security, see the "Managing Security" book, which is contained in the "Administering SQL Server" book in SQL Server Books Online.

Important Unlike the other administration tools that Access 2000 provides to work with the back-end database of an Access project, the security tools can work against both local MSDE databases and remote MSDE and SQL Server databases. However, these Access 2000 security tools rely on components that are installed by MSDE, so you must install MSDE locally before these tools will work. Also, note that when MSDE is run on Windows 95 and 98, the tools don't support Windows NT/2000 integrated security accounts, but they do support native SQL Server security accounts.
Deploying Access Projects
You can deploy Access project applications in a number of ways:

You can distribute your Access project (.adp) file to users, and then give them instructions on how to first open the file and then use the Connection command on the File menu to connect to the server where your database (.mdf) file is located. For more information about using the Connection command, see Microsoft Access Help.
Note If you need to move your back-end database (.mdf) from an MSDE installation that is running on a local computer to an MSDE or SQL Server installation that is running on another computer before you distribute your Access project application, you can use the Data Transformation Services Wizard to do so. After installing MSDE, you can run the Data Transformation Services Wizard by using the Import and Export Data command, which is available on the MSDE submenu (Windows Start menu, Programs submenu). For more information about the Data Transformation Services Wizard, see SQL Server Books Online and search for Data Transformation Services, overview on the Index tab.
You can distribute your Access project (.adp) file and database (.mdf) file to users, along with instructions on where to copy the database (.mdf) file and how to first open the Access project file and then use the Connection command on the File menu to connect directly to the database file. This method is described in "Installing the Sample Application" in "Chapter 2: Implementing Microsoft Access 2000 Client/Server Databases."
If you are creating an Access project application where the MSDE installation, the database (.mdf), and the client application (.adp) all reside on the same computer, you can deploy the application by using a backup of the database. To back up the database, point to Database Utilities on the Tools menu, and then click Backup. After you've created the backup file, give users a copy of your Access project file (.adp) and the backup file (.dat), along with instructions on how to install MSDE (if it isn't installed already) and how to restore the database from the backup file.
Important This deployment approach will work only when both the Access project file, the MSDE database, and MSDE itself are all installed on the same computer. This configuration must be in place before you create the files for distribution. For this reason, before you create the distribution files, you must have MSDE installed locally, and the connection information in your Access project file must specify the server name as (local). To set the connection information, click Connection on the File menu in Access.
If you are creating an Access project application where the MSDE installation, the database (.mdf), and the client application (.adp) all reside on the same computer, you can give users the .adp and .mdf files along with instructions on how to install MSDE (if it isn't installed already) and how to copy and attach the database file by using the sp_attach_db system stored procedure. For details on the sp_attach_db system stored procedure, see SQL Server Books Online and search for sp_attach_db on the Index tab.
If you have Microsoft Office 2000 Developer, you can use the Package and Deployment Wizard to create an installation program that will install an Access project application and MSDE in a single operation. For information about deploying Access client/server applications with this wizard, see the white paper Creating and Deploying Microsoft Access Solutions with the Microsoft Data Engine (MSDE)
The Northwind Traders sample Access project (NorthwindCS.adp) uses VBA code in the Startup module (which is invoked by using a function call from the OnOpen event of the Startup form) to build the back-end database by using Transact-SQL code contained in the NorthwindCS.SQL script file, and then attaches the database to a local installation of MSDE or SQL Server. The VBA code in the Startup module uses a combination of Access, ADO, and SQL-DMO objects to perform these operations. Although it is beyond the scope of this article to provide details about how this process works, readers familiar with VBA and Transact-SQL will benefit from examining this code. For information about installing the Northwind sample Access project, see "Installing the Northwind Traders Sample Microsoft Access Project" in Chapter 1: Understanding Microsoft Access 2000 Client/Server Development.
Summary
This chapter has covered building forms and reports, programming, and administering and deploying Access 2000 projects. For more information about these subjects, see SQL Server Books Online and the list of resources in "Appendix 2: Additional Resources for Working with Microsoft Access 2000 Projects."

-----------------------------------------

source :http://msdn2.microsoft.com/en-us/library/aa139945(office.10).aspx