excel - 如何使用vba将access数据库(.accdb)中的数据检索到Excel中

Question

我正在处理 Excel VBA 宏，并且想要从 MS Access 数据库(.accdb 文件)检索数据。

我尝试使用下面的连接字符串，它抛出运行时错误“438”

   Dim cn As Object, rs As Object,DBFullName As String,Target As Range
   DBFullName = "D:\Tool_Database\Tool_Database.accdb"
   Set Target = Sheets("Sheet1").Range("A1")
   Set cn = CreateObject("ADODB.Connection")
   cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & DBFullName & ";"

   Set rs = CreateObject("ADODB.Recordset")
   rs.Open "SELECT * FROM test", cn, , , adCmdText

  For int i = 0 To rs.Fields.Count - 1
  Target.Offset(1, i).Value = rs.Fields(i).Name
  Next
  Target.Offset(1, 0).CopyFromRecordset rs
  rs.Close
  Set rs = Nothing
  cn.Close
  Set cn = Nothing

请帮助我解决错误

Answer 1

I've tried using below connection string and it throws runtime error '438'

Run-time error: '438'意味着 Object doesn't support this property or method..

您收到该错误是因为您正在混合 VB.Net与 VBA

这个

For int i = 0 To rs.Fields.Count - 1

应该是

For i = 0 To rs.Fields.Count - 1

除了上述之外，我猜DBFullName = "D:\Tool_Database\Tool_Database.mdb"是您使用 .Accdb 时的拼写错误？