Tuesday, 12 May 2015

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

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



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

Sub DAO_Access_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"         ' Your Database Name
MyPath = ThisWorkbook.Path                      ' Path of Your Database
MyDB = MyPath & "\" & DBName                ' Full Path of Your Database
My_Table = "Sales"                                       ' Your Table Name

'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

'----------------------------------------------------------------------------------------------
'--------------------------------------------------------------
' Method - I :
Opening a Recordset to Copy all Fields Data from a Access Table named "Sales_Table":
'--------------------------------------------------------------
Set DAO_RecSet = DAO_DB.OpenRecordset("Sales_Table")

'--------------------------------------------------------------
' Method - II :
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
Next I

'Copying Records fro Recordset and Pasting from Second Row of the Worksheet:
Rng.Offset(1, 0).CopyFromRecordset DAO_RecSet

'To Copy only 6 rows and 3 columns of the Recordset to Excel Worksheet:
'Rng.Offset(1, 0).CopyFromRecordset Data:=DAO_RecSet, MaxRows:=6, MaxColumns:=3

'Selecting a All Columns in Data range in Excel to Auto Filt:
Range(WS.Columns(1), WS.Columns(FieldCount)).AutoFit
'Selecting a All Columns in Data range in Excel to Delete:
'Range(WS.Columns(1), WS.Columns(FieldCount)).Delete

'Closing the RecordSet
DAO_RecSet.Close
DAO_DB.Close

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

'Destroying the Variables
Set ADO_RecSet = Nothing
Set DAO_DB = Nothing

End Sub

Thanks,
TAMATAM
          [ BI-Reporting Analyst ]

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts