Create QueryTable and populate with ADO Recordset

  • By creating QueryTables we get so called dynamic database queries that can be updated on a regular basis.


    In order to create QueryTables manullay we need MS Query but by doing it in VBA it can be done without the add-in.


    However, the QueryTable rely heavily on ODBC which may be considered as an old fashion technique which I tend to agree on.


    So when applying an ADO-approach we need to be aware of that we can't update it in the same way as we can do when using the ODBC-approach.


    For more details about the ODBC-approach please see the following article:
    http://www.excelkb.com/article.aspx?id=10167


    What we need:
    * Microsoft Windows 2000 or above
    * Microsoft Excel 2000 or above
    * MDAC 2.5 or above
    * A database and in the example below a local SQL Server 2000 is used.


    Step 1
    Add a reference to the Microsoft ActiveX Data Object 2.x library via Tools | References... in the VB-editor.


    Step 2
    Add a standardmodule to the workbook and then add the following procedure.



    Personally I would like to see some major improvements of the QueryTable enabling us to manipulate its contents and properties with ADO.