running Access SQL from VBA

[skorpionkz]

Hi all,

I have query in Access that prepares report for me. I also have some tool build in excel that running various reports from Access and some calculation on top of it.
Therefore I would like not only extract report but be able to modify it a little. Mainly change value for WHERE.
Bellow is query I have in Access I can manually change Key_table.STATUS and/or REF_UCDB_CAPEX.[UCDB version] and it works fine.
The issue is that it is not me who running those reports and they need to have ability to change status and VBA in excel have variable for UCDB version.

I tough I can just copy this sql to excel and then just use variables like that (Key_table.STATUS) = " & Chr(34) & MYSTATUS & Chr(34), but VBA doesn't like it.

I am then reading result of this report into recordset and loop through it to populate class objects in VBA, which triggers some calculation.
Any advice on how can I provide those variable to sql code?

SELECT 
    fct_CostCurves.[Maximo Code], 
    fct_CostCurves.[Updated by], 
    fct_CostCurves.[Updated on], 
    fct_IPDetails.[Asset Reference], 
    fct_CostCurves.LookUp_Key, 
    fct_IPDetails.[IP TITLE], 
    fct_IPDetails.Domain, 
    fct_CostCurves.[Cost Curve Reference], 
    ref_UCDB_CAPEX.Name AS [Cost Curve Name], 
    fct_CostCurves.[Action Type], 
    ref_UCDB_CAPEX.[Yardstick x] AS [Primary Yardstick], 
    ref_UCDB_CAPEX.[Yardstick y] AS [Secondary Yardstick], 
    fct_CostCurves.[Existing Primary Yard Stick Qty], 
    fct_CostCurves.[Existing Secondary Yard Stick Qty], 
    fct_CostCurves.[Existing Unit Quantity], 
    fct_CostCurves.[Proposed Primary Yard Stick Qty], 
    fct_CostCurves.[Proposed Secondary Yard Stick Qty], 
    fct_CostCurves.[Proposed Unit Quantity], 
    fct_CostCurves.[Total CAPEX], 
    fct_CostCurves.Carbon, 
    fct_CostCurves.Power, 
    fct_CostCurves.Labour, 
    fct_CostCurves.Chemical, 
    fct_CostCurves.[Materials and Consumables], 
    fct_CostCurves.[Incremental OPEX]
    
FROM 
    (fct_IPDetails INNER JOIN 
        (fct_CostCurves INNER JOIN Key_table ON fct_CostCurves.LookUp_Key = Key_table.LookUp_Key)
        ON (fct_IPDetails.LookUp_Key = Key_table.[Project Code]) 
        AND (fct_IPDetails.[Intervention] = Key_table.[Intervention])) 
        INNER JOIN ref_UCDB_CAPEX ON fct_CostCurves.[Cost Curve Reference] = ref_UCDB_CAPEX.[Cost Model ID/Ref]

WHERE (((Key_table.STATUS) = "IS0011") And ((fct_CostCurves.Selected) = True) And ((fct_IPDetails.Selected) = True) And ((REF_UCDB_CAPEX.[UCDB version]) = "5.03"))

ORDER BY fct_CostCurves.LookUp_Key;

Thank you for help

 

[DataBlake]

Make sure you have the reference for "Microsoft ActiveX Data Objects Library" created
To do this go to the VBA editor and go tools>reference and check the box.
That may be why you are getting a error for class objects.

As far as the actual code goes perhaps this article can provide you insight

How to use Excel VBA to Run SQL Query - Querychat

Learn how to write Excel VBA code to connect to SQL Server database, run the SQL SELECT statement, and display the query result into Worksheet.

www.got-it.ai

 

[skorpionkz]

Thank you. The reference is ticked. I have other 30 tables that the same VBA is reading and it works fine.
But other parts of VBA reads data from each table separately while above sql is copy of query that joins the tables...

 

[DataBlake]

But other parts of VBA reads data from each table separately while above sql is copy of query that joins the tables...

It's hard for me to identify a problem with your VBA+SQL code without seeing the actual VBA side.
Would you be able to supply the rest of the code?

