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]

Ahh, I see, PHV, so using the Offset (which I had to look up in the help file to see what it was ), I can tell Excel to go put the returned data in the same row, 1 column (or however many columns as necessary) over.

Yeah, that looks a lot neater than my chicken pecking (can't call it scratch, since I'm typing on a keyboard ).

Okay, I've GOT to give this all a try. Lord willing that nothing else gets in my way, I'm going to give this a shot within the next few minutes, and post back with the results.

Thanks all!

--

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

 

[kjv1611]

Hmm, ok, right now it seems I am accomplishing quite a feat. It seems I've made Excel crash about 5 or 6 different times.

It has something to do with the parameters vs. using MS Query, and having it ask for a value.

Here is what happens.

First, I make sure that the parameter values equals to one cell in the "OtherSheet".

Then, I want to get the code for the running the MS Query connection, so I'm turning on the Macro Recorder..

Next, open MS Query, it first tells me (this query can't be edited with the wizard), and then goes to the query window. Fine, no problem.

I choose File-> Return Data to Excel. After another split second, I get the message stating that "Microsoft Office Excel has encountered a problem and needs to close. We are sorry for the inconvenience.

I'm guessing you can't have the Input box prompt set up at the same time as the parameters from Excel.

So, is there a way that I can dynamically setup this MS Query link to the SQL Server from Excel VBA, so that it does not matter what Workbook the code is being run from? In other words, I don't want the user to have to copy the data to a workbook that already has the query in place.

Any suggestions? I'm probably just really missing something or making it more complicated than it is.



--

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

 

[kjv1611]

I do have an idear, however as to how to sort of get around it, if I can't just setup the query each time via VBA. What I'm thinking is I can keep a separate Workbook containing a copy of the sheet with the Query in it, and then copy that into the active workbook when the code is running, and set the parameters up accordingly.

Does that sound like the best route, or do you have any other suggestions?

--

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

 

[SkipVought]

This may be the problem...

First, I make sure that the parameter values equals to one cell in the "OtherSheet".

Put the parameter on the same sheet as the query.

Skip,

_[red][/red]

 

[kjv1611]

Yep, that is what I was thinking was causing the problem. Because having a local sheet parameter would not require me to worry about the worksheet name.

Anyway, I'll give it a go again this morning, and see what I've got so far.


--

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

 

[kjv1611]

Alright, I have been working with it this morning. But I have one error that I cannot seem to get around. I'll post the code section, highlighting the line with the error, and then the error message:

Section of Code:

    Dim r As Range
    For Each r In Sheets(strWS).Range("C" & tr & ":C" & br)
      With Sheets("Query")
        .[E1] = r.Value
        [HIGHLIGHT].QueryTables(1).Refresh BackgroundQuery:=False[/HIGHLIGHT]
        r.Offset(0, 1).Value = .[A2]
      End With
    Next r

Error Message:

Run-time error '1004':

General ODBC Error

Any suggestions?

--

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

 

[kjv1611]

Well, shoot! Now it appears as if somehow I deleted the query?! I guess that very well COULD cause that problem!

I'll see if I can get that query back in and see if that clears up the error.

--

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

 

[kjv1611]

Alright, as an update, somehow it appears that the Query is being deleted in my code. I'm not following just exactly where that is occurring just yet. I'll post the whole Sub Procedure here, and if someone catches the line for me that'd be great. Otherwise, I'm going to step through the code and see what I can find.

Public Sub EnterFormulaForPostingToTT()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim ws1 As Worksheet
    Dim br As Long 'Row - used to find very last row with actual records
    Dim tr As Long 'Row - used to find where column titles located
    Dim x As Long 'To loop through Column C, trimming the data
    Dim lngSearchID As Long 'Variable for assigning a Unique Search ID to each Search Record (useful for SQL Query)
    
    Set wb = ActiveWorkbook
    Set ws = wb.ActiveSheet
    ws.Copy After:=ws
    Set ws1 = wb.ActiveSheet

    Application.ScreenUpdating = False
    
    If InStr(Range("A1").text, "Billing Period") Then
        If Range("A2").text = vbNullString Then
            Rows("1:2").Select
            Selection.Delete Shift:=xlUp
            Range("A1").Select
        Else
            Rows("1:1").Select
            Selection.Delete Shift:=xlUp
            Range("A1").Select
        End If
    End If
    
    br = ws1.Range("C65536").End(xlUp).Row
    tr = ws1.Cells.Find(What:="Search Criteria", After:=ws1.Cells(1, 1), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
        SearchFormat:=False).Row
    
    x = tr + 1
    For x = tr + 1 To br
        Cells(x, 3).Activate
        ActiveCell.Formula = Trim(ActiveCell.Formula)
    Next x

    ws1.Range("H:H").Insert Shift:=xlToRight
    br = ws1.Range("A65536").End(xlUp).Row
    tr = ws1.Cells.Find(What:="Login ID", After:=ws1.Cells(1, 1), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
        SearchFormat:=False).Row
    
    Range("H" & tr).Formula = "Search Format"
    Range("H" & tr + 1).Formula = "=PERSONAL.XLS!SearchFormat(C" & tr + 1 & ")"
    ws1.Range("H" & tr + 1 & ":H" & br).FillDown
    
    Columns("H:H").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A2").Select
    
    Dim lngRow As Long
    Dim strFormulaArray() As String
    Dim intArray As Integer 'Count of array
    Dim strNewFormula As String
    For lngRow = tr To br
        If InStr(Cells(lngRow, 8), "SSN") Then
            Cells(lngRow, 3) = Replace(Cells(lngRow, 3), "-", "")
        End If
        If InStr(Cells(lngRow, 8), "PHONE") Then
            Cells(lngRow, 3) = Replace(Cells(lngRow, 3), "-", "")
            Cells(lngRow, 3) = Replace(Cells(lngRow, 3), "(", "")
            Cells(lngRow, 3) = Replace(Cells(lngRow, 3), ")", "")
            Cells(lngRow, 3) = Replace(Cells(lngRow, 3), " ", "")
        End If
    Next lngRow
    
    Dim strWS As String 'ws1 name
    strWS = ws1.Name
    
    ws1.Columns("D").EntireColumn.Insert Shift:=xlToRight
    
    Dim r As Range
    For Each r In Sheets(strWS).Range("C" & tr & ":C" & br)
      With Sheets("Query")
        .[E1] = r.Value
        .QueryTables(1).Refresh BackgroundQuery:=False
        r.Offset(0, 1).Value = .[A2]
      End With
    Next r

    Application.ScreenUpdating = True
    
End Sub

--

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

 

[kjv1611]

Boy.... Do... I... feel.... dumb....

Nothing was deleting the query.

I just cannot see it when the code is stopped.

So, the error is as it stands:

"A general ODBC Error."

Any clues?

--

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

 

[SkipVought]

ODBC error is an error in the SQL logic.

What I do right after getting the error, REFRESH the query and see the message and/or EDIT the query and try to execute it in the QBE Editor to figure out the problem.

Skip,

_[red][/red]

 

[kjv1611]

Ok, I'll give that a try and post back...

--

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

 

[kjv1611]

Okay, I refreshed the data, and now I'm getting this:

[Microsoft][ODBC SQL Server Driver]String data, right truncation

Does this look like anything familiar?

--

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

 

[kjv1611]

My first guess is that it's a weird way of saying a type mismatch, possibly?

To consider that, I have the SSN set as text on the origial sheet, and did just have it set to General on the Query Sheet. I just updated that, so I'll see if that changes anything..

The SSNs are stored as VarChar on the SQL Server, I believe - I'll double check, if this first little change doesn't work.

--

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

 

[kjv1611]

Well, the change from General to Text in the Excel cell didn't work.. Still looking.

Is there a way to change the Parameter via VBA in the query in case that is somehow getting thrown off?

--

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

 

[SkipVought]

Mixing NUMBERS and TEXT in a column, often presents problems. Don't know if that could be a problem for you.

Skip,

_[red][/red]

 

[kjv1611]

Hmm, I think I see what you're suggesting. Possibly, with there being SSNs and other things in the same column, that could be causing the problem.

Is there a way to have MS Query create a temporary table of ONLY the records that contain SSNs, and then have it compare that against the SQL Server table?

--

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

 

[kjv1611]

And, actually, I was considering (just to make sure) trying to set the Parameter values of the Query via VBA. Is that possible?

I had tried this line once:

.QueryTables(1).Parameter = "E1"

But I got an error that could not be done, I forget the exact error message, but can recreate if necessary.

--

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

 

[kjv1611]

And, actually, I was considering (just to make sure) trying to set the Parameter values of the Query via VBA. Is that possible?

I had tried this line once:

.QueryTables(1).Parameter = "E1"

But I got an error stating that could not be done, I forget the exact error message, but can recreate if necessary.

--

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

 

[SkipVought]

The SOURCE for your MS Query IS the SQL Server Table, is it not?

Please post some sample SOURCE data that demonstrates this potential problem.

Skip,

_[red][/red]

 

[kjv1611]

Yes, the SOURCE is the SQL Server Table (VIEW, actually).

Currently, All I am trying to send to compare is the SSN, and that alone. But I just thought of something. In my query, I'm not checking to be sure that only SSNs are sent to compare. I could add another criteria from the spreadsheet to include only those that end up being SSNs, I guess... and the first record in the spreadsheet is a telephone listing, not SSN...

Anyway, the data being sent is all sorts of different things.

The data on the SQL Server table looks like this:
AcctID FirstName LastName Suffix SSN Etc
123456 John Smith Jr 123456789
321654 Susie Queu 978654321

The field formats are:
AcctID = (decimal(19,0),null)
FirstName = (varchar(30), null)
LastName = (varchar(60),null)
Suffix = (varchar(5),null)
SSN = (varchar(11),null)

Does that provide any clues?

--

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