User Rating: 4 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Inactive
 

 

Microsoft Access DAO Object Model: Import or Export Data from Access to Excel

 

 

Microsoft Access: Data Access Objects Library (DAO), Connect with Access Databases from Excel using VBA.

Part 3 of 3

 


Microsoft Access: Data Access Objects Library (DAO), Connect with Access Databases from Excel using VBA.

1. Connecting to Microsoft Access Database from Excel VBA, using DAO Object Model.

2. Microsoft Access DAO Object Model: Create an Index, Create Relationship between Fields, Create and Execute a Query.

3. Microsoft Access DAO Object Model: Import or Export Data from Access to Excel.

 

-----------------

Also Read:

Microsoft Access: ActiveX Data Objects (ADO), Connect with Access Databases from Excel using VBA.


 

-------------------------------------------------------------------------------------------------

Contents:

Using Automation (DAO) to Copy Data from Microsoft Access to Excel

Import data from an Access Database Table to an Excel worksheet (your host application)

Export data from Excel worksheet (your host application) to an Access Database Table

-------------------------------------------------------------------------------------------------

 

Using Automation (DAO) to Copy Data from Microsoft Access to Excel: In this section we show, with the help of practical examples, how to connect to Access Database from Excel (your host application) using DAO to: (i) import or retrieve data from an Access database to Excel worksheet; and (ii) Export data from Excel worksheet to an Access Database Table.

 

 

Range.CopyFromRecordset Method: This method is commonly used to copy records (valid for both ADO or DAO Recordset objects) from an Aceess Table to an Excel worksheet. Syntax: Range.CopyFromRecordset(Data, MaxRows, MaxColumns).

 

Range is the worksheet range to which the records are copied, starting at its upper-left corner. Data is the Recordset (ie. set of records) in the Access database to be copied and the current row in the Recordset is the starting record from where copying begins. MaxRows and MaxColumns refer to the maximum numbers of rows (ie. records) and fields respectively to be copied and omitting these arguments will indicate that all rows and fields are copied. Data is mandatory to specify while other arguments of MaxRows and MaxColumns are optional.

 

 

Example 16: Import data from an Access Database Table to an Excel worksheet (your host application).

Refer Images 16a, 16b, 16c, 16d & 16e as mentioned in the code.

 

 

 

 

 

 

Sub AccessDAO_ImportFromAccessToExcel_16()
'Using DAO to Import data from an Access Database Table to an Excel worksheet (your host application).
'refer Image 16a to view the existing SalesManager Table in MS Access file "SalesReport.accdb".

'To use DAO in your Excel VBA project, you must add a reference to the DAO Object Library in Excel (your host application) by clicking Tools-References in VBE.

'--------------
Dim strMyPath As String, strDBName As String, strDB As String
Dim i As Long, n As Long, lLastRow As Long, lFieldCount As Long
Dim daoDB As DAO.Database
Dim recSet As DAO.Recordset

'---------------
'your data source with which to establish connection - ENTER the MS Access Database Name:
strDBName = "SalesReport.accdb"
'get path / location of the database, presumed to be in the same location as the host workbook:
strMyPath = ThisWorkbook.Path
'set the string variable to the Database:
strDB = strMyPath & "\" & strDBName

'assign the database reference to an object variable:
Set daoDB = DBEngine.Workspaces(0).OpenDatabase(strDB)

Dim ws As Worksheet
'set the worksheet:
Set ws = ActiveWorkbook.Sheets("Sheet8")

'--------------
'COPY RECORDS FROM ALL FIELDS USING CopyFromRecordset:
'refer Image 16b to view records copied to Excel worksheet

'Open a recordset based on a MS Access Table named "SalesManager":
Set recSet = daoDB.OpenRecordset("SalesManager")

Set rng = ws.Range("A1")
lFieldCount = recSet.Fields.count

For i = 0 To lFieldCount - 1

'copy column names in first row of the worksheet:

rng.Offset(0, i).Value = recSet.Fields(i).Name

Next i

 

'copy record values starting from second row of the worksheet:
rng.Offset(1, 0).CopyFromRecordset recSet
'to copy 4 rows and 3 columns of the recordset to excel worksheet:
'rng.Offset(1, 0).CopyFromRecordset Data:=recSet, MaxRows:=4, MaxColumns:=3

'select a column range:
Range(ws.Columns(1), ws.Columns(lFieldCount)).AutoFit
'worksheet columns are deleted because this code is only for demo:
Range(ws.Columns(1), ws.Columns(lFieldCount)).Delete

recSet.Close
Set recSet = Nothing

'--------------
'COPY RECORDS FROM SELECTED FIELDS USING CopyFromRecordset:
'refer Image 16c to view records copied to Excel worksheet

'copy records from the selected fields (EmployeeId, FirstName & JoinDate):
strSQL = "SELECT EmployeeId, FirstName, JoinDate FROM SalesManager WHERE EmployeeId > 15"
'Open a dynaset-type recordset based on a SQL statement:
Set recSet = daoDB.OpenRecordset(strSQL, dbOpenDynaset)

Set rng = ws.Range("A1")
lFieldCount = recSet.Fields.count

For i = 0 To lFieldCount - 1

'copy column names in first row of the worksheet:

