Create an Access project

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. You can connect an Access project to a remote SQL Server database, a local SQL Server database, or a local installation of the SQL Server Desktop engine.

Microsoft Office Access 2007 allows you to create new Access project (.adp) files, in addition to working 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 Office Access 2007, and then tells you how to create a new Access project in Access 2007 or connect an Access project to an existing SQL Server database.

What do you want to do?

Understand Access projects

Understand how to work with Access projects in Office Access 2007

Create an Access project

Connect an existing Access project to a different SQL Server database

Understand 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.

For more information about either importing or linking to SQL Server data, click the links in the See Also section.

Unlike an Access database, an Access project does not contain any data or data-definition-based objects. Instead, these database objects are stored in the SQL Server database.

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

After you connect to a SQL Server database, you can view, create, modify, and delete tables, views, stored procedures, user-defined functions, and database diagrams by using the Table Designer, Query Builder, and SQL Text Editor tools in Access. Although the user interface used for working with these database objects in an Access project is different from the user interface used for working with equivalent database objects in an Access database, the Access project user interface 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, and the Input Mask Wizard. These wizards can help you to quickly create a prototype or simple application, and they can also make it easier to create an advanced application.

Top of Page

Understand how to work with Access projects in Office Access 2007

The process for creating an Access project in Access 2007 is not as obvious as in earlier versions of Access. However, you can still create new Access projects and continue to use your existing ones in Access 2007. There are no file format differences between Access 2007 projects and those created in earlier versions of Access.

When you work with Access projects, you can take advantage of many of the new features of Access 2007, such as the Navigation Pane customization tools, the improved form and report design tools, and embedded macros. However, certain new features of Access 2007 are not available in Access projects, such as attachments, multivalued fields, or integration with other programs, such as Microsoft Office Outlook 2007 or Windows SharePoint Services 3.0. In addition, Layout View is not available for designing forms and reports.

Top of Page

Create an Access project

1. Click the Microsoft Office Button , and then click New.

   Alternatively, on the Getting Started with Microsoft Office Access page, under New Blank Database, click Blank Database.

2. 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.

3. 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.

4. Connect to an existing SQL Server database    

   1. 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

        

   2. 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.

   3. 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.

5. Create a new SQL Server database    

   1. 1. 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.

      2. 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.

      3. 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.

Top of Page

Connect an existing Access project to a different SQL Server database

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, use the following procedure.

1. Click the Microsoft Office Button , and then click Open.

2. 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.

3. Click the Microsoft Office Button , point to Server, and then click Connection.

   • The Data Link Properties dialog box appears.

   • 

4. In the dialog box, enter the required information for the following:

   • Server name

   • Information needed to log on to the server

   • Database name

5. 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.

6. 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.

Top of Page