Recipe 14.2 Dynamically Connect to SQL Server from an ADP | Access Data Analysis Cookbook (Cookbooks)

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

14.2.2 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:

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

Designate a location for the new project when prompted. When the Connection dialog opens, press Cancel. Do not fill in any connection information.
You will now have an empty project. You want to create a startup form like the one shown in Figure 14-5.

Figure 14-5. The startup form for the ADP

This form has a button that allows users to change their connection information. However, you want to prompt them to connect to the SQL Server database the first time they connect, so place the following code in the Form_Load event:
```
Private Sub Form_Load( )     If Not CurrentProject.IsConnected Then         DoCmd.RunCommand acCmdConnection     End If End Sub
```
The code for the Connect button simply executes the same line of code a second time:
```
Private Sub cmdConnect_Click( )     DoCmd.RunCommand acCmdConnection End Sub
```

14.2.3 Discussion

The DoCmd.RunCommand statement allows you to execute almost any item that appears in the built-in Access menus, as shown in the Object Browser in Figure 14-6. In this case, you are invoking the Data Link dialog by using the acCmdConnection constant.

Figure 14-6. Constants used with the DoCmd.RunCommand statement

When the form loads, the CurrentProject's IsConnected property is checked. The first time the form loads, you want to prompt for connection information before proceeding. Once users type in their credentials, this information will be saved. Should the users ever want to change their connection information, the Connect button on the form will allow them to do so.

If you need to dynamically connect at runtime and don't want to save connection information, you can connect and disconnect in code by taking advantage of the CurrentProject.OpenConnection and CurrentProject.CloseConnection methods. To open a project, use OpenConnection, passing in your connection information as a string:

CurrentProject.OpenConnection strConnect

The connection string, strConnect, looks like this for integrated security against the Northwind database on the local server:

PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST SECURITY INFO=FALSE;INITIAL  CATALOG=Northwind;DATA SOURCE=(local)

The following connection string works for a SQL Server user named Dudley with a password of "password":

PROVIDER=SQLOLEDB.1;PERSIST SECURITY INFO=TRUE;USER  ID=Dudley;PASSWORD=password;INITIAL CATALOG=Northwind;DATA SOURCE=(local)

The sample project, 14-02code.adp, demonstrates this technique.