Run Multiple Queries Without Having to Keep Selecting the Data Source?

[justinua]

Hello, I'm pretty new to VBA, and I've been able to set-up a macro so that I can run multiple queries (all of which come from different tables on the same server). My issue is that with each query I'm having to select the data source and login over and over again. This is despite the fact that it is the exact same thing. Is there a way to run all of these queries at the same time without having to select the data source each time?

I think it might have to do with the VBA code creating a new connection for every single query, but I am not knowledgeable enough to know for sure. The code I'm using for each one looks like the example below:


SQLString = Range("EXPrevCurrentString") ' Insert the name of the range/cell where the query is stored here

'Update the connection string with the server
ConnectionString = Replace(ConnectionString, "@ServerName", Server)

'Update the query string with the selected MarketID and FlowDate
SQLString = Replace(SQLString, "@MarketID", MarketID)
SQLString = Replace(SQLString, "@EXArchiveDate", EXArchiveDate)


'Query to get the data with the "Current" Tag
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=__________;UID=_________;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=________;DATABASE=_______" _
, Destination:=Range("$A$1")).QueryTable
.CommandText = SQLString
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_EXPrevCurrent"
.Refresh BackgroundQuery:=False
End With

Thanks and I appreciate any help you can give!

 

[williamgowtama]

It'd be better to put the data source's name on the sheet that you want to withdraw the data with. Follow this step below:
Excel Worksheet side:
Create the Worksheet that contains some cells that stores the datasource and the table that you want to process, and put the id and password there too. example:

A1 A2
Source "DATA SOURCE"
Table "Table name"
id "your id"
password "your password"
"SheetName"

Then on the VBA side:
right click the project, then add form, make a form that has:
id: "textbox1"
Password: :textbox2"

Button

then code the Button_Click to put the input of textbox1 and textbox2 to the id and password cells.

Hope this helps

Regards, William

 

[justinua]

It'd be better to put the data source's name on the sheet that you want to withdraw the data with. Follow this step below:
Excel Worksheet side:
Create the Worksheet that contains some cells that stores the datasource and the table that you want to process, and put the id and password there too. example:

A1 A2
Source "DATA SOURCE"
Table "Table name"
id "your id"
password "your password"
"SheetName"

Then on the VBA side:
right click the project, then add form, make a form that has:
id: "textbox1"
Password: :textbox2"

Button

then code the Button_Click to put the input of textbox1 and textbox2 to the id and password cells.

Hope this helps

Regards, William

I appreciate your response, but I still haven't been able to make this work. I also would prefer that the people that use the form do not have to enter their password, since they are using windows authentication and it works fine when I just enter in my user name.

When I run the macro using the code I pasted in my initial post, it works fine, I just have to keep selecting the data source and confirming my login, even though the data source is always the same and my login information is the same. The login information is already filled into the correct fields because of the code I have, so I am having to click "OK" over and over again. Does that make sense? Is there any way to manipulate my code so that I won't have to keep doing those two steps repeatedly?

Let me know if you need any other info. Thanks again.