About an Access project (ADP)

A Microsoft Access project (.adp) is an Access data file that provides efficient, native-mode access to a Microsoft SQL Server database through the OLE DB component architecture. Using an Access project, you can create a client/server application as easily as a file server application. This client/server application can be a traditional solution based on forms and reports, or a Web-based solution based on data access pages, or a combination of both. You can connect the Access project to a remote SQL Server database, a local SQL Server database, or a local installation of SQL Server 2000 Desktop engine.

Connects the Access project to the SQL Server database

Database objects stored in the SQL Server database

Database objects stored in the Access project

An Access project is called a project because it contains only code-based or HTML-based database objects: forms, reports, the name and location of data access pages, macros, and modules. These are the database objects that you use to create an application. Unlike a Microsoft Access database, an Access project does not contain any data or data definition based objects: tables, views, database diagrams, stored procedures, or user-defined functions. Instead, these database objects are stored in the SQL Server database.

To create your application and access the data, you connect the Access project to the SQL Server database by using the New command on the File menu to display the Data Link Properties dialog box or by using the Database Wizard.

You can connect to one of the following SQL Server databases:

• Microsoft SQL Server 2000 on Microsoft Windows 2000 or later

• Microsoft SQL Server 2000 Desktop Engine on Windows 2000 or later

Note  If you are connecting to a named instance of a SQL Server 2000 server, your Access project client computer must have Microsoft Data Access Components (MDAC) version 2.6 installed. You can install MDAC 2.6 by installing Microsoft SQL Server 2000 Personal Desktop Edition from the \MSDE2000 folder on the Microsoft Office 2003 CD-ROM.

Working with an Access project is very similar to working with an Access database. The process of creating forms, reports, data access pages, macros, and modules is virtually the same as that used to create an Access database.

Once you connect to an SQL Server database, you can view, create, modify, and delete tables, views, stored procedures, user-defined functions, and database diagrams by using the Database Designer, Table Designer, Query Designer, Query Builder, and SQL Text Editor. Although the user interface for working with these database objects is different from the equivalent database objects in an Access database, it is just as user-friendly. An Access project also contains many of the same wizards as an Access database, such as the Form Wizard, the Report Wizard, the Page Wizard, and the Input Mask Wizard. These Wizards help you to quickly create a prototype or simple application, and also make it easier to create an advanced application.

If your Access project is connected to an SQL Server 2000 database, you can use and take advantage of extended properties. Extended properties store additional attribute information about SQL Server database objects that are stored in the SQL Server database and provide added functionality for an Access project such as:

• Creating a lookup on a column.
• Displaying user-friendly validation text.
• Formatting data and Defining input masks.
• Using subdatasheets, saving sort order and filters, and saving datasheet appearance of tables, views, stored procedures, and inline user-defined functions.

Some extended properties are used by Access and are not visible through an Access project, such as a property which sets the column widths in a table datasheet. Other extended properties are available from the Database, Table, and Query Designers property sheets for views, single statement stored procedures (except column properties), and in-line functions. Because multistatement stored procedures, and table and scalar user-defined functions are edited in the SQL Text Editor, they don't support extended properties.

By default, SQL Server does not support extended property inheritance. A column in a view does not automatically have the same properties as the column in the base table. However, an Access project does support inheritance by copying the extended properties from table columns to view and function columns on creation, and by copying extended properties from table columns to form and report control properties.

Note  Changes to the extended properties of tables, views, stored procedures, and in-line functions are seen by all users of the database, because extended properties are stored in the SQL Server database. This means, for example, that if you change the format of a table datasheet, you are changing it for everyone who subsequently opens the table datasheet.