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]

As an update to my question, it finally "unfroze" - or maybe I should say "thawed" , and I went ahead and saved the query, and told it I wanted to edit within Microsoft Query.

So, now I've got what looks similar to doing a query with click and drag type options within Access or SQL Server Management Studio - SQL Server 2005 - (or Enterprise Manager with SQL Server 2000).

My question is, then, I guess, where do I go from here?

--

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

 

[PHV]

Don't ollow the wizard but go directly in the MS Query grid and uncheck the Immediate button.
You may define parameters and inform MS Query which cells to use for their values.

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

 

[SkipVought]

Hi,

in the QBE Editor make sure that Records/Automatic Query is NOT CHECKED. That means that in the editor, you execute when you want, not on each change you make.

If you have a LIST of items that would be SQL coded...

  AND [The Field] IN ([i]Your List[/i])

where Your List is on a sheet somewhere.
You can use this kind of technique...
1. a FUNCTION to return the list

Function MakeList(rng As Range)
    Dim r As Range
    Const TK = "'"
    Const CM = ","
    For Each r In rng
        With r
            MakeList = MakeList & TK & .Value & TK & CM
        End With
    Next
    MakeList = Left(MakeList, Len(MakeList) - 1)
End Function

2. the SQL string variable to concatenate the function

sSQL = "Select * From [The Table] Where [The Field] IN (" & makelist([i]YourRange[/i]) & ")"

Skip,

_[red][/red]

 

[kjv1611]

Thanks for that advice, guys. I'll rerun through it either this afternoon if I get a chance, or tomorrow morning if not. Then, I'll post back with whatever results. I was originally just going to have the others import the spreadsheet into the SQL Server, and then import back into Excel the results from SQL Server. But, for those whom I am doing this for, that may be more work than just setting up the MS Query deal.

I just hope I can get this to work.

--

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

 

[kjv1611]

Okay, I found and unchecked the "Records/Automatic Query" option. So, THAT was what was causing the query to take so long!

Okay, I'm working now, and will probably have more questions, soon, I guess..

--

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

 

[SkipVought]

No, that is NOT why the query took so long.

It's why the query executed each time you made a change in the QBE grid.

Skip,

_[red][/red]

 

[kjv1611]

In the MS Query editor, how do I tell it what cells I want to get the value from, or is that what you were talking about with the SQL string of the Range in a worksheet, Skip?

--

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

 

[kjv1611]

Gotcha (not why query took so long). So, it would have initially taken that time, anyway, when I am first tell it to open MS Query if the table being queried is rather large, then. So turning off the "auto query" option just keeps it from hitting against the server every time I make a change, and thus slowing things down and using bandwidth to the server, I guess.

If that's the case, I still like the thought of turning that "auto query" off.



--

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

 

[kjv1611]

In trying to find how to tell MS Query where to look in Excel for my criteria, I went to the Criteria Menu, selected "Add Criteria", then clicked the "Values" button to specify values, I guess, and then everything seemed to freeze. I'll probably just have to wait for a moment, and then it will release, and I can go back to trying..

--

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

 

[kjv1611]

Okay, after a long wait, I got an error message that stated, "Unable to retrieve values." No reason as to why or any other detail. Now that's a helpful error message!

If anyone has any further suggestions on this, I'm all ears.


Thanks!

--

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

 

[SkipVought]

If you hit the VALUES button in the Criteria window, the query editor manager performs a query to return a list of distince values for that column.

What kind of criteria statement do you need to construct?

[Field] equals
[Field] does not equal
[Field] is less than...

If its,

[Field] is one of...

then if the list is LONG or the list is DYNAMIC, you can do it via code in the VB Editor.

Here's an example of one I'm working on as we speak...

Sub GetRepayOrders()
    Dim sSQL As String
    
    sSQL = "SELECT"
    sSQL = sSQL & "  MFG_ORDER_INFO.MFG_ORD"
    sSQL = sSQL & ", MO_DEMAND.DMD_ORD"
    sSQL = sSQL & ", MFG_ORDER_INFO.LPCT"
    sSQL = sSQL & ", MFG_ORDER_INFO.PCT"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "FROM"
    sSQL = sSQL & "  FPRPTSAR.MFG_ORDER_INFO MFG_ORDER_INFO"
    sSQL = sSQL & ", FPRPTSAR.MO_DEMAND MO_DEMAND"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "WHERE MFG_ORDER_INFO.MFG_ORD = MO_DEMAND.MFG_ORD"
    sSQL = sSQL & "  AND ((Substr(MO_DEMAND.DMD_ORD,1,7) In (" & MakeList([LOANNO]) & ")))"
        
