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
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
sir, i face some problem ,please solve this database are not connect , myfile is g drive
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
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.
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
I am having this same issue. Did you ever figure it out?
Also I would like to be able to filter with multiple job titles.
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
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
Hello
I am in a macro and vba is a beginner.
I want to use a variable instead of the word “owner”, which is equal to one cell in sheet2.
I did it and got it wrong.
Can you help me?
Thanks