代码之家 › 专栏 › 技术社区 › cdpp

Excel VBA 3704错误(无法打开与AdoDB的连接)

adodb vba excel

cdpp · 技术社区 · 3 年前

我正在尝试使用adodb处理工作表中的某些内容。由于某种原因,我一开始就无法打开连接;下面的代码给出了3704错误

我已经检查了我的推荐信,Active X等已启用。

Dim sSQLQry As String
Dim ReturnArray

Dim oCn As New ADODB.Connection
Dim oRs As New ADODB.Recordset
Dim str_provider As String
Dim str_hdr As String
If Application.Version < 12 Then
    str_provider = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="
    str_hdr = "Excel 8.0;"
Else
    str_provider = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source="
    str_hdr = "Excel 12.0;"
End If

Dim str_conn
str_conn = str_provider & ThisWorkbook.FullName & "; Extended Properties='" & str_hdr & "HDR=Yes;'"";"
oCn.Open str_conn
Dim str_query As String
str_query = "select * from " + "final_song" + "where 1=0"
oCn.Execute str_query

0 回复 | 直到 3 年前

CDP1802 3 年前

尝试在“扩展属性”中使用双引号

   "; Extended Properties=""" & str_hdr & "HDR=Yes;"";"

假设您有一张名为的表上的数据 final_song

Option Explicit

Sub QuerySQL()

    Const SQL = "SELECT TOP 5 [F1],[F2],[F3] FROM [final_song$]"

    Dim oCn As New ADODB.Connection, oRs As New ADODB.Recordset
    Dim str_conn As String, str_prop As String
    Dim wb As Workbook, i As Integer, msg As String
    
    ' connection parameters
    If Application.Version < 12 Then
        oCn.Provider = "Microsoft.Jet.OLEDB.4.0;"
        str_prop = "Excel 8.0;"
    Else
        oCn.Provider = "Microsoft.ACE.OLEDB.12.0;"
        str_prop = "Excel 12.0;"
    End If

    ' data source
    Set wb = ThisWorkbook
    str_conn = "Data Source=" & wb.FullName & "; " & _
               "Extended Properties=""" & str_prop & "HDR=No;"";" ' no header to use F1,F2,F3

    'Debug.Print str_conn
    oCn.ConnectionString = str_conn
    
    ' open connection
    On Error Resume Next
    oCn.Open
    If oCn.Errors.Count > 0 Then
        msg = oCn.ConnectionString & vbCrLf
        For i = 1 To oCn.Errors.Count
            msg = msg & vbCrLf & oCn.Errors.Item(i-1).Description
        Next
        MsgBox msg, vbCritical, "Connection Error"
        Exit Sub
    End If
    On Error GoTo 0
    'Debug.Print Join(Split(oCn.ConnectionString, ";"), vbCrLf)
    
    ' execute
    Set oRs = oCn.Execute(SQL)
    MsgBox oRs.GetString, vbInformation

End Sub

推荐文章