as far as i know you still have to define those variables inside of VBA
example being if you want to create a query that joins data together for a table in excel you will need to define things with Dim like QueryDef object, define and close a connection, define a recordset, etc
the article i supplied gives a good explanation of how to select data, display the data on a worksheet
VBA will not take SQL at face value you kind of have to code around it

but again i don't even know where your code is hanging up just what you have pasted in the code box which doesn't make sense to me considering none of the variables have been defined.
heres another article example that deals directly with access connections

Access database .accdb operations via Excel VBA | My Excel Database

exceldb.net

 

[Norie]

Can you post the VBA code you are trying to run that query with?

 

[skorpionkz]

Hi all,

here is the code I am using in vba:
Code is not fully finished. I would need to run code to loop through recordset and populate report class before printing it, but i cannot execute sql.

PBL is public variables class. plus there is function to establish connection. which works on 30+ access tables.

Public RecordSet             As ADODB.RecordSet
Public Connection           As ADODB.Connection

Public Sub LoadCostCurveReportQuery(ByRef myReport As cls_REPORT_PRINT)

    Dim sSQL                    As String
    Dim str_Status              As String
    Dim str_Version             As String

    PBL.ConnectTo_IPSDB
    str_Status = "IS0011"
    str_Version = "5.03"

    sSQL = "SELECT fct_CostCurves.[Maximo Code], fct_CostCurves.[Updated by], fct_CostCurves.[Updated on], fct_IPDetails.[Asset Reference], fct_CostCurves.LookUp_Key, fct_IPDetails.[IP TITLE], fct_IPDetails.Domain, fct_CostCurves.[Cost Curve Reference], ref_UCDB_CAPEX.Name AS [Cost Curve Name], fct_CostCurves.[Action Type], ref_UCDB_CAPEX.[Yardstick x] AS [Primary Yardstick], ref_UCDB_CAPEX.[Yardstick y] AS [Secondary Yardstick], fct_CostCurves.[Existing Primary Yard Stick Qty], fct_CostCurves.[Existing Secondary Yard Stick Qty], fct_CostCurves.[Existing Unit Quantity], fct_CostCurves.[Proposed Primary Yard Stick Qty], fct_CostCurves.[Proposed Secondary Yard Stick Qty], fct_CostCurves.[Proposed Unit Quantity], fct_CostCurves.[Total CAPEX], fct_CostCurves.Carbon, fct_CostCurves.Power, fct_CostCurves.Labour, fct_CostCurves.Chemical, fct_CostCurves.[Materials and Consumables], fct_CostCurves.[Incremental OPEX]" & _
       " FROM (fct_IPDetails INNER JOIN (fct_CostCurves INNER JOIN Key_table ON fct_CostCurves.LookUp_Key = Key_table.LookUp_Key) ON (fct_IPDetails.LookUp_Key = Key_table.[Project Code]) AND (fct_IPDetails.[Intervention] = Key_table.[Intervention])) INNER JOIN ref_UCDB_CAPEX ON fct_CostCurves.[Cost Curve Reference] = ref_UCDB_CAPEX.[Cost Model ID/Ref]" & _
       " WHERE (((Key_table.STATUS) = '" & str_Status & "') And ((fct_CostCurves.Selected) = True) And ((fct_IPDetails.Selected) = True) And ((REF_UCDB_CAPEX.[UCDB version]) = '" & str_Version & "'))" & _
       " ORDER BY fct_CostCurves.LookUp_Key;"



    Set PBL.RecordSet = PBL.Connection.Execute(sSQL)

    On Error Resume Next
    PBL.RecordSet.MoveFirst
    On Error GoTo 0

    If PBL.RecordSet.EOF = False Then
        myReport.RPRINT
    End If

    PBL.Connection.Close

    Set PBL.RecordSet = Nothing
    Set PBL.Connection = Nothing


End Sub

 

[Norie]

Why are you executing a SELECT statement?

 

[skorpionkz]

Why are you executing a SELECT statement?

Unfortunately I know VBA but not SQL. If you could point out what should I have there instead?