'
    With wsRepayOrders.QueryTables(1)
        .Connection = Array(Array( _
        "ODBC;DSN=A010PROD;;DBQ=A010PROD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;
FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;" _
        ), Array("CSR=F;FWC=F;PFC=10;TLO=0;"))
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
    End With
End Sub
Function MakeList(rng As Range)
    Dim r As Range
    Const TK = "'"
    Const CM = ","
    For Each r In rng
        With r
            MakeList = MakeList & TK & .Value & TK & CM
        End With
    Next
    MakeList = Left(MakeList, Len(MakeList) - 1)
End Function

Skip,

_[red][/red]

 

[kjv1611]

Skip, I wish it were easier, but that appears to be what I am looking to do. And though it looks complex, it's not too terribly far out of reach for me, I don't think!

Yes, I think the "is one of" operation is what I'd be looking for. I'll see if I can get something together along those lines, see how it works, and post back as to my code and results (how well it works for me, any errors - hope not, etc)

Thanks! Hopefully I'll get this together in short order after I take care of one other little issue here.. (not related).



--

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

 

[kjv1611]

Okay, this may be a dumb question, so if so, I guess I'm just dumb. How do I get the query to include the data from the original spreadsheet in the results? Does including that in the WHERE clause take care of it, or do I need to somehow include something similar in the SELECT clause. I would think I need to include it in the SELECT clause, but I wanted to make sure.



--

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

 

[SkipVought]

What do you mean by, "How do I get the query to include the data from the original spreadsheet in the results?"

1. Join ALL the data from the original spreadsheet with data from the external source or

2. Code a criteria for a column from the original spreadsheet to filter the fata from the external source or

3. Something entirely different

???


Skip,

_[red][/red]

 

[kjv1611]

Well, first, I'm not looking for any fata. I'm always looking to lose some of that, anyway.


Basically, the end result I want is just to:
[ol][li]return a column of data from the SQL server to the existing spreadsheet based on values in that spreadsheet if possible, [/li]
[li]or either basically return all the Excel data along with the one new column from the SQL server.[/li][/ol]


I am fine with returning NULLS or not nulls - NULL or just blank really doesn't matter at this point.



--

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

 

[SkipVought]

There are a number of ways to do this. I'd recommend starting with a query on new sheet, using the MakeList function to return one or more lists for the IN () clause(s). Then do a lookup from your existing sheet to the QueryTable to fill in the blanks that you are looking for.

Skip,

_[red][/red]

 

[kjv1611]

Now that is something I thought about doing before. The last time (yesterday, I think) I tried it, it worked for the first several, but then started giving the same ID matching with several different SSNs where I used the =LOOKUP() function. For the life of me, I couldn't figure out what the cause of that one was. But I'll try this again, and see what I get. With that one, the query was done actually on the SQL server, and then imported back to Excel.

--

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

 

[kjv1611]

One more thing I'm just not too sure about here is the connection. Is this setting up an ODBC connection, or what sort of connection are we setting up? I want to make sure that I find the correct details for the connection. I was trying to use the UDL Wizard method to find the connection, as described in thread705-1245040, but what I am getting looks nothing like what you have. I had tried to do a query with MS Query before, and have the whole thing recorded with Excel's Macro recorder, but it didn't record anything useful. It could just be that I goofed somewhere, or that messed up the focus of Excel when I had to work on something else right in the middle of it.

Any thoughts?

--

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

 

[SkipVought]

First, you need an ODBC Driver configured so that you can access the database. Start/Settings/Control Panel/Administrative Tools/Data Source (ODBC)...

Once you have that condifured, you select that driver when you start the Query Wizard. It should list the available Owners and Tables that you have access to.

Once you have made that selection and formatted a query, returned a data result, File/Return data to Excel will add the QueryTable to the sheet and return the data.

Then turn on your macro recorder and record Data/Get External Data/Edit Query -- File/Return data to Excel and turn off the recorder. The CONNECTION string and the SQL will be in the recorded code.

Skip,

_[red][/red]