Querying Data from SQL server based on Spreadsheet Data 2

[kjv1611]

This is based on some mentions in thread707-1318585.

I thought about posing the follow up question there, but in order to keep the thoughts separate, and to keep that thread from getting overly long, I thought I'd start this one.

I am wanting to try querying (using MS Query) data from a SQL server as matches data in a spreadsheet, and have then set up the arguments in VBA to have that happen in the background for other users, when they have a new workbook. I am now thinking that I'll eventually rather move this over to Access, as I believe that would be a better long-term solution, anyway - or either working with the dbas to get all this done on a SQL server - but I don't know if we'll ever get it that far.

So, here is where my problem is for the moment:

I'm trying to use the wizard, by going to:
Data -> Import External Data -> New Database Query

But when I get to that says, "Query Wizard - Filter Data", and it is askign what field to filter by, then "only include rows where" field name (I choose equals, does not equal, etc in one drop down box; then I'm to type in a value or select a value from the next drop down box). When I click on that second drop down box, Excel wants to freeze up for a really long time, due to the fact, I am guessing, that it is apparently trying to hit against a very large table.

So, what I am wanting to be able to do is to tell the query that it has to match a value in a particular column in a particular worksheet. So, how can I accomplish this?

Do I need to just type something into the field instead of clicking the drop down, and then proceed with the wizard, and then edit the VBA code to actually look in that column, or is there some better way?

The database table is far too large to return the whole thing to a spreadsheet.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Ok, so once it returns data back to Excel, that's when it will get the ODBC connection. I'll give that a shot and go from there... Hopefully I can get this whole deal down pat after all of this!

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

He is risen!

Skip,

_[red][/red]

 

[kjv1611]

So, just to make sure...

When PHV said,

You may define parameters and inform MS Query which cells to use for their values.

Was he talking about the method Skip lists in his [highlight]5 Apr 07 9:31[/highlight] posting?

Or is there some easier way? I know I can do it, but I may end up just having the other user do it in SQL Server for starters, and then go back later to work this in.


--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Can be done EITHER way.

Check out Data/Get External Data/Parameters. -- there are three options to choose from.

Skip,

_[red][/red]

 

[kjv1611]

Hmm, I might look at that real quick, then. Will post back..

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

By the way....

I'm sort of stuck on one point that just seems plain silly to me. For whatever reason, I can't get to the "parameters" option when I go to:
Data -> Import External Data ->Parameters

I have tried it on a Worksheet with a Database Query already setup and returned to that sheet, a sheet with data (no query) on it, and even a blank worksheet!

So, am I just totally missing something?

I can go to the "Data Range Properties" of a query in an Excel worksheet by right-clicking on the top-left cell (Usually "A1") of that query range in the sheet, but I was thinking that was not the same thing as the "parameters".

Is there some option I need to change in Excel to update this?

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

In the MS Query QBE Editor, replace the criteria VALUES that you want to parameterize, with [Enter some value?].

File/Return data to Excel (fill in the blanks with real data)

NOW, Data/Get External Data/Parameters...

Skip,

_[red][/red]

 

[kjv1611]

Hmmmm! That sounds like it could work! Something makes me think you've tinkered with this stuff a time or two here and there.

I'll give that a try, and post back in the next few, or first thing in the AM.

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

To create a parameter in MS-Query use the brackets, eg in a criteria cell of the ms-query grid:
=[some meaningful text]

Returns to the spreadsheet with the appropriate button and now you may say to Excel how to deal with the parameter.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kjv1611]

Yes! It worked! And 30 seconds to spare!

I'm going ahead and putting a pointy thing in here for all of this. This is going to be some big learning project, that's for sure!

I'll go into more detail later, but at least I can see the Parameters option, now.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Okay, on the Parameters thing:

Is there a way to force the query to accept a Range instead of just one Cell? When I tried to select a column instead of a Cell, I got an error message, stating:

Selection can be a single cell only.

Or if I enabled "Refresh automatically when cell value changes", then I got:

To refresh an external data range automatically when the parameter value changes, you must select a single cell to supply the parameter.

Or, do I somehow put a formula or this query in each cell within a row where I want the data returned? I could be wrong, but it would seem that it would be like running a separate query for each and every cell, which I KNOW would not be a good option, as this spreadsheet at most one time has had 20,000 or 24,000 records - I forget the exact number.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

What is the CRITERIA (SQL) that you are meeting with the parameter that needs a range?

Skip,

_[red][/red]

 

[kjv1611]

I believe this is the SQL you're talking about:

SELECT dwhPerson_vw.AcctID, dwhPerson_vw.FirstName, 
       dwhPerson_vw.LastName, dwhPerson_vw.Suffix, 
       dwhPerson_vw.SSN, dwhPerson_vw.City, 
       dwhPerson_vw.State
FROM   dw.dbo.dwhPerson_vw dwhPerson_vw
WHERE  (dwhPerson_vw.SSN=?)

Now, I don't know if this will make this any more or less complicated. In a way, it seems like it could be less complicated, but I'm thinking it would probably end up being more complicated! ....

