Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Thanked 0 Times in 0 Posts

    User Name & PW for MS Query (2003)

    I have an Excel workbook that is used to study jobs on a monthly basis. One workbook is for one job and each month a new tab is created via code (vba). In order to get the information I need to generate the report I am using MS Query to connect to our accounting database. This db is a Pervasive.SQL db. I have to run two separate queries to get all the info. When the user clicks "Update" the following code is run:

    <pre>Private Sub cmdUpdate_Click()
    Dim Msg1, Response, MyStr
    Msg1 = "You are about to Update the current Job's cost." & Chr(13) & _
    "Click 'Yes to continue or 'No' to cancel."
    Response = msgbox(Msg1, 4 + 64)
    If Response = vbNo Then
    Exit Sub
    End If
    Range("D5").Formula = Now()
    End Sub

    When the RefreshAll line is run, the user is prompted for their UserName & PW, but b/c I have two queries, the user is again prompted for UserName & PW. I am looking for a way to only have this happen once. If I create a from that has UserName & PW how could I use this to RefershAll and only be prompted once?


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: User Name & PW for MS Query (2003)

    I cannot find any way to specify login information for the Refresh / RefreshAll methods.
    As an alternative, you could set the Connection property of each QueryTable object anew, passing the username and password from the form.
    See Connection Property for some background info.
    You can see the current Connection property by typing the following in the Immediate window:

    ? ActiveSheet.QueryTables(1).Connection

    and pressing Enter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts