Use MS Query in Excel to Return SQL Server

[kjv1611]

Excel 2003
SQL Server 2005

If there would be a better forum to discuss/ask this, let me know.

What I would like to do is, in VBA if possible, query data from a SQL Server database view according to data in certain cells within the Worksheet.

For instance, I might have this data:

I want to return the FieldName from the SQL Server Table which contained the "Search Criteria" as I'll list examples, below for each row, IF there is a match. Also, if there is a match, I want to return the AccountID.
If there is no match, I want to return something like "NULL" or "NoFind"

Here is an exmaple of the SearchFormat that will be listed, currently in column "H":

PHONE
SSN
NAME
ADDRESS

And here are some made-up data that would match the search formats:

Search Criteria SearchFormat
111-11-1111 SSN
111111111 SSN
222-222-2222 PHONE
2222222222 PHONE
2222222 PHONE
Smith, John NAME
Homer Simpson NAME
123 Main St City, ST ADDRESS

So, I'd want to return the field where it was located. So, if telephone number, I'd want to know if it's phone1, phone2, or phone 3. If address, add1 or add2, and so on.

I want to be able to say, if SearchFormat is SSN, then compare against any SSN fields in the SQL Server database table, whereas if it is PHONE, then look in the various phone fields, and so on.

I hope this is making some sense.

Can this be done with MS Query? If so, how complex would it be to setup, and is it possible it would cause more lag on the SQL server than say a query run in Query Analyzer, or the new MS SQL Server Management Studio?

The reason I am asking is that the view which I would query against has over 30 million records, and I'd hate to cause any serious lag, and get a phone call from the head dba for that server.

--

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

 

[SkipVought]

Hi,

Add a querytable to yor sheet via Data/Get External Data/New Database Query...
, returning the resultset to the sheet.

Turn on the macro recorder and record EDITING the query and File/Return data to Excel.

Modify the Command string to create an IN list...

sSQL = sSQL & "Where MyField In (" & MakeList([MyListRange]) & ")"

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]

Wow, this sounds complicated, but pretty cool!

Cool Beans, Yo!

Sorry, I must've read that like 40 times over in like thread1256-1329979 - at least the cool beans part, ya know?



I'll give this a shot, and let you know what problems I encounter, because I'm sure I'll come up with something!

--

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

 

[kjv1611]

After looking at this for a while, and getting started, I think I'm starting to grasp a little. But what I want to know is how to I make sure that the returned values match the data in my spreadsheet?

I'll keep trying, and see what I come up with.

--

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

 

[kjv1611]

As an update, I've recorded the macro, and then tested running it after the other code for specifying each search format. Now I've got to attempt the customization as mentioned by Skip.

I'll post back my results. Thanks again.

--

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

 

[SkipVought]

If you have data on a sheet, then you have to loop to get row parameters, use ADO to get a recordset and return the data to the sheet in VBA.

Skip,

_[red][/red]

 

[kjv1611]

Hmmm... I get the loop idea and all, and the ADO is the method for connecting a recordset to the SQL server, correct?

But what I am wondering is if I do this, then is that going to make the process much slower than exporting the data to SQL server, and querying the data directly on the server? We have a Sandbox on the server we can export tables to.

--

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

 

[kjv1611]

And with the way this is going, I'm starting to wonder whether it'd be easeier to do it in Access than Excel, and then just export the data to an Excel Worksheet after all the calculations/querying is done.

Any thoughts, there?

--

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

 

[SkipVought]

What are the data elements on the sheet that would be criteria values in your query?

Skip,

_[red][/red]

 

[kjv1611]

The Data Elements are really just one field/column, I'm afraid. Basically, based on the column, "SearchFormat", I want to be able to match up the value in another column, "SearchCriteria".

So, for instance, if the SearchFormat is PHONE, then I want to compare the value in SearchCriteria with the 3 possible PHONE fields in the SQL Server View.