If it made it any easier, I could return a combination of the AccountID, and a combination of the FName, LName, and Suffix. The city and state are not necessarily required at this point.

Anyway, does that SQL provide any clues? I got that by choosing "edit query" and hitting the "SQL" buttno in MS Query.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

The criteria in question is...

WHERE  (dwhPerson_vw.SSN=?)

This is a SINGLE VALUE, not a range.

Your question was, "Is there a way to force the query to accept a Range instead of just one Cell?"

If you have a range of SSNs, you sinple loop thru the list, write each value to the CELL linked as the parameter and then execute the query within the loop.

Skip,

_[red][/red]

 

[kjv1611]

If you have a range of SSNs, you sinple loop thru the list, write each value to the CELL linked as the parameter and then execute the query within the loop.

Ok, just to be sure I'm following...

I need to just write up the VBA, where I have a string value for the SQL statement, and I basically need to update the parameter value within the SQL statement (string variable) for each cell in the given row, and then set the Cell, say for AccountID equal to the returned value. So, the query updates only that one cell, as apposed to returning a whole table of data to a spreadsheet.

If this is correct, I also want to keep in mind: will this cause much of a great deal of resource usage on the SQL Server? I mean, I do know that they upgraded all the hardware to some really SMOKIN hardware that I could only ever DREAM of setting up something like it on my own - I like computer hardware, and was practically drooling when told of what all was installed on these servers.

But, I don't want it to bog down the server at all for 2 reasons:
1. I want it to be as efficient as possible for those running it, and just because I like trying to make things run as fast and efficient as possible anyway.
2. I don't want to get a call from one of the dbas saying, "Hey, what in the world are you doing?"

But if you think it wouldn't be terribly resource intensive, I can at least give it a try. It won't be a big deal if it's a one time test, anyway. If it were ran fairly often (which this would be) AND was resource intensive, then I might get that call.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Appears that we are confusing two approches.

Approch 1 - the Parameter Method. Format the query with one or more criteria like Where Field=?. Link each parameter to a CELL via Data/Get External Data/Parameters. Each time the query executes a connection is opened, resultset is fetched and the connection closes. Loop thru the parameter list, writing the value(s) to the linked cell and execute the query.

Approch 2 - the VBA method. Concatenate the parameters in the SQL statement. Open the connection ONCE, loop thru the parameter source list and execute the query for each row of parameter values, finally close the connection.

Skip,

_[red][/red]

 

[kjv1611]

For Approach 1, as you list it, I'm not sure I follow. How do I do one parameter for each record (row) in the spreadsheet?

And, I'm wondering which one of these would be less resource intensive, and/or faster/more efficient. I'm actually almost afraid to ask, b/c I'm thinking you may end up saying the second one. .

Hopefully, if nothing else good comes of all this, at least you can look back on this and say, "I really had to think with that one, because I couldn't get Mr. Dense to grasp the material just right."

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Lets say that you have ONE parameter criteria in your query.

On another sheet you have a list of 10 SSN's in B1:B10.

On your query sheet, A1 is linked to your parameter. You return your resultset to A3.

dim r as range
for each r in sheets("OtherSheet").range("B1:B10")
   with sheets("QuerySheet") 
     .[A1] = r.value
     .querytables(1).Refresh BackgroundQuery:=False
     'now do stuff with this SSN's data in this workbook

   end with
next

Skip,

_[red][/red]

 

[kjv1611]

Ahh, Ok, so what I will end up doing is looping through that code, and for instance (which is probably all I NEED to do), I can just copy out the AccountID for each record to where it needs to go via VBA...

So, I'll just here make up a make-believe 2 worksheets that fit your example, and match what I really NEED to do.

Ok...

Lets say that you have ONE parameter criteria in your query.

On another sheet you have a list of 10 SSN's in B1:B10.

One workbook, 2 worksheets. QuerySheet and OtherSheet

In QuerySheet, the query will return the SSN and AcctID
In OtherSheet, If there is a SSN returned, I want to put it in Row C (in this example) for that row/record.
Row B in OtherSheet, as you suggested will contain the SSN (for this example)

So, then I could do something like this:

dim r as range
For Each r in sheets("OtherSheet").range("B1:B10")
   with sheets("QuerySheet")
     .[A1] = r.value
     .querytables(1).Refresh BackgroundQuery:=False
     'now do stuff with this SSN's data in this workbook
     'This is where I copy the AcctID if there is one...
     Worksheets("OtherSheet").Range("C" & lngRow) = _
         Worksheets("QuerySheet").Range("A3")
         'Just sticking with what you mentioned as where data being returned.
   End With
Next r

So, do you think that line up with what you are suggesting?

If so, I might could actually make that work - my dense brain and all!

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

Provided the QueryTable takes its parameter in QuerySheet.A1 and put the first row of data in QuerySheet.A3:
Dim r As Range
For Each r in Sheets("OtherSheet").Range("B1:B10")
With Sheets("QuerySheet")
.[A1] = r.Value
.QueryTables(1).Refresh BackgroundQuery:=False
'This is where I copy the AcctID if there is one...
r.Offset(0, 1).Value = .[A3]
End With
Next r

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886