Queries (1 Viewer)

Hi,

In MS Access,
I create many queries to do calculation of the data, and also the queries contents have been changed depend on the user what criteria values input on the form. And then export the final results to the Excel worksheets.

In VB2005, can we do that?

For example:
qry1: retrieve data from table 1 where [Name] = "xxxx"
qry2: retrieve data from table 2 where [Month] = "July-07"
qry3: qry1 Left Join qry2
qry4: Sum(qry3.fieldname) Group By qry3.fieldnames

qry5: retrieve data from table 3 where [Student] = "xxxx"
qry6: Sum(qry5.fieldname) Group By qry5.fieldnames
.....

qry10: do crosstab query.
Export qry10 results to Excel worksheets.

In MS Access, I can do it.
Can we create many temporary queries and setup the temporary name of each query in VB2005?

I'm not sure about VB2005 but in VB 6, which is what I program in, I would add a Reference to the MS DAO 3.6 Object Library. Look for a "Refernces" menu option.

Once the library is part of the project you have access to all of the database objects that exist in Access. You can create record sets, query defs, .Execute SQL Deletes & Appends.

This is from a VB6 app I just finished.

Dim dbsYieldData As Database
Dim rstItemData As Recordset

Set dbsYieldData = OpenDatabase(sDBPath)

Set rstItemData = dbsYieldData.OpenRecordset("SELECT * FROM Cutters", dbOpenDynaset, dbOptimistic)

Greg