If the SearchFormat is NAME, then I want to compare the value in SearchCriteria with the Name fields (The fields in the SQL View are actually split into FirstName, LastName, and Suffix. I believe that at least in most cases, it will be okay to just compare the First and Last Name fields for the name.

I'm sure I'll have to do some tweaking through the testing of this method, in order to get the checks just right.

Does that make any sense?

Basically, if NAME is the search format, then I want to see if the value in SearchCriteria matches the name in FirstName and LastName fields, and if so, return the associated AccountID.

Of course, I've already thought of one possible problem I coudl run into, and that is returning multiple AccountIDs for those accounts which may have the same customer associated. Say if one person has 2 or more "accounts", and they have the same name and address, then I would imagine I'd end up with multiple records, and with that I'm not sure that the spreadsheet could contain all the possibilities in ever case.

--

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

 

[SkipVought]

Here's some code I am developing as we speak, that uses THREE parameters from a table on a sheet...

Sub MFG_Load_Main()
    Dim r As Range, iColFrom As Integer, iColThru As Integer, lRowOUT As Long
    Dim sConn As String, sSQL As String, sServer As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    
    Set cnn = New ADODB.Connection
    
    sServer = "a010prod"
    cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
               "Server=" & sServer & ";" & _
               "Uid=/;" & _
               "Pwd="
    
    Set rst = New ADODB.Recordset
    
    With wsOldOfflPartOPS
        iColFrom = .Rows(1).Find("BEG OPER").Column
        iColThru = .Rows(1).Find("END OPER").Column
        
        For Each r In .[PN]
            sSQL = sSQL & "SELECT"
            sSQL = sSQL & "  PART_NUM"
            sSQL = sSQL & ", OPER"
            sSQL = sSQL & ", CC"
            sSQL = sSQL & ", MACH_GRP"
            sSQL = sSQL & ", PST"
            sSQL = sSQL & "  Sum(RUN_HOURS)"
            sSQL = sSQL & vbCrLf
            sSQL = sSQL & "FROM FPRPTSAR.MC_BUILD_SCHEDULE"
            sSQL = sSQL & vbCrLf
            sSQL = sSQL & "WHERE PART_NUM  ='" & r.Value & "'"
            sSQL = sSQL & "  And OPER      BETWEEN '" & .Cells(r.Row, iColFrom).Value & "' AND '" & .Cells(r.Row, iColThru).Value & "'"
            sSQL = sSQL & "  And PST       <Last_Day(Sysdate+365)"
                
            With rst
                .Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
                                  
                .MoveFirst
            
                With wsMFG_Load
                    lRowOUT = .[A1].CurrentRegion.Rows.Count + 1
                    .Cells(lRowOUT, "A").CopyFromRecordset rst
                End With
            End With
        Next
    End With
    
    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Sub

Skip,

_[red][/red]

 

[kjv1611]

Wow! Ok, so basically it's running a query for each record in the spreadsheet, is that correct?

--

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

 

[SkipVought]

yes.

Skip,

_[red][/red]

 

[PHV]

I would query against has over 30 million records
Hopefully all columns candidate for the search are indexed !

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

 

[kjv1611]

I would think they are indexed. Is there a way I can tell whether or not various columns in the view are indexed. I probably should remember how to find that, but I just can't think of it.

--

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

 

[PHV]

The better way is to ask the dba ...

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

 

[kjv1611]

Yeah, I would have figured as much.

I guess I'll have yet another question for one of the main guys up there.

They know me well at our tech office, what with how many questions, sometime seemingly strange unexpected questions, I can come up with in relation to our SQL Server tables and such.

--

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

 

[PHV]

FYI, a view has NO indexed column as it is only a SELECT instruction (like a saved query in Access), but the optimizer may play with the underlaying indexed columns.

 

[kjv1611]

Ok, yes, it seems like I vaguely remember something about that. Not whether this particular view is optimized, but about how that views are not indexed, but can be optimized.

I'm pretty sure that is setup for all of the larger views such as this. Of course, we do have some even larger.

I'll see what I can find out.

--

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