[RESOLVED] Using an Access Query from VB6 ADO

[sparbag]

Hello all,

I have built in Acces a query that uses 5 tables to return, specifically 1 - The total expenditure in materials of a job and 2- knowledge of whether or not the Client is to charge TAX against labour.

The query performs perfectly
I had attempted for the whole day to build the query in VB6 and failed miserably. Building it in Access took me ten minutes.

Now that I have this query, how can I use it from my VB6 application?
I would like to fill a recordset with the results of the query so that I may run calculations against the expected tax deductions for an accurate yearly returns. Please be aware that this is an application to complete my University project, I am not about to base an organisations tax returns from an Access app.

All I have found so far is to use parameterised command objects, which I have never used before.

Any assistance is greatly appreciated.

Kind regards

Steve

[GaryMazzone]

If you want to keep everything in VB. Open the query in design mode.
Shift to SQL View.
Copy the results and bring it to the VB code and paste. You will need to do some clean up (puting it into string)

[sparbag]

Dear Gary,
Thank you very much. From my last two posts I think it is clear that I can no longer see the woods for the trees.

Here's the query string just for lunacy's sake:

        Dim strQuery As String
        strQuery = "SELECT tblClient.ClientName, tblWork.Invoice, tblWork.Payment, " _
        & "tblWork.Paid, First(tblMaterials.PurchasedItem) AS [First Of PurchasedItem], " _
        & "Sum(tblMaterials.PurchaseCost) AS [Sum Of PurchaseCost], " _
        & "Count(*) AS [Count Of tblMaterials], tblPurchaseType.Type, tblClient.CIS " _
        & "FROM tblPurchaseType INNER JOIN ((tblClient INNER JOIN tblWork ON tblClient.ClientID=tblWork.ClientID) " _
        & "INNER JOIN tblMaterials ON tblWork.WorkID=tblMaterials.WorkID) " _
        & "ON tblPurchaseType.PurchaseTypeID=tblMaterials.PurchaseType " _
        & "GROUP BY tblClient.ClientName, tblWork.Invoice, tblWork.Payment, tblWork.Paid, " _
        & "tblPurchaseType.Type, tblClient.CIS, tblPurchaseType.Type, tblClient.CIS " _
        & "HAVING (((tblPurchaseType.Type)='Customer') AND ((tblClient.CIS)=True)); "

Kind regards

Steve

[GaryMazzone]

I would do a slight change. Not using a Having clause but a Where

Dim strQuery As String
        strQuery = "SELECT tblClient.ClientName, tblWork.Invoice, tblWork.Payment, " _
        & "tblWork.Paid, First(tblMaterials.PurchasedItem) AS [First Of PurchasedItem], " _
        & "Sum(tblMaterials.PurchaseCost) AS [Sum Of PurchaseCost], " _
        & "Count(*) AS [Count Of tblMaterials], tblPurchaseType.Type, tblClient.CIS " _
        & "FROM tblPurchaseType INNER JOIN ((tblClient INNER JOIN tblWork ON tblClient.ClientID=tblWork.ClientID) " _
        & "INNER JOIN tblMaterials ON tblWork.WorkID=tblMaterials.WorkID) " _
        & "ON tblPurchaseType.PurchaseTypeID=tblMaterials.PurchaseType " _
        & " WHERE tblPurchaseType.Type ='Customer' AND tblClient.CIS=True "
        & "GROUP BY tblClient.ClientName, tblWork.Invoice, tblWork.Payment, tblWork.Paid, " _
        & "tblPurchaseType.Type, tblClient.CIS, tblPurchaseType.Type, tblClient.CIS;"

Just more to the SQL standard

[brucevde]

Now that I have this query, how can I use it from my VB6 application?

You can simply execute the query and load a recordset.

Using the sample Northwind database, the following VB code executes the Employee Sales By Country query.

Private Sub Command1_Click()
    Dim rs As ADODB.Recordset
    Dim cmd As ADODB.Command
    Dim fld As ADODB.Field
    
    Dim dteStart As Date, dteEnd As Date
    dteStart = "01-Jan-1995"
    dteEnd = "31-Dec-1995"
    
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = "provider=microsoft.jet.oledb.4.0;data source=m:\testing\nwind2002.mdb"
        .CommandText = "[Employee Sales By Country]"
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter(, adDBDate, adParamInput, , dteStart)
        .Parameters.Append .CreateParameter(, adDBDate, adParamInput, , dteEnd)
        
        Set rs = .Execute
    End With
    
    Do Until rs.EOF
        For Each fld In rs.Fields
            Debug.Print fld.Name; " = "; fld.Value,
        Next
        Debug.Print
        rs.MoveNext
    Loop
    
    rs.Close
    
End Sub

[sparbag]

Hello both,

I apologise for not posting back immediately. I was in the process of converting the returned recordset values to variables, performing the calculations then writing the desired single value back to the database.
Now that I know that function performs correctly I am going to address the requirements that you both have offered. Firstly I shall change the HAVING to a WHERE and retest the code.
Secondly, as an educational advantage I shall apply the command object method to the recordset. This is the method I would prefer at this moment in time due directly to being advantageous towards the marking of the project.

Unfortunately, due to the time here in Cardiff being 22:41 hrs I'm off to relax before going to bed. I shall be back working on this tomorrow, and if you're both in aggreement, I shall continue posting in this thread.

Kind regards

Steve