Results 1 to 2 of 2
2007-03-30, 14:55 #1
- 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
Range("D5").Formula = Now()
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?
2007-03-30, 23:15 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 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:
and pressing Enter.