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.....
LVL 4
jazjefAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
I can't envision how this would work.  You could automate Access and get it to run a make-table query and then load the data from that table into your grid but that would not be updateable.  To open a recordset and process it requires using the .OpenRecordset method.  You would have to somehow intercept that code and pass the data to your grid.

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.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<
It's not practical to try to write such a huge query again in VB6 code,
>>

It really is for a number of reasons, one of which is you'll force a user into having a copy of Access installed.

You will be far better off performance and usage wise of reproducing the query in VB6

Jim.
Gustav BrockCIOCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

jazjefAuthor Commented:
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
PatHartmanCommented:
You have to break the string into several pieces in order to insert variables where they are needed and then concatenate them.

strSelect1 = "...."

strWhere1 = " WHERE (((p.yearID)= " & YourYearVariable & ") AND ((p.teamID)= " & " YourTeamVariable & ") AND ((p.IPouts)>99) AND ((f.POS)='P'));"

strSelect2 = "..."
strWhere2 = "..."

strFinal = strSelect1 & strWhere1 & strSelect2 & strWhere2

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jazjefAuthor Commented:
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.