Office2010通过Microsoft Query读取dbf文件失败求助

Troubleshooting Microsoft Query Issues #

Let’s start with common fixes for the "impossible to access the table" error you’re seeing:

• Check Architecture Match: Office 2010 comes in 32-bit and 64-bit versions. If you’re running 32-bit Excel, you must use 32-bit dBASE/FoxPro drivers (and vice versa). To confirm your Office version:
  1. Open Excel, go to File > Help
  2. Look for "About Microsoft Excel" – it will clearly state 32-bit or 64-bit
• Fix File Path & Permissions:
  • Move your zonas.dbf file to a folder with no spaces or special characters in the path (e.g., use C:\DBF_Files\zonas.dbf instead of C:\My Important Files\zonas.dbf)
  • Ensure you have read permissions for the folder containing the dbf file
• Verify File Integrity: Try opening the dbf file with a free desktop tool (like DBF Viewer Plus) to confirm it’s not corrupted. If it is, restore a backup or repair the file.
• Update Drivers: Outdated drivers often clash with Windows 10. Download the latest 32/64-bit Microsoft dBASE ODBC driver matching your Office version and reinstall it.

VBA Code Alternative (Reliable Workaround) #

If Microsoft Query still won’t cooperate, using VBA with ADODB is a solid way to import your dbf data directly into Excel. Here’s how to set it up:

Step 1: Enable the Developer Tab #

1. Go to File > Options > Customize Ribbon
2. Check the box for Developer and click OK

Step 2: Create a New VBA Module #

1. Click the Developer tab > Visual Basic (or press Alt + F11)
2. In the VBA Editor, go to Insert > Module

Step 3: Paste the Import Code #

Replace C:\Your\File\Path\zonas.dbf with the actual full path to your dbf file, then paste this code into the module:

Sub ImportDBFtoExcel()
    Dim conn As Object
    Dim rs As Object
    Dim strConn As String
    Dim strSQL As String
    Dim ws As Worksheet
    Dim filePath As String
    
    ' Set your dbf file path here
    filePath = "C:\Your\File\Path\zonas.dbf"
    
    ' Create a new sheet for the imported data (optional)
    Set ws = ThisWorkbook.Sheets.Add
    ws.Name = "Zonas Imported Data"
    
    ' Initialize ADODB objects
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    ' First try dBASE driver
    strConn = "Driver={Microsoft dBASE Driver (*.dbf)};DBQ=" & Left(filePath, InStrRev(filePath, "\")) & ";"
    
    ' Open connection, fall back to Visual FoxPro driver if needed
    On Error Resume Next
    conn.Open strConn
    If Err.Number <> 0 Then
        strConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & Left(filePath, InStrRev(filePath, "\")) & ";"
        conn.Open strConn
        If Err.Number <> 0 Then
            MsgBox "Failed to connect. Check driver installation and file path.", vbCritical
            Exit Sub
        End If
    End On Error GoTo 0
    
    ' Query to get all data from the dbf table
    strSQL = "SELECT * FROM " & Mid(filePath, InStrRev(filePath, "\") + 1, Len(filePath) - InStrRev(filePath, "\") - 4)
    
    ' Load data into recordset
    rs.Open strSQL, conn
    
    ' Copy column headers to the sheet
    For i = 0 To rs.Fields.Count - 1
        ws.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i
    
    ' Copy all data rows to the sheet
    ws.Cells(2, 1).CopyFromRecordset rs
    
    ' Clean up resources
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
    
    MsgBox "Data imported successfully!", vbInformation
End Sub

Step 4: Run the Macro #

1. In the VBA Editor, click the green play arrow (or press F5)
2. If prompted, enable macros (you may need to adjust Excel’s macro security settings temporarily)

Key Notes #

• The code automatically tries two different drivers to maximize compatibility with your system.
• Double-check the file path in the code – use backslashes (\) and avoid typos.
• If you get driver errors, install the ODBC driver that matches your Office architecture (32/64-bit) from Microsoft’s official website.

内容的提问来源于stack exchange，提问作者Ing Angel Lopez