Excel VBA- CopyFromRecordSet Method of Range Object

Excel VBA To Retrieve data from MS-Access Table

By Ajeet Kumar

Excel Range Object has a very interesting method called "CopyFromRecordSet". It can be used to fetch data from MS-Access.

In Excel VBA Developer help, we are given the below:

Range.CopyFromRecordset Method

Copies the contents of an ADO or DAO Recordset object onto a worksheet, beginning at the upper-left corner of the specified range. If the Recordset object contains fields with OLE objects in them, this method fails. Syntax

expression.CopyFromRecordset(Data, MaxRows, MaxColumns)

expression:   A variable that represents a Range object.



Option Explicit

Sub pCopyFromRecordSet()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim cmd As ADODB.Command
    Dim MyDBpath As String
    Dim strProvider As String
    Dim prc
   
    prc = Application.InputBox("Enter the price", "Price Above", 21000)
    MyDBpath = "E:\Exercise Excel\MobilesDB.mdb"
    strProvider = "Microsoft.Jet.OLEDB.4.0"
    ''create an instance of connection object
    Set cnn = New ADODB.Connection
    Debug.Print cnn.Provider
   
    ''open the connection
    With cnn
        .Provider = strProvider
        .Open MyDBpath
    End With
   
    ''create an instance of recordset
    Set rst = New ADODB.Recordset
    ''open the recordset
    ''Must provide the ActiveConnection value
    rst.Open Source:="SELECT * FROM Brands WHERE color='White' and Price" & ">=" & prc, ActiveConnection:=cnn
    Range("B2").CurrentRegion.Clear
    Range("B2").CopyFromRecordset rst

Set cnn = Nothing
Set rst = Nothing
Set cmd = Nothing

End Sub

Note: Access Database connection string is different for Access 2003 (*.mdb) and Access 2007/2010 (*.accdb) because Drivers are different for both the databases.

For Access 2003 Database the Provider is Microsoft.Jet.OLEDB.4.0. For Access 2007/2010 the Provider is Microsoft.ACE.OLEDB.12.0

So, we replace Jet by ACE.