Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Mar 2006
    Whittier, California, USA
    Thanked 0 Times in 0 Posts

    Object Explorer in VBA (2003)

    Hello All,

    A real programmer (I am strictly an amatuer) recently showed me how he creates queries using SQL Server Manager. He can open a window on the left called Object Explorer and use it to display the fields of any table in the database. These fields can then be dragged directly into a query. This is so much more efficient than what I do with VBA in Access - constantly using Alt + F11 to switch from my code to the table design view to get the names of the fields I need.

    Is there a similar (or any easier) way to do this in VBA?



  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Object Explorer in VBA (2003)

    You can create a query in design view in Access, test it, then switch to SQL view (View | SQL) to see the SQL statement of the query that you built interactively.
    When you transfer the SQL to the Visual Basic Editor, you have to take the following gotchas into account:

    1) Access inserts line breaks into the SQL statement. You have to replace these with spaces for use in VBA.

    2) You'll probably want to assign the SQL to a string variable, so you must enclose the SQL statement in double quotes. If the SQL statement contains quoted strings, this will confuse VBA. The workaround is to replace double quotes <code>"</code> within the SQL statement with double double quotes <code>""</code> or with single quotes <code>'</code>.
    Attached Images Attached Images
    • File Type: png x.png (26.8 KB, 0 views)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts