请教 vba 如何读取 mdb 格式数据库？

yxp

数据库在附件里
Sub domdb()
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim connStr As String, sqlStr As String
    On Error Resume Next
    connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\abc.mdb;"
    conn.Open connStr
    sqlStr = "SELECT * FROM aaa"
    rs.Open sqlStr, conn
    For i = 1 To rs.Fields.Count
        Range("a" & i).Value = rs.Fields(i - 1)
    Next i
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

shiruiqiang

  For i = 1 To rs.Fields.Count
        Range("a" & i).Value = rs.Fields(i - 1)
    Next i
Range("A" & Rows.Count).End(xlUp).Offset(1).CopyFromRecordset

baofa2

1. Sub test1()
   
2.   Dim Conn As Object, rs As Object, SQL As String, i As Integer
   
3.   Set Conn = CreateObject("ADODB.Connection")
   
4.   Set rs = CreateObject("ADODB.Recordset")
   
5.   If Application.Version < 12 Or InStr(Application.Path, "WPS") > 0 Then
   
6.     Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\abc.mdb"
   
7.   Else
   
8.     Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\abc.mdb"
   
9.   End If
   
10.   SQL = "SELECT * FROM aaa"
    
11.   rs.Open SQL, Conn, 1, 3
    
12.   With Range("A1")
    
13.     .CurrentRegion.ClearContents
    
14.     For i = 0 To rs.Fields.Count - 1
    
15.       .Offset(, i).Value = rs.Fields(i).Name
    
16.     Next
    
17.     .Offset(1).CopyFromRecordset rs
    
18.   End With
    
19.   rs.Close
    
20.   Set rs = Nothing
    
21.   Conn.Close
    
22.   Set Conn = Nothing
    
23. End Sub

复制代码

baofa2

abc.zip (31.08 KB, 下载次数: 11)

2024-4-3 17:38 上传

点击文件名下载附件

zjdh

Sub domdb()
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim connStr As String, sqlStr As String
    On Error Resume Next
    connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\abc.mdb;"
    conn.Open connStr
    sqlStr = "SELECT * FROM aaa"
    rs.Open sqlStr, conn
    For i = 1 To rs.Fields.Count
        Cells(1, i).Value = rs.Fields(i - 1).Name
    Next i
    Rows("2:1000").ClearContents
    Range("A" & Rows.Count).End(xlUp).Offset(1).CopyFromRecordset rs
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

yxp

baofa2 发表于 2024-4-3 17:33

感谢，原来是 jet 引擎没有，用 ace 就可以