Creating Access Projects (ADPs)

Now that you have SQL Server installed, you can return to the discussion of using Access Projects to work with SQL Server databases. To create a new Access Project for a SQL Server database, you first select the Office Button New. On the right side of the window, you will see the Blank Database section, as shown in Figure 10-5. Click on Browse (the folder icon to the right of the file name) and a window similar to the one shown in Figure 10-6 will be displayed.

Figure 10-5

Figure 10-6

In the Save As Type drop-down list, select the Microsoft Office Access Projects (*.adp) option, select a location for the Access Project, specify a file name, and press the OK button. You will be returned to the original window and press the Create button. You will be asked if you want to connect to an existing SQL Server database. Selecting Yes will allow you to create a new Access Project using an existing SQL Server database. Selecting No will create a new Access Project and a new SQL Server database. You will now look at each of these options in detail.

Using an Existing SQL Server Database

To create a new Access Project based upon an existing SQL Server database, select the Office Button New, browse for the location of the Access Project, and a window similar to that in Figure 10-6 is displayed, Provide a name, select the Microsoft Office Access Projects (*.adp) option, and then click on OK.

Click on the Create button and then click on the Yes button in the pop-up window. You are then prompted (as shown in Figure 10-7) to specify details necessary to connect to your SQL Server database.

Figure 10-7

In the example of Figure 10-7, a database called pubs on the goz_tablet1100\sqldev server is being used. The option to use integrated Windows security is checked, so no SQL Server user name or password has to be filled in.

Tip 

If you have trouble connecting to SQL Server using integrated Windows security, try specifying a user name of sa and a blank password, which may have been installed by default.

You can click the Test Connection button to test the connection to make sure it works. You can create the new Access Project based on this database by clicking OK. The new Access Project will look similar to the screen shown in Figure 10-8.

Figure 10-8

Using a New SQL Server Database

Let’s now take a look at how to create a new Access Project based on a new SQL Server database. For your convenience, I included a text file for download from Wrox.com that contains the code for the SQL Server objects created in the following Try It Out sections.

Try It Out-Creating an Access Project Using a New SQL Server Database

1. Select the Office Button New, browse for the location of the Access Project and a window similar to that in Figure 10-6 is displayed, Specify Ch10CodeExamples for the file name, select the Microsoft Office Access Projects (*.adp) option, and then click on OK. Click on the Create button and then click on the No button in pop-up window. A screen similar to the one shown in Figure 10-9 then appears.

   
   Figure 10-9

2. Specify the name of your SQL Server instance, or select one from the list. Specify whether to use a Trusted Connection or a specific SQL Server login account, depending on how security for your SQL Server instance is set up. Also, specify the name for the new SQL Server database as Ch10CodeExamplesSQL. Click the Next button.

3. A screen similar to Figure 10-10 will appear. Click the Finish button to create the new SQL Server database.

   
   Figure 10-10

How It Works

The wizard walked you through the steps of creating a new Access Project and a new SQL Server database. The end result of performing the preceding steps is that you now have an Access Project (Ch10CodeExamples.ADP) on your hard drive, and you have a new database called Ch10CodeExamplesSQL in your SQL Server database. At this point, the SQL Server database and Access Project are both empty. Let’s look at some examples of how to work with SQL Server objects from within Access so your project does not remain empty.