Creating a Project File for an Existing SQL Server Back-End Database

3 4

To create a project file as a front end for an existing SQL Server database, follow the steps listed below. For this example, the SQL Server back end is the pubs sample database provided with SQL Server. On my system, this database is located in the F:\Microsoft SQL Server\MSSQL\Data folder; the location can differ depending on the version of SQL Server and your computer setup.

1. Click the Project (Existing Data) selection in the New section of the New File task pane.
2. In the File New Database dialog box that appears, give your new project a name, as shown in Figure 19-8, and then click Create. In this example, the project is named Publications.

   

   Figure 19-8. Give the new project a name in the File New Database dialog box.

3. In the Data Link Properties dialog box that appears, click the Connection tab and select the server and the database on the server, as shown in Figure 19-9. The Use Windows NT Integrated Security option generally works fine, so you don’t need to enter a user name or a password.

   

   Figure 19-9. You set connection options for the project in the Data Link Properties dialog box.

4. Click the Test Connection button to test the connection to the database. If the connection is good, you’ll see a Test connection succeeded message.
5. Click OK to open the new project with the SQL Server tables and queries. Figure 19-10 shows the Database window with the tables displayed.

   

   Figure 19-10. Tables from the pubs SQL Server database are listed in a new Access project.

You can now create forms, reports, and other objects using the familiar Access objects and methods, although there are a few special considerations to keep in mind.

See the sections "Using Forms in Access Projects," and "Using Reports in Access Projects," for more details on working with forms and reports in projects.)