|[ Team LiB ]|
Recipe 14.2 Dynamically Connect to SQL Server from an ADP
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.
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:
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.
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:
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.
|[ Team LiB ]|