Results 1 to 2 of 2
Thread: Object Explorer in VBA (2003)
2008-02-20, 19:14 #1
- Join Date
- Mar 2006
- Whittier, California, USA
- Thanked 0 Times in 0 Posts
Object Explorer in VBA (2003)
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?
2008-02-20, 20:09 #2
- Join Date
- Mar 2002
- Thanked 28 Times in 28 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>.