Get Data from Access Database using Excel VBA

If we can automate the process of getting data from Access database using Excel VBA then we can enhance our data analytical capabilities. Earlier we had learnt how to get data from an access database manually.

Watch the training video to learn how you can quickly and easily get data into an Excel worksheet for further analysis from a database  using VBA.

 

Watch the video on YouTube.

Here’s the complete VBA code to import data into an Excel worksheet from an Access database using VBA:

Option Explicit

Sub getDataFromAccess()
‘ Click on Tools, References and select
‘ the Microsoft ActiveX Data Objects 2.0 Library

Dim DBFullName As String
Dim Connect As String, Source As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer

Cells.Clear

‘ Database path info

‘ Your path will be different
DBFullName = “C:\Users\takyar\Documents\NorthWind.accdb”
‘ Open the connection
Set Connection = New ADODB.Connection
Connect = “Provider=Microsoft.ACE.OLEDB.12.0;”
Connect = Connect & “Data Source=” & DBFullName & “;”
Connection.Open ConnectionString:=Connect

‘ Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
‘ Filter Data
Source = “SELECT * FROM Orders WHERE [Shipper ID] = 3 ”
‘Source = “SELECT * FROM Customers WHERE [Job Title] = ‘Owner’ ”

.Open Source:=Source, ActiveConnection:=Connection

‘ MsgBox “The Query:” & vbNewLine & vbNewLine & Source

‘ Write field names
For Col = 0 To Recordset.Fields.Count – 1
Range(“A1”).Offset(0, Col).Value = Recordset.Fields(Col).Name
Next

‘ Write recordset
Range(“A1”).Offset(1, 0).CopyFromRecordset Recordset
End With
ActiveSheet.Columns.AutoFit
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub

 

4 thoughts on “Get Data from Access Database using Excel VBA

  1. mustakin

    Sub Refresh()
    ‘ Click on Tools, References and select
    ‘ the Microsoft ActiveX Data Objects 2.0 Library
    Dim DBFullName As String
    Dim Connect As String, Source As String
    Dim Connection As ADODB.Connection
    Dim Recordset As ADODB.Recordset
    Dim Col As Integer
    Cells.Clear
    ‘ Database path info
    ‘ Your path will be different
    DBFullName = “G:\Factory_AllReport.accdb”

    ‘ Open the connection
    Set Connection = New ADODB.Connection
    Connect = “Provider=Microsoft.ACE.OLEDB.12.0;”
    Connect = Connect & “Data Source=” & DBFullName & “;”
    Connection.Open ConnectionString:=Connect

    ‘ Create RecordSet
    Set Recordset = New ADODB.Recordset
    With Recordset

    ‘ Filter Data

    Source = “SELECT * FROM Basic_Info_QC Query”
    ‘Source = “SELECT * FROM Customers WHERE [Job Title] = ‘Owner’ ”

    .Open Source:=Source, ActiveConnection:=Connection

    ‘ MsgBox “The Query:” & vbNewLine & vbNewLine & Source
    ‘ Write field names
    For Col = 0 To Recordset.Fields.Count – 1
    Range(“A1”).Offset(0, Col).Value = Recordset.Fields(Col).Name
    Next
    ‘ Write recordset
    Range(“A1”).Offset(1, 0).CopyFromRecordset Recordset
    End With
    ActiveSheet.Columns.AutoFit
    Set Recordset = Nothing
    Connection.Close
    Set Connection = Nothing

    End Sub

    Reply
  2. Jhoy

    Hi I am a novice in macro and I am creating something like I need to get data from my access file to output in excel file. I want to just have the data that are within date range inputted in the input box. However I keep having the error “No value given for one or more required parameters”

    My code is below:

    Option Explicit

    Sub getDataFromAccess()
    ‘Click on Tools, References and select
    ‘ the Microsoft ActiveX Data Objects 2.0 Library

    Dim DBFullName As String
    Dim Connect As String, Source As String
    Dim Connection As ADODB.Connection
    Dim Recordset As ADODB.Recordset
    Dim Col As Integer
    Dim W1Startdate As Date, W1Enddate As Date

    W1Startdate = Application.InputBox(“Enter the Start Date”)
    W1Enddate = Application.InputBox(“Enter the End Date”)

    Cells.Clear

    ‘ Database path info

    ‘ Your path will be different
    DBFullName = “C:\Users\CrisJhoyLim\Documents\Manila DI.accdb”

    ‘ Open the connection
    Set Connection = New ADODB.Connection
    Connect = “Provider=Microsoft.ACE.OLEDB.12.0;”
    Connect = Connect & “Data Source=” & DBFullName & “;”
    Connection.Open ConnectionString:=Connect

    ‘ Create RecordSet
    Set Recordset = New ADODB.Recordset
    With Recordset
    ‘ Filter Data

    ‘Source = “SELECT * FROM Pfizer WHERE [OPENTIME] >= #6/1/2014# and [OPENTIME] = W1StartDate and OPENDATE < W1Enddate"

    'Source = "SELECT * FROM Customers WHERE [Job Title] = 'Owner'"

    .Open Source:=Source, ActiveConnection:=Connection

    ' MsgBox “The Query:” & vbNewLine & vbNewLine & Source

    ' Write field names
    For Col = 0 To Recordset.Fields.Count – 1
    Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
    Next

    ' Write recordset
    Range("A1").Offset(1, 0).CopyFromRecordset Recordset
    End With
    ActiveSheet.Columns.AutoFit
    Set Recordset = Nothing
    Connection.Close
    Set Connection = Nothing
    End Sub

    Reply
    1. Dinesh Kumar Takyar Post author

      Use a code like so:
      Source = “SELECT * FROM Orders WHERE [Order date]>=#04/01/2006# and [Order Date]<=#04/30/2006#" Of course, your table name and headers will be according to your data-base.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *