Access Cookbook

Dynamically Connect to SQL Server from an ADP

Problem

When you create a new ADP, you are prompted for connection information that is saved with the ADP. If you want to change it later, you need to choose File → Connection from the menu and manually input new connection information in the Data Link dialog. Since the users of your ADP may not know how to do that, they would be connecting to SQL Server using your security credentials, not their own. You’d like to create a project that automatically opens the Data Link dialog and prompts the users for their own connection information instead of displaying your connection information.

Solution

This solution involves creating an unbound ADP (an ADP that is not yet connected to a SQL Server database) and prompting the user to fill in the connection information by displaying the Data Link dialog.

Since connection information is saved with the ADP, you need to create a new ADP with no connection information.

Follow these steps to implement this functionality in your ADPs:

1. Create a new ADP by choosing File → New and clicking on Project (Existing Data), as shown in Figure 14-4.

   

   Figure 14-4. Creating a new ADP

2. Designate a location for the new project when prompted. When the Connection dialog opens, press Cancel. Do not fill in any connection information.

3. You will now have an empty project. You want to create a startup form like the one shown in ...