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

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

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

Ken Getz; Paul Litwin; Andy Baron

نمايش فراداده ، افزودن یک نقد و بررسی
افزودن به کتابخانه شخصی
ارسال به دوستان
جستجو در متن کتاب
بیشتر
تنظیمات قلم

فونت

اندازه قلم

+ - پیش فرض

حالت نمایش

روز نیمروز شب
جستجو در لغت نامه
بیشتر
لیست موضوعات
افزودن یادداشت
افزودن یادداشت جدید










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


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

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


  1. 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
  2. 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.


/ 232