Re: Use MS Query in VBA to only get a SQL statement

Thanks, I can see this might be quite possible.
The only question is if this could be done without making linked tables in Access.
This would be a lot of trouble as the Interbase database has 166 tables.
I suppose it would be quite possible to let Access make the query on the Interbase tables.

RBS

"K Dales" <KDales@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:AF9EEADE-5827-4D02-BFE9-78DE824AE5B3@xxxxxxxxxxxxxxxx
If I understood, the question was about using MSQuery interactively within
code, to help a "non-coding" user create a query from within Excel at runtime.

I agree with Al that Access would actually be a better option, but
implemented in a diferent way. I don't know of any way to manipulate MSQuery
under VBA control, but you have full freedom to do so with Access, assuming
your users will have Access on their machines. You could create an Access
database that has a linked table to your Interbase table. You could then use
Automation to open and run the Access database under VBA control, where you
could bring up a new query design grid or even the query wizard for your
Excel user, then grab that SQL string and close (or at least hide) Access -
then you can use the SQL however you need to.

If you are not familiar with the concept of Automation you will need to
research the help files, this newsgroup, and/or the MSDN library. But I will
give a basic description. To run Access under VBA control, you add a
reference to MS Access to your Excel VBA project, then declare an object
variable to refer to an Access session. You can then use the Access object
model in your Excel VBA project to start up Access (by default will be
invisible to user), manipulate it, make it visible when desired, hide it,
close it, etc... e.g:

Dim AccessApp As Access.Application ' defines the variable to hold an Access
session
Set AccessApp = New Access.Application ' starts the session - hidden
AccessApp.OpenCurrentDatabase MyDBFilePath ' opens an Access file
AccessApp.Visible = True ' shows your Access session to the user
AccessApp.Visible = False ' hides it but keeps it running

You can use VBA code to work with Access just as you would if you were
running it from an Access project as long as you prefix all your properties
and methods with AccessApp. (e.g. AccessApp.DoCmd.OpenQuery "My Query").
When done, quit your Access session, and destroy the object variable:

AccessApp.Quit
Set AccessApp = Nothing

I like your idea of providing users with a built-in interface to design a
query at runtime; with Automation I think you could implement it without
needing too much other than some basic automation knowledge and a few
commands (Start the query design/Make Access visible and pass control to the
user/when user is done read and store the SQL text and close the query/ hide
Access).

"Al" wrote:

The optimal place to get your SQL statements is from MS Access as Access will
allow you to have a where condition on a field not included in the Select
statement. After creating your Query using the Access GUI, you can switch to
SQL view and simply copy and paste the query into your Excel VBA module.



"RB Smissaert" wrote:

> I wonder if this were possible:
> Use MS Query to construct a SQL statement and use the statement in VBA > code
> (ADO, ODBC connecting to Interbase), but don't let it run the > statement.
> So just use the MS Query as a SQL wizard, incorporated in VBA code.
> Doing this would save a lot of coding to get a similar wizard, although > it
> shouldn't be too difficult either.
> One drawback I can see is that MS Query won't let you do a WHERE > condition
> on a field that is not in the SELECT
> clause. The good thing though is that it will automatically add any > joins,
> although I am not sure how it will handle
> joins that are not inner joins.
> Hope this explains it clear enough and thanks for any advice.
>
> RBS
>
> 

.

Relevant Pages

  • RE: SQL statement works in a query, but not in VBA
    ... This SQL statement below is confusing the heck out of me. ... I built my query from the query builder in Access. ... the SQL view, copied it, and pasted this into a VBA module. ... JOIN (Plant INNER JOIN AnalysisTestGroup ON" _ ...
    (microsoft.public.access.modulesdaovba)
  • Re: Dont want to wrap long text - just truncate
    ... document then postprocess the results using VBA, ... All you can do in the standard user interface is specify a table of view, ... you have to specify the SQL in an OpenDataSource method call in VBA. ... via MS Query, MS Query has to be installed, and you will need an ODBC DSN ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Use MS Query in VBA to only get a SQL statement
    ... Excel macro keeps running while the query grid is showing, ... how you would capture the sql. ... > Access database under VBA control, where you could bring up a new ...
    (microsoft.public.excel.programming)
  • Re: Filter Access query results based on selected information
    ... Any thoughts on how to get the results to update and filter based on ... When pulling dta from Access you use a SQL (String query Language). ... VBA queries call this Commandtext. ...
    (microsoft.public.excel.programming)
  • Re: Merging content from access
    ... My hunch at the moment is that this is a problem in my access query. ... I agree with you that Word VBA is a more elegent solution. ... The other field-based technique requires you to have a "parent" table as ... the mail merge data source and a child table inserted using a DATABASE ...
    (microsoft.public.word.docmanagement)
Loading