Access Cookbook - Ken Getz [292]
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
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.
14.3. Share an ADP from a Shared Network Folder
Problem
You want to share an ADP on a network. However, the second user who attempts to open the ADP gets an error message.
Solution
This solution involves opening the ADP using the /runtime switch.
Follow these steps to create a shared ADP:
Create a shortcut on each user's desktop. In the Properties dialog, enter information using the format shown in Figure 14-7.
Figure 14-7. Properties for creating a shortcut for a shared ADP
Copy the shortcut to each user's machine.
Discussion
ADPs are intended for single-user activity. Using the /runtime switch allows you to get around this limitation by opening the ADP as read-only.
In Access MDB databases, the Jet database engine allows multiple users to share a frontend database containing forms, reports, queries, and code, just as they can share backend databases that contain data. If necessary, the Jet engine can lock resources in the database to prevent multiple users from interfering with each other.
ADPs don't have the benefit of the Jet database engine, so Access has no way to handle multiple users of a single project file. Users can share SQL Server data, but usually each user needs her own ADP. You can get around this by using the /runtime command-line option to force the ADP to be read-only. In this case, Access detects that there is no danger of users interfering with each other because they can't change anything, so it allows multiple users to work with the same ADP.
14.4. Fill the Drop-Down Lists When Using ServerFilterByForm in an ADP
Problem
You have turned on the ServerFilterByForm property. However, when users open the form and select from the combo boxes, the only choices are Is Null and Is Not Null. How do you get the combo boxes to show a list of valid values for that field?
Solution
If you turn on the ServerFilterByForm property, your form will open in a special view that turns text boxes into combo boxes. This allows users to define their own server filters at runtime, which are then processed by SQL Server before the record source data is returned to the form. However, you'll often see only the values shown in Figure 14-8 when you expand one of the combo boxes.
Figure