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:

    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.