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

 

[SkipVought]

So what's the SQL that is getting the error?

And what does it say when you execute it in MS Query?

Skip,

_[red][/red]

 

[kjv1611]

Here is the SQL:

SELECT dwhBorrower_vw.AcctID
FROM dw.dbo.dwhBorrower_vw dwhBorrower_vw
WHERE (dwhBorrower_vw.SSN=?)

This is the error given by MS Query:
....
There is no error. It prompts me for a value, and if I put nothing in the Input box, then there is no error (and no records returned, of course).

So, I tried with some numbers, and....
Query ran perfectly, no errors...

So, I tried with some letters, and....
It again ran perfectly with no errors...

So, is there a way for me to change the parameter value in VBA to make sure that is not getting off the track somewhere?

--

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

 

[kjv1611]

I'm now thinking that since it doesn't seem to matter whether the search input text is a number or letter (and I also even tried symbols), that it's nothing to do with the data being compared.

Just to be sure, I'm thinking about adding into the VBA for running the query the option to look at the Search Format and make sure that it only searches SSNs...

--

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

 

[SkipVought]

I prefer concatenating the value into the SQL rather than using the parameter method...

    Dim r As Range
    For Each r In Sheets(strWS).Range("C" & tr & ":C" & br)
      With Sheets("Query")[s]
        .[E1] = r.Value[/s]
        with .QueryTables(1)[b]
          sql = "SELECT dwhBorrower_vw.AcctID "
          sql = sql & "FROM dw.dbo.dwhBorrower_vw dwhBorrower_vw "
          sql = sql & "WHERE (dwhBorrower_vw.SSN=" & r.value & ")"

          .commandtext = sql[/b]
          .Refresh BackgroundQuery:=False
        end with
        r.Offset(0, 1).Value = .[A2]
      End With
    Next r

Skip,

_[red][/red]

 

[kjv1611]

Ok, so if I do it this way, it will basically make sure the SQL is correct in MS Query?

I'll try this and see what it returns.

Thanks for all the effort!

--

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

 

[kjv1611]

Alright, I tried this method, and first, it gave "Variable not defined error" for "Sql = ..." lines.

So, I just changed all the "Sql" listings to ".Sql and no more errors there.

But then, I ran it, and got this error:

Run-time error '1004':

SQL Syntax Error

So, I thought maybe Sql was actually a variable, and not a member of .QueryTables(1). So I changed the .Sql back to Sql and Dimmed the variable at the top of that section (Dim Sql As String).

But that returned the same error. So, either way, it appears there is an error...

I think I'm going to try a DEBUG.PRINT Sql next to see what the SQL being put together comes out to be...

--

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

 

[kjv1611]

Okay! Now we're getting somewhere!

Apparently, I need to make the code start one row down from where it is currently picking up the "range" in the second spreadsheet, as it's picking up the Row Header.

See the SQL that was printed with Debug.Print:

My code where I "printed":

    Dim Sql As String
    Dim r As Range
    For Each r In Sheets(strWS).Range("C" & tr & ":C" & br)
      With Sheets("Query")
        .[E1] = r.Value
        With .QueryTables(1)
          Sql = "SELECT dwhBorrower_vw.AcctID "
          Sql = Sql & "FROM dw.dbo.dwhBorrower_vw dwhBorrower_vw "
          Sql = Sql & "WHERE (dwhBorrower_vw.SSN=" & r.Value & ")"

          .CommandText = .Sql
          [highlight]Debug.Print "Sql: " & Sql[/highlight]
          [highlight]Debug.Print "CommandText: " & .CommandText[/highlight]
          .Refresh BackgroundQuery:=False
        End With
        r.Offset(0, 1).Value = .[A2]
      End With
    Next r

The Immediate Window gave:

Sql: SELECT dwhBorrower_vw.AcctID FROM dw.dbo.dwhBorrower_vw dwhBorrower_vw WHERE (dwhBorrower_vw.SSN= Search Criteria)
CommandText: SELECT dwhBorrower_vw.AcctID FROM dw.dbo.dwhBorrower_vw dwhBorrower_vw WHERE (dwhBorrower_vw.SSN= Search Criteria)

--

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

 

[kjv1611]

Alright! I've got that error "nipped in the bud"

The correction looks like this:

    Dim Sql As String
    Dim r As Range
    For Each r In Sheets(strWS).Range("C" & tr [HIGHLIGHT]+ 1[/HIGHLIGHT]& ":C" & br)
      With Sheets("Query")
        .[E1] = r.Value
        With .QueryTables(1)
          Sql = "SELECT dwhBorrower_vw.AcctID "
          Sql = Sql & "FROM dw.dbo.dwhBorrower_vw dwhBorrower_vw "
          Sql = Sql & "WHERE (dwhBorrower_vw.SSN=[HIGHLIGHT]'[/HIGHLIGHT]" & r.Value & "[HIGHLIGHT]'[/HIGHLIGHT])"

          .CommandText = .Sql
          Debug.Print "Sql: " & Sql
          Debug.Print "CommandText: " & .CommandText
          .Refresh BackgroundQuery:=False
        End With
        r.Offset(0, 1).Value = .[A2]
      End With
    Next r

--

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

 

[kjv1611]

It's currently running. I'll know the results in just a few, hopefully... will post back later on..

--

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

 

[kjv1611]

One more update.

It was running, and I was going to break into it to see where it was, and when I did so, it gave an error:
"Run-time error '7':
Out of memory"

But it would go back to querying.

It appears this querying takes a while, as it has to loop through every record. I may give it another shot after a while to see if it will complete in a reasonable time period.

--

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

 

[PHV]

Is SSN indexed in dwhBorrower_vw ?

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

 

[kjv1611]

Seems like I remember asking about that before, and I believe I was told that it was optimized but not indexed or something like that...

It is a View of the underlying table, and not the direct table. So, I'm guessing that could explain some lag.

What I'd be wondering is whether it'd be possible to setup the SQL within Microsoft Query that would just export all the data I want to compare with to the SQL Server, then execute the query on that, and return the data to Excel that way. If that isn't even possible, then I'll just have to do this without that option, I guess.

--

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

 

[PHV]

Seems to me that msaccess is better suited for your issue.

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, that's what I was thinking, too. But since I told my manager I could probably get it back in Excel quicker, just not as efficient (quicker as in me saying, ok, it's finished), they preferred me to leave it in Excel to start with, and then just work on moving it to Access over time.

By the way, it did finally return the results, and they appear to all be accurate.

I think total time for running it ended up being maybe 10 or 15 minutes. Frankly for what is being queried, that really is too long, but at least I do now that it is working.

Well, I'll pass this along with the caviat that it takes a while to run, and then advise that it will run MUCH quicker once it is in Excel. Wow! I never knew that it would be so difficult to get something like this working efficiently between a SQL server and Excel versus Access and SQL server. It's a night and day difference!

--

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

 

[kjv1611]

Thanks, you two. You've both been most helpful. I'd post multiple "pinkies" on Skip, but I'm limited to one.



Oh well.

This has definitely been one big learning venture for me, that's for sure.

Besides some technical things, this was just a good wake-up call for principle number one for technical projects:

1. Plan ahead, and determine what tool will be best used for the job at hand. Wrong tool equals just not working, or being a much more terrible head-ache! [flush]

--

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