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 (OLE DB: A component database architecture that provides efficient network and Internet access to many types of data sources, including relational data, mail files, flat files, and spreadsheets.) 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 (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.), 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 (database: A collection of data related to a particular subject or purpose. Within a database, information about a particular entity, such as an employee or order, is categorized into tables, records, and fields.), an Access project does not contain any data or data definition based objects: tables (table: A database object that stores data in records (rows) and fields (columns). The data is usually about a particular category of things, such as employees or orders.), views (view (object): In an Access project, a type of query that is a virtual table based on an SQL SELECT statement. For example, a view might contain only 3 out of 10 available columns in a join of two tables, in order to limit access to certain data.), database diagrams (database diagram: A graphical representation of any portion of a database schema. It can be either a whole or partial picture of the structure of the database. It includes tables, the columns they contain, and the relationships between the tables.), stored procedures (stored procedure: A precompiled collection of SQL statements and optional control-of-flow statements that is stored under a name and processed as a unit. The collection is stored in an SQL database and can be run with one call from an application.), or user-defined functions (user-defined function: A query that takes input parameters and returns a result like a stored procedure. Types: scalar (multistatement; returns one value), inline (one statement; an updateable table value), and table (multistatement; table value).). 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 an Access project to a Microsoft SQL Server 2000 or SQL Server 2005 database by using Microsoft Access 2000, Access 2002, Access 2003, and Access 2007. However, when you use Access 2000, Access 2002, or Access 2003 to connect to SQL Server 2005, you cannot make design changes to SQL Server objects, and some features of SQL Server 2005 may not work correctly. To obtain full functionality when connecting an Access project to SQL Server 2005, connect by using Access 2007 or later.

 Note   If you are connecting to a named instance of a SQL Server 2000 or SQL Server 2005 server, your Access project client computer must have Microsoft Data Access Components (MDAC) version 2.71 or later installed. You can install MDAC 2.71 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 the wizard doesn't start

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.