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