Jet OLEDB Provider 在 Windows 7 64 位上除 Excel 2010 之外的任何地方都可以使用 [英] Jet OLEDB Provider working everywhere except Excel 2010 on Windows 7 64-bit

问题描述

我在某些 VBA 代码中使用Microsoft.Jet.OLEDB.4.0"提供程序进行基本连接，除了运行 64 位 Microsoft Office 安装的 Windows 7 64 位操作系统外，该代码适用于任何地方Excel 2010.

I'm using the "Microsoft.Jet.OLEDB.4.0" provider for a basic connection in some VBA code, and the code works everywhere except on windows 7 64-bit operating systems running a 64-bit installation of Microsoft Office Excel 2010.

实际上，XP 32 或 64、Vista 32 或 64 和 7 32 的所有其他组合，以及 Excel 2003、2007 或 2010 安装在运行此代码时都没有问题，但在上述系统上，它会导致错误关于缺少提供程序"，我无法创建连接字符串.

Literally every other combination of XP 32 or 64, Vista 32 or 64, and 7 32, with Excel 2003, 2007, or 2010 installations has no problem running this code, but on the above described system, it results in an error about a "Missing Provider" and I can't create the connection string.

With Conn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Mode = adModeRead
    .ConnectionString = "Data Source=" & path & ";Extended Properties='text;HDR=YES;FMT=Delimited'"
    .Open
End With

我进行了大量研究，但据我所知，操作系统应该带有全套提供程序，包括 32 位版本的 Jet Provider(不存在 64 位版本), 并且 Excel 使用它进行连接应该没有问题.有任何想法吗?

I've done a ton of research, but from what I can tell, the operating system is supposed to come with a full set of providers, including the 32-bit version of the Jet Provider (no 64 bit version exists), and Excel should have no problem using it for the connection. Any ideas?

推荐答案

我不知道这对我的具体示例之外的人是否有帮助.我正在使用提供程序对 CSV 执行查询.例如:

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)

在这里，源文件已被修改，以便所有列都按标题命名为 Cn:

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

通常，使用 Jet OLEDB 提供程序，可以使用上述查询字符串将文件内容读入单元格:

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

但是在上面的代码中，ProviderError"将在 64 位机器上触发，因为没有可用的 Jet 提供程序.我的解决方法如下.我实际上是将文件加载到 excel 中，并自己解析查询字符串.我用逗号分隔符分隔查询字符串，以便查询字符串的每个部分成为新单元格的公式.要创建公式，我只需添加一个 = 符号并将字符串Cn"替换为对源列的引用.这样，像 (C2+C4*10000000) 这样的复杂查询仍然会得到评估.然后我根据源数据的长度复制公式，然后用硬编码值覆盖公式.最终结果与执行复杂的 Jet OLEDB 查询相同，但速度稍慢.代码如下.

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

上述解决方案假设查询有 3 列，但可以轻松调整以进行任何查询，使用 split 获取尽可能多的列，并动态地重新排列 pos() 和 col() 数组.

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.

这篇关于Jet OLEDB Provider 在 Windows 7 64 位上除 Excel 2010 之外的任何地方都可以使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！