rng.Offset(0, i).Value = recSet.Fields(i).Name

Next i


'copy record values starting from second row of the worksheet:
rng.Offset(1, 0).CopyFromRecordset recSet

'select a column range:
Range(ws.Columns(1), ws.Columns(lFieldCount)).AutoFit
'worksheet columns are deleted because this code is only for demo:
Range(ws.Columns(1), ws.Columns(lFieldCount)).Delete

recSet.Close
Set recSet = Nothing

'--------------
'COPY RECORDS FROM ALL FIELDS OF A RECORDSET:
'refer Image 16d to view records copied to Excel worksheet

'Open a recordset based on a MS Access Table named "SalesManager":
Set recSet = daoDB.OpenRecordset("SalesManager")

Set rng = ws.Range("A1")
lFieldCount = recSet.Fields.count

For i = 0 To lFieldCount - 1

'copy column names in first row of the worksheet:

rng.Offset(0, i).Value = recSet.Fields(i).Name

recSet.MoveFirst
'copy record values starting from second row of the worksheet:

n = 1

Do While Not recSet.EOF

rng.Offset(n, i).Value = recSet.Fields(i).Value

recSet.MoveNext

n = n + 1

Loop

Next i


'select column range to AutoFit column width:
Range(ws.Columns(1), ws.Columns(lFieldCount)).AutoFit
'worksheet columns are deleted because this code is only for demo:
Range(ws.Columns(1), ws.Columns(lFieldCount)).Delete

recSet.Close
Set recSet = Nothing

'--------------
'COPY RECORDS FROM SELECTED FIELDS OF A RECORDSET:
'refer Image 16e to view records copied to Excel worksheet

'copy all records from the 3 fields of EmployeeId, SurName, JoinDate:
strSQL = "SELECT EmployeeId, SurName, JoinDate FROM SalesManager"
'Open a dynaset-type recordset based on a SQL statement:
Set recSet = daoDB.OpenRecordset(strSQL, dbOpenDynaset)

Set rng = ws.Range("A1")
lFieldCount = recSet.Fields.count

For i = 0 To lFieldCount - 1

'copy column names in first row of the worksheet:

rng.Offset(0, i).Value = recSet.Fields(i).Name

recSet.MoveFirst
'copy record values starting from second row of the worksheet:

n = 1

Do While Not recSet.EOF

rng.Offset(n, i).Value = recSet.Fields(i).Value

recSet.MoveNext

n = n + 1

Loop

Next i


'select a column range:
Range(ws.Columns(1), ws.Columns(lFieldCount)).AutoFit
'worksheet columns are deleted because this code is only for demo:
Range(ws.Columns(1), ws.Columns(lFieldCount)).Delete

recSet.Close

'--------------
'close the objects:
daoDB.Close

'destroy the variables:
Set daoDB = Nothing

Set recSet = Nothing

End Sub

 

 

 

Example 17: Export data from Excel worksheet (your host application) to an Access Database Table.

Refer Images 17a, 17b & 17c as mentioned in the code.

 

 

 

 

Sub AccessDAO_ExportFromExcelToAccess_17()
'Using DAO to Export data from Excel worksheet (your host application) to an Access Database Table.
'refer Image 17a to view the existing SalesManager Table in MS Access file "SalesReport.accdb", indexed on the "EmployeeId" field.
'refer Image 17b for data in Excel worksheet which is exported to Access Database Table.
'refer Image 17c to view the SalesManager Table in Access file "SalesReport.accdb", after data is exported.

'To use DAO in your Excel VBA project, you must add a reference to the DAO Object Library in Excel (your host application) by clicking Tools-References in VBE.

'--------------
Dim strMyPath As String, strDBName As String, strDB As String
Dim i As Long, n As Long, lLastRow As Long, lFieldCount As Long
Dim daoDB As DAO.Database
Dim recSet As DAO.Recordset

'---------------
'your data source with which to establish connection - ENTER the MS Access Database Name:
strDBName = "SalesReport.accdb"
'get path / location of the database, presumed to be in the same location as the host workbook:
strMyPath = ThisWorkbook.Path
'set the string variable to the Database:
strDB = strMyPath & "\" & strDBName

'assign the database reference to an object variable:
Set daoDB = DBEngine.Workspaces(0).OpenDatabase(strDB)

Dim ws As Worksheet
'set the worksheet:
Set ws = ActiveWorkbook.Sheets("Sheet9")

'Open a recordset based on a MS Access Table named "SalesManager":
Set recSet = daoDB.OpenRecordset("SalesManager")

'--------------
'COPY RECORDS FROM THE EXCEL WORKSHEET:
'Note: Columns and their order should be the same in both Excel worksheet and in Access database table

lFieldCount = recSet.Fields.count
'determine last data row in the worksheet:
lLastRow = ws.Cells(Rows.count, "A").End(xlUp).Row

'start copying from second row of worksheet, first row contains field names:
For i = 2 To lLastRow

recSet.AddNew

For n = 0 To lFieldCount - 1

recSet.Fields(n).Value = ws.Cells(i, n + 1)

Next n

recSet.Update

Next i


'--------------
'close the objects:
recSet.Close
daoDB.Close

'destroy the variables:
Set daoDB = Nothing

Set recSet = Nothing

End Sub