running Access SQL from VBA

skorpionkz

Well-known Member
Joined
Oct 1, 2013
Messages
1,168
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?

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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
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
 

skorpionkz

Well-known Member
Joined
Oct 1, 2013
Messages
1,168
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

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

skorpionkz

Well-known Member
Joined
Oct 1, 2013
Messages
1,168
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.
Code:
Public RecordSet             As ADODB.RecordSet
Public Connection           As ADODB.Connection

Code:
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
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Why are you executing a SELECT statement?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top