Access VBA DAO CopyFromRecordset Method to Copy Selected Fields Data from Access Table to Excel

Access VBA DAO CopyFromRecordset Method to Copy Selected Fields or SQLQuery Results Data from Access Table to Excel

How to Transfer Selected Fields/SQL Query Result data from an Access DAO Recordset to Excel with VBA Macro
'Using ADO Method to Import data from an Access Database Table to an Excel worksheet, we need to use ADO in your VBA project, you must add a reference to the ADO Object Library in Excel.
'By clicking Tools-References in VBA, and then choose an appropriate version of Microsoft ActiveX Data Objects x.x Library from the list, as shown below :

The following Macro has written in Excel VBA Module to Import/Copy Data from a Selected Fields/SQL Query results from Access Database Table into an Excel Sheet using DAO Recordset Method.

'-------------------------------------------------

Sub DAO_Access_Slected_2_Excel()

Dim MyPath As String, DBName As String, MyDB As String, StrSQL As String

Dim J As Long, K As Long, FieldCount As Long

Dim DAO_DB As DAO.Database

Dim DAO_RecSet As DAO.Recordset

Dim WS As Worksheet

Dim Rng As Range

DBName = "Sales_Database.accdb"

MyPath = ThisWorkbook.Path

MyDB = MyPath & "\" & DBName

My_Table = "Sales_Table"

'Assigning the Database Reference to an object variable:

Set DAO_DB = DBEngine.Workspaces(0).OpenDatabase(MyDB)

Set WS = ActiveWorkbook.Sheets("Data")

WS.Activate

WS.Range("A1").Select

'Opening a Recordset to Copy Specific Fields/SQL Query Result Data from a Access Table named "Sales_Table" :

Str_SQL = "SELECT Sales_Period, Prod_Id, NetSales From SALES_TABLE Where NetSales >500"

Set DAO_RecSet = DAO_DB.OpenRecordset(Str_SQL, dbOpenDynaset)

Set Rng = WS.Range("A1")

FieldCount = DAO_RecSet.Fields.Count

For J = 0 To FieldCount - 1

'Copy Column Names to First Row of the Worksheet:

Rng.Offset(0, J).Value = DAO_RecSet.Fields(J).Name

DAO_RecSet.MoveFirst

'Copying Records from Recordset and Pasting from Second Row of the Worksheet:

K = 1

Do While Not DAO_RecSet.EOF

Rng.Offset(K, J).Value = DAO_RecSet.Fields(J).Value

DAO_RecSet.MoveNext

K = K + 1

Loop

Next J

'Selecting a All Columns in Data range to Auto Filt:

Range(WS.Columns(1), WS.Columns(FieldCount)).AutoFit

'Selecting a All Columns in Data range to Delete:

'Range(WS.Columns(1), WS.Columns(FieldCount)).Delete

'Closing the RecordSet and DB

DAO_RecSet.Close

DAO_DB.Close

MsgBox "All the Records Copied from Target Access Table To Excel Sheet", vbOKCancel, "JobDone"

'Destroying the Variables

Set ADO_RecSet = Nothing

Set DAO_DB = Nothing

End Sub

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------