How to retrieve data from access database(.accdb) into excel using vba

Question

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

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

PLease help me to resolve the error

You say it's an .accdb file - then why are you usnig an .mdb extension in your connection string?? DBFullName = "D:\Tool_Database\Tool_Database.mdb" ?? — marc_s
– marc_s, Commented Sep 2, 2016 at 6:28

Siddharth Rout · Accepted Answer · 2016-09-02 06:59:20Z

2

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

Run-time error: '438' means that the Object doesn't support this property or method..

You are getting that error because you are mixing VB.Net with VBA

This

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

should be

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

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

answered Sep 2, 2016 at 6:59

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user2514925 Over a year ago

yes .mdb is a typo. How to resolve this error do i need to add any reference

Balasubramaniyan Ramadoss Over a year ago

I tried the above code and it is working fine for the database I have. I doubt about the data in the database. Try this recordset open command: rs.Open "SELECT * FROM Books", cn, adOpenStatic, adLockReadOnly

Balasubramaniyan Ramadoss Over a year ago

@ Siddharth Rout: I am Not user2514925

Siddharth Rout Over a year ago

@BalasubramaniyanRamadoss: I didn't understand your comment then :)

Balasubramaniyan Ramadoss Over a year ago

@ Siddharth Rout: I don't get any error while trying the above given code with different database.

|

user7075507 · Accepted Answer · 2016-10-31 17:40:16Z

This should do it for you. Drop the WHERE clause if you don't want to apply a filter.

Also, set a reference to: Microsoft ActiveX Data Objects 2.8 Library

Sub Select_From_Access()
    Dim cn As Object, rs As Object
    Dim intColIndex As Integer
    Dim DBFullName As String
    Dim TargetRange As Range

    DBFullName = "C:\Users\Ryan\Desktop\Nwind_Sample.mdb"

    'On Error GoTo Whoa

    Application.ScreenUpdating = False

    Set TargetRange = Sheets("Select").Range("A1")

    Set cn = CreateObject("ADODB.Connection")
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"

    Set rs = CreateObject("ADODB.Recordset")
    rs.Open "SELECT * FROM [OrderDetails] WHERE [OrderID] = 10248", cn, , , adCmdText

    ' Write the field names
    For intColIndex = 0 To rs.Fields.Count - 1
    TargetRange.Offset(1, intColIndex).Value = rs.Fields(intColIndex).Name
    Next

    ' Write recordset
    TargetRange.Offset(1, 0).CopyFromRecordset rs

    Application.ScreenUpdating = True
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    On Error GoTo 0
    Exit Sub

End Sub

Collectives™ on Stack Overflow

How to retrieve data from access database(.accdb) into excel using vba

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related