Retrieve data from Access-database to Excel with ADO

  • A quite frequent questions on several forums is how to connect to a so called Access-database (per se MS Jet Database) and retrieve data to Excel.


    The tips also shows how we can use several Recordset with one connection.


    One of the critical process when working with database is to reduce the time we are connected to the database, i e working with the Recordset. If we experience a slow datatransfer we then may consider to disconnect the Recordset(s).


    In the tip You will see how easy it is to create a disconnected Recordset.


    What we need:
    * Microsoft Windows 2000 or above
    * Microsoft Excel 2000 or above
    * MDAC 2.5 or above
    * A Jet database (mdb)


    Step 1
    Add a reference to the Microsoft ActiveX Data Object x.x library.


    Step 2
    Insert following procedure in a standardmodule:



    For those of You who prefer to use a tool instead You may check out:
    SQL Tester


    Kind regards,
    Dennis