Import or Export Data from Access to Excel using ADO
Microsoft Access: ActiveX Data Objects (ADO), Connect with Access Databases from Excel using VBA.
Part 3 of 4
Microsoft Access: ActiveX Data Objects (ADO), Connect with Access Databases from Excel using VBA:
1. Microsoft Access: ActiveX Data Objects Library (ADO).
2. Microsoft Access: Use ADO to Execute SQL statements.
3. Import or Export Data from Access to Excel using ADO.
4. Microsoft Access: ActiveX Data Objects Extensions (ADOX).
————–
Also Read:
———————————————————————————————————
Contents:
Use ADO to Import Data from Microsoft Access Database to Excel
Use ADO to Import data from an Access Database Table to an Excel worksheet (your host application)
Use ADO to Export data from Excel worksheet (your host application) to Access Database Table
——————————————————————————————————–
Use ADO to Import Data from Microsoft Access Database 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 ADO to: (i) import or retrieve data from Access database to Excel worksheet; and (ii) Export data from Excel worksheet to Access Database Table.
Range.CopyFromRecordset Method: This method is commonly used to copy records 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 9: Using ADO to Import data from an Access Database Table to an Excel worksheet (your host application).
Refer to Images 9a, 9b, 9c, 9d and 9e as mentioned in the code.
The code is simple to understand, though apparantly long due to multiple options shown (reference to images 9a to 9e) as to how data can be imported into Excel. Each option can be treated as a separate code and run accordingly.
Sub automateAccessADO_9()
‘Using ADO to Import data from an Access Database Table to an Excel worksheet (your host application).
‘refer Image 9a to view the existing SalesManager Table in MS Access file “SalesReport.accdb”.
‘To use ADO in your VBA project, you must add a reference to the ADO Object Library in Excel (your host application) by clicking Tools-References in VBE, and then choose an appropriate version of Microsoft ActiveX Data Objects x.x Library from the list.
‘————–
‘DIM STATEMENTS
Dim strMyPath As String, strDBName As String, strDB As String, strSQL As String
Dim i As Long, n As Long, lFieldCount As Long
Dim rng As Range
‘instantiate an ADO object using Dim with the New keyword:
Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection
‘————–
‘THE CONNECTION OBJECT
strDBName = “SalesReport.accdb”
strMyPath = ThisWorkbook.Path
strDB = strMyPath & “\” & strDBName
‘Connect to a data source:
‘For pre – MS Access 2007, .mdb files (viz. MS Access 97 up to MS Access 2003), use the Jet provider: “Microsoft.Jet.OLEDB.4.0”. For Access 2007 (.accdb database) use the ACE Provider: “Microsoft.ACE.OLEDB.12.0”. The ACE Provider can be used for both the Access .mdb & .accdb files.
connDB.Open ConnectionString:=“Provider = Microsoft.ACE.OLEDB.12.0; data source=” & strDB
‘————–
‘OPEN RECORDSET, ACCESS RECORDS AND FIELDS
Dim ws As Worksheet
‘set the worksheet:
Set ws = ActiveWorkbook.Sheets(“Sheet8”)
‘Set the ADO Recordset object:
Set adoRecSet = New ADODB.Recordset
‘Opening the table named SalesManager:
strTable = “SalesManager”
‘————–
‘COPY RECORDS FROM ALL FIELDS USING CopyFromRecordset:
‘refer Image 9b to view records copied to Excel worksheet
‘open recordset/table:
adoRecSet.Open Source:=strTable, ActiveConnection:=connDB, CursorType:=adOpenStatic, LockType:=adLockOptimistic
Set rng = ws.Range(“A1”)
lFieldCount = adoRecSet.Fields.count
For i = 0 To lFieldCount – 1
‘copy column names in first row of the worksheet:
rng.Offset(0, i).Value = adoRecSet.Fields(i).Name
Next i
‘copy record values starting from second row of the worksheet:
rng.Offset(1, 0).CopyFromRecordset adoRecSet
‘to copy 4 rows and 3 columns of the recordset to excel worksheet:
‘rng.Offset(1, 0).CopyFromRecordset Data:=adoRecSet, 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
adoRecSet.Close
‘————–
‘COPY RECORDS FROM SELECTED FIELDS USING CopyFromRecordset:
‘refer Image 9c to view records copied to Excel worksheet
‘copy all records from the selected fields (EmployeeId, FirstName & JoinDate):
strSQL = “SELECT EmployeeId, FirstName, JoinDate FROM SalesManager WHERE EmployeeId > 15″
adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic, LockType:=adLockOptimistic
Set rng = ws.Range(“A1”)
lFieldCount = adoRecSet.Fields.count
For i = 0 To lFieldCount – 1
‘copy column names in first row of the worksheet:
rng.Offset(0, i).Value = adoRecSet.Fields(i).Name
Next i
‘copy record values starting from second row of the worksheet:
rng.Offset(1, 0).CopyFromRecordset adoRecSet
‘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
adoRecSet.Close
‘————–
‘COPY RECORDS FROM ALL FIELDS OF A RECORDSET:
‘refer Image 9d to view records copied to Excel worksheet
adoRecSet.Open Source:=strTable, ActiveConnection:=connDB, CursorType:=adOpenStatic, LockType:=adLockOptimistic
Set rng = ws.Range(“A1”)
lFieldCount = adoRecSet.Fields.count
For i = 0 To lFieldCount – 1
‘copy column names in first row of the worksheet:
rng.Offset(0, i).Value = adoRecSet.Fields(i).Name
adoRecSet.MoveFirst
‘copy record values starting from second row of the worksheet:
n = 1
Do While Not adoRecSet.EOF
rng.Offset(n, i).Value = adoRecSet.Fields(i).Value
adoRecSet.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
adoRecSet.Close
‘————–
‘COPY RECORDS FROM SELECTED FIELDS OF A RECORDSET:
‘refer Image 9e 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”
adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic, LockType:=adLockOptimistic
Set rng = ws.Range(“A1”)
lFieldCount = adoRecSet.Fields.count
For i = 0 To lFieldCount – 1
‘copy column names in first row of the worksheet:
rng.Offset(0, i).Value = adoRecSet.Fields(i).Name
adoRecSet.MoveFirst
‘copy record values starting from second row of the worksheet:
n = 1
Do While Not adoRecSet.EOF
rng.Offset(n, i).Value = adoRecSet.Fields(i).Value
adoRecSet.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
adoRecSet.Close
‘————–
‘close the objects
connDB.Close
‘destroy the variables
Set adoRecSet = Nothing
Set connDB = Nothing
End Sub
Example 10: Using ADO to Export data from Excel worksheet (your host application) to Access Database Table.
Refer to Images 10a, 10b & 10c, as mentioned in the code.
Sub automateAccessADO_10()
‘Using ADO to Export data from Excel worksheet (your host application) to an Access Database Table.
‘refer Image 10a to view the existing SalesManager Table in MS Access file “SalesReport.accdb”
‘refer Image 10b for data in Excel worksheet which is exported to Access Database Table.
‘refer Image 10c to view the SalesManager Table in Access file “SalesReport.accdb”, after data is exported.
‘To use ADO in your VBA project, you must add a reference to the ADO Object Library in Excel (your host application) by clicking Tools-References in VBE, and then choose an appropriate version of Microsoft ActiveX Data Objects x.x Library from the list.
‘————–
‘DIM STATEMENTS
Dim strMyPath As String, strDBName As String, strDB As String, strSQL As String
Dim i As Long, n As Long, lastRow As Long, lFieldCount As Long
‘instantiate an ADO object using Dim with the New keyword:
Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection
‘————–
‘THE CONNECTION OBJECT
strDBName = “SalesReport.accdb”
strMyPath = ThisWorkbook.Path
strDB = strMyPath & “\” & strDBName
‘Connect to a data source:
‘For pre – MS Access 2007, .mdb files (viz. MS Access 97 up to MS Access 2003), use the Jet provider: “Microsoft.Jet.OLEDB.4.0”. For Access 2007 (.accdb database) use the ACE Provider: “Microsoft.ACE.OLEDB.12.0”. The ACE Provider can be used for both the Access .mdb & .accdb files.
connDB.Open ConnectionString:=“Provider = Microsoft.ACE.OLEDB.12.0; data source=” & strDB
‘————–
‘OPEN RECORDSET, ACCESS RECORDS AND FIELDS
Dim ws As Worksheet
‘set the worksheet:
Set ws = ActiveWorkbook.Sheets(“Sheet9”)
‘Set the ADO Recordset object:
Set adoRecSet = New ADODB.Recordset
‘Opening the table named SalesManager:
strTable = “SalesManager”
adoRecSet.Open Source:=strTable, ActiveConnection:=connDB, CursorType:=adOpenStatic, LockType:=adLockOptimistic
‘————–
‘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 = adoRecSet.Fields.count
‘determine last data row in the worksheet:
lastRow = ws.Cells(Rows.count, “A”).End(xlUp).Row
‘start copying from second row of worksheet, first row contains field names:
For i = 2 To lastRow
adoRecSet.AddNew
For n = 0 To lFieldCount – 1
adoRecSet.Fields(n).Value = ws.Cells(i, n + 1)
Next n
adoRecSet.Update
Next i
‘————–
‘close the objects
adoRecSet.Close
connDB.Close
‘destroy the variables
Set adoRecSet = Nothing
Set connDB = Nothing
End Sub