Sql – Excel 2010 32-bit windows 7 64-bit odbc- Connection Issue

I don't know if this will be helpful to people outside my specific example. I was using the provider to perform a query on a CSV. For instance:

SELECT C7, 0.0001, (C2+C4*10000000) FROM (filename)

Here, the source file has been modified so that all of the columns are named Cn by their headers:

csvColumns = UBound(Split(lineIn, Delimiter)) + 1
For icol = 1 To csvColumns: columnLine = columnLine & "C" & icol & Delimiter: Next icol

So in my case, I have a file that looks like this:

C1       C2       C3       C4       C5       C6       C7
1234     654332   23.214   5432     12345    123      60918234.234
2345     876332   43.223   6534     23456    234      34958732.432
3456     987332   54.243   7654     34567    345      92645378.564

Normally, using the Jet OLEDB provider, the above query string can be used to read the contents of the file into a cell:

On Error GoTo PoviderError
With Conn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Mode = adModeRead
    .ConnectionString = "Data Source=" & path & ";Extended Properties='text;HDR=YES;FMT=Delimited'"
    .Open
End With
Rst.Open "SELECT " & selectText & " FROM [" & file & "];", Conn, adOpenKeyset, adLockOptimistic, adCmdText
If Not Rst.EOF Then Destination.CopyFromRecordset Rst

But in the above code "ProviderError" will get triggered on 64 bit machines because there is no Jet provider available. My workaround was as follows. I actually load the file into excel, and parse the query string myself. I break the query string by comma delimiter so that each section of the query string becomes the formula for a new cell. To create the formula, I simply prepend an = sign and replace the string "Cn" with a reference to the source column. This way, complex queries like (C2+C4*10000000) still get evaluated. I then copy down the formula according to the length of the source data, and then overwrite the formulas with hard coded values. The end result is identical to doing a complex Jet OLEDB query, albeit slightly slower. Code is below.

PoviderError:
    Resume FailOver 'Resets the error state so that further errors can be thrown
FailOver:
    FileReadFailover fixedFile, Destination, selectText, Delimiter
    ...

Private Sub FileReadFailover(ByVal fName$, ByRef Dest As Range, ByVal inputs$, ByVal delim$)
    Dim newBook As Workbook
    Dim pos(0 To 2) As Integer, col(0 To 2) As String
    Dim referenceText As String, i As Integer

    'Parse the query string 'inputs'
    pos(0) = 0: pos(1) = InStr(pos(0) + 1, inputs, ","): pos(2) = InStr(pos(1) + 1, inputs, ",")
    col(0) = Trim(Mid(inputs, pos(0) + 1, pos(1) - pos(0) - 1))
    col(1) = Trim(Mid(inputs, pos(1) + 1, pos(2) - pos(1) - 1))
    col(2) = Trim(Mid(inputs, pos(2) + 1))

    Application.StatusBar = Application.StatusBar & " Missing Jet Provider, waiting for Excel to open file..."
    Application.ScreenUpdating = True 'Allow excel to display the status bar showing the file loading
    Application.Workbooks.OpenText Filename:=fName, DataType:=xlDelimited, Other:=True, OtherChar:=delim
    Set newBook = Application.ActiveWorkbook
    Application.ScreenUpdating = False
    If newBook Is Nothing Then Err.Raise 1, , "User Cancelled Load"

    'Create a formula that will pull the values from the external file just opened.
    referenceText = Replace(newBook.Sheets(1).Cells(1, 1).Address(, , , True), "$A$1", "R[" & 2 - Dest.row & "]C")
    For i = 0 To 2
        If InStr(1, col(i), "C") Then col(i) = "=" & Replace(col(i), "C", referenceText)
        Dest.Offset(0, i).FormulaR1C1 = col(i)
    Next i
    'Copy the formulae down the based on the length of the input file
    Dest.Worksheet.Range(Dest, Dest.Offset(0, 2)).Copy _
        Dest.Worksheet.Range(Dest.Offset(1), Dest.Offset(newBook.Sheets(1).UsedRange.Rows.Count - 2, 2))
    'Make sure the worksheet recalculates to pull the values
    If Application.Calculation = xlCalculationManual Then Dest.Worksheet.Calculate
    'Now overwrite the formulas that pull the values with the values themselves
    Dest.Worksheet.Range(Dest, Dest.Offset(0, 2).End(xlDown)).Copy
    Dest.Worksheet.Range(Dest, Dest.Offset(0, 2).End(xlDown)).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Application.StatusBar = "File Import Successful"

    newBook.Close (False)
End Sub

The above solution assumes a query with 3 columns, but could easily be adjusted to take a any query, use split to get as many columns as there are, and dynamically redim pos() and col() arrays.

Ok, working now. Couple of mistakes on my side:

1. It is crossDomain: true, dataType: "jsonp" , typo - missed capital letters.
2. JSONP requests are not transparent. The data are not simply JSON notation, but have to be wrapped in a Js function call (on the server side): see http://en.wikipedia.org/wiki/JSONP Basically this means, if you cannot modify the sent data, JSONP is not the right option for you.

All things considered, it works. So my personal checklist would be:

1. Use json if possible (e.g. with Chrome, FF, or most likely IE10). Make sure response header is set: Access-Control-Allow-Origin:*
2. If using jsonp, check: async: true, jsonp and crossdomain: true, cache is false, jQuery.support.cors is true These are the prerequisites I have found.
3. Also make sure the jsonp response is a function call (JSON wrapped in function), not "just ordinary" JSON data.