如何使用VBA将访问数据库(.accdb)中的数据检索到excel中 [英] How to retrieve data from access database(.accdb) into excel using vba

问题描述

我正在使用Excel VBA宏，我想从MS Access数据库(.accdb文件)中检索数据.

I'm working on Excel VBA macros and I want to retrieve data from a MS Access database (.accdb file).

我尝试使用下面的连接字符串，它会引发运行时错误"438"

I've tried using below connection string and it throws runtime error '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

请帮我解决错误

推荐答案

我尝试使用下面的连接字符串，它会引发运行时错误"438"

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时出现的错字?

Beside the above, I guess DBFullName = "D:\Tool_Database\Tool_Database.mdb" is a typo from your end as you are using .Accdb?

这篇关于如何使用VBA将访问数据库(.accdb)中的数据检索到excel中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！