Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use MS Query in VBA to only get a SQL statement
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use MS Query in VBA to only get a SQL statement
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use MS Query in VBA to only get a SQL statement
Yes, but I would like to all automate this from Excel in VBA.
So I don't want to start up Access. Could I just use the Access dialog without actually showing Access via OLE automation? RBS "Al" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use MS Query in VBA to only get a SQL statement
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use MS Query in VBA to only get a SQL statement
Not to actually build a query - I know of no way of just calling up the query
wizard or design screen without actually having Access running; but you can (as I mentioned) keep Access hidden from the user up to the point where the user needs to see the dialogs. And all of your code to control this is in your Excel VBA project. With a few tricks it is even possible to keep the main Access screen hidden, if that is a distraction (that is beyond the scope of a quick answer here, but resizing windows and/or the use of Windows API calls for Topmost window are handy tricks!). So: from Excel, you start Access and keep it hidden - users won't even know it is there - until you need it, then you pop it up, get their input, and immediately hide it again. From the user's end it would appear the same as if only the query builder popped up. "RB Smissaert" wrote: Yes, but I would like to all automate this from Excel in VBA. So I don't want to start up Access. Could I just use the Access dialog without actually showing Access via OLE automation? RBS "Al" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use MS Query in VBA to only get a SQL statement
FYI: This is what I tried that didn't work
Sub ShowQueryUI() Dim acApp As Access.Application Set acApp = New Access.Application acApp.OpenCurrentDatabase "C:\Dick\Temp.mdb" acApp.Visible = True acApp.DoCmd.SelectObject acTable, "TblTest", True acApp.DoCmd.RunCommand acCmdNewObjectQuery MsgBox "Done" End Sub It brings up that dialog "Design View, Simple Query Wizard, etc.." which would be nice to eliminate, but I didn't see an option for that. And the Excel macro keeps running while the query grid is showing, so I'm not sure how you would capture the sql. If you could force the query to be saved as a certain name, you could use QueryDef to get to it, but with the Excel macro completed, I don't see how you could force anything on the save. I'll be interested to know if anyone has other ideas. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com K Dales wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Statement Query | Excel Worksheet Functions | |||
If statement query | Excel Worksheet Functions | |||
IF statement query | Excel Discussion (Misc queries) | |||
IF statement query | Excel Worksheet Functions | |||
Run Query statement in Excel | Excel Programming |