jazjef
asked on
How do I launch a query stored in MS Access from VB6?
I have a HUGE query in MS Access that I want to launch from VB6 by passing 2 parameters from two VB6 combo-boxes to the MS Access db---which will then load the results of the query into a VB6 MSHFlexGrid control.
It's not practical to try to write such a huge query again in VB6 code, so that's why I'm pursing the pass-the-parameters solution. I've seen some EE posts on this, but I can't get a clear grasp of them---I guess I need someone to talk me through it.
Anyone know how to do this?
Many thanks for any help.....
It's not practical to try to write such a huge query again in VB6 code, so that's why I'm pursing the pass-the-parameters solution. I've seen some EE posts on this, but I can't get a clear grasp of them---I guess I need someone to talk me through it.
Anyone know how to do this?
Many thanks for any help.....
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can't you use DAO to retrieve the querydef object?
Then you can pull the SQL from this and run it in VB:
strSQL = YourQuery.SQL
' Modify SQL with your parameters.
' Run the SQL.
/gustav
Then you can pull the SQL from this and run it in VB:
strSQL = YourQuery.SQL
' Modify SQL with your parameters.
' Run the SQL.
/gustav
ASKER
OK.... so, here's the query [text file attached]. It pulls data from the Lahman database of MLB statistics... I don't know how to convert this into a statement in VB6, but as per the suggestion of Jim Dettman, that would be the way to go. The 2014 Lahman database can be downloaded in MS Access at the Lahman Database Website
I have two combo boxes in VB6 that specify the two parameters. Ideally, one would select those parameters, click a button, and the query would populate the MSHFlexGrid with the requested records from the Lahman Database. The query will return any MLB team for any year in a specific format---just paste it into SQL view in MS Access. About a year or so ago some people here on EE helped me complete it, and now I'm wanting to move forward with the way it's used.
Note: my project is in no way a commercial use of the Lahman Database----it's 'fair use' of the Lahman Database along with an application I offer for free that will connect to the Lahman Database. Users of my free application would have to download the Lahman Database themselves to use with my free application as I have no permission to distribute the database with my application.
Lahman-database-query.txt
I have two combo boxes in VB6 that specify the two parameters. Ideally, one would select those parameters, click a button, and the query would populate the MSHFlexGrid with the requested records from the Lahman Database. The query will return any MLB team for any year in a specific format---just paste it into SQL view in MS Access. About a year or so ago some people here on EE helped me complete it, and now I'm wanting to move forward with the way it's used.
Note: my project is in no way a commercial use of the Lahman Database----it's 'fair use' of the Lahman Database along with an application I offer for free that will connect to the Lahman Database. Users of my free application would have to download the Lahman Database themselves to use with my free application as I have no permission to distribute the database with my application.
Lahman-database-query.txt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK; I'm splitting the points. without Jim's comment, I would not have embarked on trying to convert it; without Pat's comment, i wouldn't have applied a good strategy to solve it.
I've made HUGE progress on this SQL statement in converting it to VB6 code. In fact, I have all the Pitching data portion of the statement done---which means I'm going to be able to complete all of it. The Batting data uses all the same INNER JOIN etc syntax that the Pitching data part of the statement does; and the UNION of the two statements should not be a problem.
*Thanks to JimDettman; I figured "he knows what he's talking about, or he wouldn't be on EE commenting on VB6 stuff" .... your comment made me decide that solving the problem in VB6 would indeed be a gigantic step forward for my application and for my personal knowledge base.
*Thanks to PatHartman; I read Pat's comment and decided to use the strategy where I completely break that giant statement down into chunks, and just put the whole thing together one small piece at a time. That turned out to be a great strategy.
So, Pat gets me pointed in the right direction, and Jim gives me the push I needed to start heading in that direction. Thanks a ton!
I've made HUGE progress on this SQL statement in converting it to VB6 code. In fact, I have all the Pitching data portion of the statement done---which means I'm going to be able to complete all of it. The Batting data uses all the same INNER JOIN etc syntax that the Pitching data part of the statement does; and the UNION of the two statements should not be a problem.
*Thanks to JimDettman; I figured "he knows what he's talking about, or he wouldn't be on EE commenting on VB6 stuff" .... your comment made me decide that solving the problem in VB6 would indeed be a gigantic step forward for my application and for my personal knowledge base.
*Thanks to PatHartman; I read Pat's comment and decided to use the strategy where I completely break that giant statement down into chunks, and just put the whole thing together one small piece at a time. That turned out to be a great strategy.
So, Pat gets me pointed in the right direction, and Jim gives me the push I needed to start heading in that direction. Thanks a ton!
It is simply just easier to open the querydef and copy the SQL string. All you have to do is modify the part where it gets its parameters.