Import data from Access to Excel (ADO)

With the procedure below you can import data from an Access table to a worksheet.

Sub ADOImportFromAccessTable(DBFullName As String, _
    TableName As String, TargetRange As Range)
' Example: ADOImportFromAccessTable "C:\FolderName\DataBaseName.mdb", _
    "TableName", Range("C1")
Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
    Set TargetRange = TargetRange.Cells(1, 1)
    ' open the database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
        DBFullName & ";"
    Set rs = New ADODB.Recordset
    With rs
        ' open the recordset
        .Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable 
        ' all records
        '.Open "SELECT * FROM " & TableName & _
            " WHERE [FieldName] = 'MyCriteria'", cn, , , adCmdText 
        ' filter records
        
        RS2WS rs, TargetRange ' write data from the recordset to the worksheet
        
'        ' optional approach for Excel 2000 or later (RS2WS is not necessary)
'        For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
'            TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
'        Next
'        TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data

    End With
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

You'll find the procedure RS2WS by clicking on this link.

The macro examples assumes that your VBA project has added a reference to the ADO object library.
You can do this from within the VBE by selecting the menu Tools, References and selecting Microsoft ActiveX Data Objects x.x Object Library.
Use ADO if you can choose between ADO and DAO for data import or export.

Document last updated 2001-11-27 12:46:39 Printerfriendly version

User comments:

Ole P. from Norway wrote (2006-09-22 18:39:16 CET):

Re: Advanced SQL statements
I guess you have done the creating of your views correct.
Views created using VB/VBA will not be visible in the Access user interface and will only be available to VB/VBA procedures/functions. Access 11 might have fixed this...

Nicolas B. from Frankfurt/Main, Germany wrote (2006-09-22 10:29:26 CET):

Advanced SQL statements
Hi Ole,

I need some special SQL statements to import data from Access databanks to an Excel sheet. It seems that you are quite fit with this.
I'd like te create views (these virtual tables, that can receive the result-sets of a SQL query). I've looked around on differents websites, but all the proposed solutions failed.
I tried: "CREATE VIEW [Data_1] AS SELECT...(rest of a successful query)",
and also: "CREATE VIEW Data_1 AS SELECT...(rest of a successful query)".
But none works.

Have you any idea or advice to create such views ?
Thanks a lot in advance.

Ole P. from Norway wrote (2006-06-29 10:12:28 CET):

Re: Getting the tablenames in a connected Access file.
ADO alone can't do this. You need to reference the "Microsoft ADO Ext. 2.x For DDL and Security" library to be able to enumerate the contents of an Access (or any other database-type). Below is a small example.
Call the procedure like this from your own code where you already has created an ADO connection object to your Access database:
EnumerateConnectionContent cn ' cn must be an open ADO connection object.

Sub EnumerateConnectionContent(cn As ADODB.Connection)
' requires a reference to the ActiveX DataObjects library and the ADO Ext. library
Dim cat As ADOX.Catalog, tbl As ADOX.Table, fld As ADOX.Column, v As ADOX.View, p As ADOX.Procedure

If cn Is Nothing Then Exit Sub
If cn.State <> adStateOpen Then Exit Sub

Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cn

Application.StatusBar = "Listing tables and fields..."
For Each tbl In cat.Tables
Debug.Print "Table: " & tbl.Name & ", Type: " & tbl.Type & ", Field Count: " & tbl.Columns.Count
For Each fld In tbl.Columns
Debug.Print "Field: " & fld.Name & " (" & fld.Type & ")"
Next fld
Next tbl
Debug.Print
Set tbl = Nothing
Set fld = Nothing

Application.StatusBar = "Listing views..."
On Error Resume Next
For Each v In cat.Views
Debug.Print "View: " & v.Name & ", Command: " & v.Command
Next v
On Error GoTo 0
Debug.Print
Set v = Nothing

Application.StatusBar = "Listing procedures..."
On Error Resume Next
For Each p In cat.Procedures
Debug.Print "Procedure: " & p.Name & ", Command: " & p.Command
Next p
On Error GoTo 0
Debug.Print
Set p = Nothing

Application.StatusBar = False

' free memory
Set cat = Nothing
End Sub

Nicolas B. from Frankfurt, Germany wrote (2006-06-29 09:36:18 CET):

Getting the tablenames in a connected Access file.
Hi Ole,

Usually Access file can be composed of one or more tables.
Is it thus possible by means of ADO to check the names of all the tables of a connected Access file ?
For instance, in your example above, is there any property of cn which can give the name of the 1st, 2nd,... tables back?

Thanks a lot in advance.

Ole P. from Norway wrote (2006-06-20 22:28:24 CET):

Re: Determine row count of recordset
Something like this will usually return the count of records/rows in a recordset:
lngCountOfReturnedRecords = rs.RecordCount

The RecordCount property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support the RecordCount property.

DL Bailey from Frazer ,PA wrote (2006-06-20 12:55:15 CET):

Determine row count of recordset
What would be the code to determine if a recordset has greater than 65,536 rows. If it does then I would want to put in a MSG Box that would say "To Many Rows for Excel"

Seems simple but I'm stuck..... Can someone give a solution?. Thanks in advance

Ole P. from Norway wrote (2006-06-13 08:53:50 CET):

Re: adCmdText
See the last paragraph in the example above...

SC from Schaumburg, IL wrote (2006-06-09 22:13:10 CET):

adCmdText
The program works fine on my machine, but on others it says "Compile error: Can't find the project or library." How to fix it.

Ole P. from Norway wrote (2006-05-09 11:28:06 CET):

Re: SELECT * FROM WHERE
You can achieve this by doing something like this:
strSQL = "SELECT * FROM " & TableName
strSQL = strSQL & " WHERE [FieldName] = '" & Worksheets(1).Range("A1").Text & "'"
.Open strSQL, cn, , , adCmdText

andreat from UK wrote (2006-05-08 12:52:39 CET):

SELECT * FROM WHERE
Hello everybody,

great code, but can I use MyCriteria as a variable i.e MySheet(1).Range("A1"). It will be useful to me

Thanks