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

 

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

7 thoughts on “Get Data from Access Database using Excel VBA”

  1. 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

  2. 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

  3. Hi i used your code and it works almost fine for me, there is however just one thing i would like to tweak:

    In the Source Line here it says “Source = “SELECT * FROM Customers WHERE [Job Title] = ‘Owner'” ”
    I would like to change (Owner) to (Range(“J1”).VALUE)
    that way i can get the correct results every time i run the code by just modifying range J1

  4. I have problems Excel VBA macros, can not save the data from listview to access the database. I created this code, you may be able to fix the location of my drawback. Thank you in advance

    Private Sub CommandButton19_Click()
    Dim ComandoSQL As String
    Dim id
    id = TextBox1
    ComandoSQL = “Select NomorID_ Nomor_Nota, Date , ” _
    & “Kode_Peg, Name, Kode_Wil, ” _
    & “Keca FROM Data_Order;”

    Call Koneksi
    Set fian = fila.OpenRecordset(ComandoSQL)

    s = 1
    Do While Not fian.EOF
    With ListView2
    .ListItems.Add , , fian(0)
    .ListItems(s).ListSubItems.Add , , fian(1)
    .ListItems(s).ListSubItems.Add , , fian(2)
    .ListItems(s).ListSubItems.Add , , fian(3)
    .ListItems(s).ListSubItems.Add , , fian(4)
    .ListItems(s).ListSubItems.Add , , fian(5)
    s = s + 1
    End With
    ‘ fian.MoveNext
    fian.Update
    Loop
    fian.Close
    fila.Close
    Call Putus
    MsgBox “Data Success!”, vbDefaultButton1, “Information”
    Call New
    Exit Sub
    End Sub

  5. instead of using Range(“A1”).Offset(1, 0).CopyFromRecordset Recordset, is there anyway that can I copy to a column that I want
    For example, my result from sql is FullName, DOB, Address, and I want it show on Column A, C, and F instead.

    Thank you

Leave a Reply

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