An Access project is an Access file (.adp) that provides a direct, efficient connection to a Microsoft SQL Server database through the OLE DB component architecture. Microsoft Access 2007 and 2010 allow you to create new Access project (.adp) files and work with existing Access projects that you created in earlier versions of Access. This article provides a brief explanation of Access projects, tells you how Access projects work in Access 2007 and 2010. It also covers how to create a new Access project in Access 2007 and 2010 and how to connect an Access project to an existing SQL Server database.
Note: Access 2010 is the last version of Access that supports working with Access project (.adp) files.
In this article
Overview of Access projects
Creating an Access project provides several advantages over linking to SQL Server tables or views. For example, in an Access project, you can make design changes to SQL Server objects, such as tables and views, and you can create, edit, and use other SQL Server features, such as database diagrams, stored procedures, and user-defined functions. In contrast, when you link to a SQL Server database, you cannot make design changes to any SQL Server objects, and you can only link to SQL Server tables and views. However, linking allows you to connect to multiple SQL Server databases, whereas an Access project (*.adp) file can only connect to a single SQL Server database.
For more information about linking to a SQL Server database, click the article Link to or import from an SQL Server database.
An Access project does not contain any data or data-definition-based objects. Instead, these objects are stored in the SQL Server database. However, working with an Access project is still very similar to working with an Access database. The process of creating forms, reports, macros, and modules are virtually the same as that used to create an Access database.
In Access 2007 and 2010, you can create new Access projects and use projects that were created in earlier versions of Access. In addition, you can take advantage of many of the new features of Access 2007 and 2010, such as the improved form and report design tools, the new macro designer, improved conditional formatting tools, and ribbon customization. However, certain newer Access features are not available in Access projects, such as attachments, multivalued fields, the Navigation Control, or the Web Browser Control. In addition, Layout View is not available for designing forms and reports.
Compatibility between Access and SQL Server versions
Access 2010 .adp files are compatible with SQL Server 2008 and earlier. Later versions of SQL Server may also be compatible with Access 2010 .adp files, but as a general rule of thumb, if you want to modify SQL objects from within an Access .adp file, the Access version must be higher than the SQL Server version. For example, you cannot modify objects in a SQL Server 2008 database by using Access 2007. Likewise, you cannot modify objects in a SQL Server 2005 database by using Access 2003.
Important: Before upgrading to a new version of Access or SQL Server, always test your Access project with the new version(s) to be sure all the features work correctly.
Data type and diagram support
Date/time data types
Access 2010 provides limited support for four new date/time data types that were added in SQL Server 2008:
-
TIME
-
DATE
-
DATETIME2
-
DATETIMEOFFSET
In an Access project, you can create and design tables and views that use these new data types. However, on forms and reports, fields of these data types behave as if they are text fields. As a result, you cannot use date formats or the date pickers with these data types.
Variable-length data types
The following variable-length data types can be used in Access 2010 projects:
-
VARBIN(MAX)
-
VARCHAR(MAX)
-
NVARCHAR(MAX)
However, you cannot add or modify fields that use MAX specifiers from within an Access project.
BIGINT data type
Access 2010 supports the SQL Server BIGINT data type. However, the Access user interface enforces a lower limit of -999999999999999999 (approximately -2^60) as opposed to the actual lower limit of -2^63. The upper limit in the Access user interface is 2^63-1, the same as in SQL Server.
Unsupported features
Data types
The following new SQL Server 2008 data types are not supported in Access projects:
-
GEOGRAPHY
-
GEOMETRY
-
HIERARCHYID
Access blocks design mode for tables or views that use these types.
Database diagrams (relationship view)
As a result of the three unsupported data types listed in the previous section, you cannot open SQL Server 2008 database diagrams in an Access project.
Recommendations for Access projects in Access 2010
We continue to support creating and working with .adp files in Access 2010. However, we recommend that developers use the SQL Server design tools provided in SQL Server Management Studio to design database objects such as tables, views and stored procedures, and then use the Access interface to design the forms, reports, macros and modules that connect to those objects. The tools provided by SQL Server provide the most powerful interface for creating SQL Server objects.
Create an Access project
Use this procedure to create a new Access project (.adp) file in Access 2007 or 2010.
-
On the File tab, click New. If you're using Access 2007, click the Microsoft Office Button and then click Open.
-
Do one of the following:Create an Access project in the default file location
-
Under Blank Database, type a name for the project in the File Name box using the .adp file name extension.
-or-
Create an Access project in a different file location
-
Under Blank Database, click Browse for a location in which to save your database.
-
In the File New Database dialog box, browse to the location where you want to save the project, or accept the default location.
-
Type a file name for the project in the File name box.
-
In the Save as type list, select Microsoft Office Access Projects (*.adp), and then click OK.
-
-
-
Under New Project, click Create.
Access prompts you with the following message:
Do you want to connect to an existing SQL Server database?
If you want to connect to an existing SQL Server database, click Yes and continue with step 4; otherwise, click No and skip to step 5 to create a new SQL Server database.
-
Connect to an existing SQL Server database
-
In the Data Link Properties dialog box, enter the required information for the following:
-
Server name
-
Information needed to log on to the server
-
Database name
-
-
Click Test Connection to verify that the settings are correct and that the connection succeeds. If the connection does not succeed, the Microsoft Data Link Error dialog box displays a message describing the problem. Click OK to close the message, check your settings in the Data Link Properties dialog box, and then click Test Connection again. Also, you might need to check with the SQL Server database administrator for the database you are connecting to, to make sure your settings are correct or that the computer is available.
-
After the Microsoft Data Link dialog box displays the message Test connection succeeded, click OK to close the dialog box, and then click OK to close the Data Link Properties dialog box. Access connects the project to the SQL Server database.
-
-
Create a new SQL Server database
-
On the first page of the Microsoft SQL Server Database Wizard, enter the following information:
-
The SQL Server computer you want to use for this database
-
Your logon information
-
A name for the SQL Server database
Note: To skip the connection process at this time, click Cancel. Access creates the project, but it is not connected to a SQL Server database. Before you can do anything with the project, you will need to connect it to a SQL Server database, which you can do by using the steps in the next section.
-
-
Click Next to continue. Access attempts to connect to the SQL server computer you specified. If Access cannot connect to the SQL server computer, it displays an error message describing the problem. The SQL server computer name or logon information might be incorrect, or you might not have the necessary permissions to create a database on the SQL Server computer that you specified. You might need to check with the SQL Server database administrator for the database you are creating, to make sure your settings are correct or that the computer is available.
-
After Access successfully connects to the SQL Server computer, the wizard displays the message: The wizard has all the information it needs to create your SQL Server database. Click Finish to continue. Access creates the SQL Server database and connects it to the project.
-
Connect an existing Access project to a different SQL Server database
Use the following procedure if you have an existing Access project that is not connected to a SQL Server database, or if you want to change the SQL Server computer or database to which your project is connected.
-
On the File tab, click Open. If you're using Access 2007, click the Microsoft Office Button and then click Open.
-
In the Open dialog box, select and open the Access project. A Microsoft Office Access Security Notice dialog box might appear. If you trust the source of the database, click Open to continue.
-
On the File tab, click Server, and then click Connection. (If you're using Access 2007, click the Microsoft Office Button, point to Server and then click Connection.) The Data Link Properties dialog box appears.
-
In the Data Link Properties dialog box, enter the required information for the following:
-
Server name
-
Information needed to log on to the server
-
Database name
-
-
Click Test Connection to verify that the settings are correct and that the connection succeeds. If the connection does not succeed, Access displays an error message describing the problem. Click OK to close the message, check your settings in the Data Link Properties dialog box, and then click Test Connection again. Also, you might need to check with the SQL Server database administrator for the database you are connecting to, to make sure your settings are correct or that the computer is available.
-
After the Microsoft Data Link dialog box displays the message Test connection succeeded, click OK to close the dialog box, and then click OK to close the Data Link Properties dialog box.
Access connects the project to the SQL Server database you specified.
