Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 14.2 Dynamically Connect to SQL Server from an ADP

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:

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

Figure 14-5. The startup form for the ADP

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

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