vba sql连接txt文件，错误80004005

298923124

vba sql连接txt文件，错误80004005

文件路径是没问题。

Sub tt()
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim fname
   
    fname = Dir(ThisWorkbook.Path & "\11.txt")
    If fname = "" Then
        MsgBox "文件不存在"
        Exit Sub
    End If
   
   
    Select Case Application.Version * 1    '设置连接字符串,根据版本创建连接
    Case Is <= 11
        strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & ThisWorkbook.Path & "\11.txt"
    Case Is >= 12
        strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\11.txt;Extended Properties='text;HDR=YES;IMEX=1';"
    End Select
   
'    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Hzc\Desktop\11.txt;Extended Properties='text;HDR=No;FMT=Delimited'"
   
     ' 打开连接
    conn.Open strConn
   
    ' 这里的文件名应该根据你的实际文件名进行修改
    strSQL = "SELECT * FROM 11.txt"
   
    ' 执行查询并返回记录集
    rs.Open strSQL, conn
   
   
   
End Sub

zpy2

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path ;Extended Properties='text;HDR=YES;FMT=Delimited';"

https://www.connectionstrings.com/microsoft-jet-ole-db-4-0/

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;Extended Properties="text;HDR=Yes;FMT=Delimited";
The delimiter can be specified in the registry at the following location:
HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Jet \ 4.0 \ Engines \ Text
"Format" = "TabDelimited"
or
"Format" = "Delimited(;)"

lss001

Sub tt()
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim fpath$, fname$, strConn$, db$
   
    fpath = ThisWorkbook.Path
    fname = Dir(fpath & "\11.txt")
    If fname = "" Then
        MsgBox "文件不存在"
        Exit Sub
    End If
   
     '根据版本不同创建连接字符串
    Select Case Application.Version * 1
        Case Is <= 11: db = ".JET.OLEDB.4.0"
        Case Is >= 12: db = ".ACE.OLEDB.12.0"
    End Select
    strConn = "Provider=Microsoft" & db & ";Data Source=" & fpath & ";Extended Properties='text;HDR=yes;IMEX=2;FMT=Delimited';"

     ' 打开连接
    conn.Open strConn

     '根据实际修改文件名
    strSQL = "SELECT * FROM [11.txt]"
   
    ' 执行查询并返回记录集
    rs.Open strSQL, conn
   
     '关闭释放对象
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub