Using UserForm parameters in ADO SQL Queries

  • I see a good many questions nowadays about how we can use controls on
    a User Form to dictate changes to a SQL query for returning records to
    Excel from a database.


    Good news I guess... It means more people are getting into using VBA & ADO
    to talk to their databases. :)


    Anyway, I thought I would post up an example of how you can use UserForm controls
    to determine a SQL query to either return ALL records from a table or
    via use of a checkbox and combobox, filter the records to return only a subset.


    The attached example uses the Northwind database, as this comes as standard with
    any Access installation. You may need to amend the path to your copy of Northwind.
    To do this, edit the stCon variable at the top of the UserForm code module (shown below
    also).


    Make sure you also have a reference set to Microsoft ActiveX Data Components v2.5 or later
    in your VBE.


    For those who cannot download files/attachments... the code is displayed below.


    With thanks as always to my friend Dennis Wallentin, especially for teaching me about
    Error Checking methods with ADO.