Run nested Access SQL Query from Excel VBA

Question

sorry for the title not being as specific as it should be, but english is not my first language and I couldn't explain better. It is NOT a question about how running queries on an Access database from Excel VBA, I know how to do that. I requesting help because I have a working SQL query built into Access for testing which I need to launch from within a macro enabled Excel spreadsheet. The contest: I'm building a tool composed by an Access database (which is "passive", it stores only data) and some Excel spreadsheets interacting with it. I need to it this way because of the users who will have to use it, so I can't change this. I have functions which let me communicate with the DB prebuilding the strings I need. In this case I want to read a recordset result of the query. The VBA function to make this is the following:

Public Function Read_Recordset(ByVal stSQL1 As String) As ADODB.Recordset
Dim cnt As ADODB.Connection
Dim stDB As String
Dim stConn As String
Dim wbBook As Workbook
Dim wsSheet1 As Worksheet

 'Instantiate the ADO-objects.
Set cnt = New ADODB.Connection
Set Read_Recordset = New ADODB.Recordset

 'Path to the database.
stDB = Foglio1.Cells(1, 2)

 'Create the connectionstring.
stConn = "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & stDB & ";"

With cnt
    .Open (stConn) 'Open the connection.
    .CursorLocation = adUseClient 'Necessary to disconnect the recordset.
End With
Debug.Print stSQL1

With Read_Recordset
    .Open stSQL1, cnt 'Create the recordset.
    Set .ActiveConnection = Nothing 'Disconnect the recordset.
End With

'Release objects from the memory.
cnt.Close
Set cnt = Nothing
End Function

Now, in the Access DB I already built the query I need, which is quite complicated but working flawlessy:

SELECT TOP 2 *
FROM (
SELECT C.Name, format(O.Freight,"#0.00") as Freight, format((O.Forwarding+
C.FixedFee + O.Freight*C.MgmtSurcharge/100 + O.Freight*C.FixedFuelSurcharge/100+(
Switch
(
1.85<C.FuelReferencePrice,
C.FuelReferencePrice,1.85>C.FuelReferencePrice,1.85
)
- C.FuelReferencePrice)/1.85*C.IndexedFuelSurcharge*O.Freight),"#0.00") as
AdditionalCosts,format((O.Freight+(O.Forwarding + C.FixedFee +
O.Freight*C.MgmtSurcharge/100 + O.Freight*C.FixedFuelSurcharge/100+(
Switch
(
1.85<C.FuelReferencePrice,
C.FuelReferencePrice,1.85>C.FuelReferencePrice,1.85
)
- C.FuelReferencePrice)/1.85*C.IndexedFuelSurcharge*O.Freight)),"#0.00") as TotalCost
FROM Temp_TaxableWeights AS T INNER JOIN (Weight_Ranges AS W INNER JOIN (Carriers AS C
INNER JOIN [OBPT_Groupage&LorryOwner] AS O ON C.[ID] = O.[CarrierID]) ON W.ID =
O.WeightRangeID) ON T.CarrierID = C.ID
WHERE (((W.WeightMin)< T.TaxableWeight) AND ((W.WeightMax)>= T.TaxableWeight) AND
((O.DistrictID)=35)) AND O.RateTypeID=4
UNION SELECT C.Name, format(O.Freight*T.TaxableWeight,"#0.00") as Freight,
format((O.Forwarding + C.FixedFee + O.Freight*T.TaxableWeight*C.MgmtSurcharge/100 +
O.Freight*T.TaxableWeight*C.FixedFuelSurcharge/100+(
Switch
(
1.85<C.FuelReferencePrice,
C.FuelReferencePrice,1.85>C.FuelReferencePrice,1.85
)
-C.FuelReferencePrice)/1.85*C.IndexedFuelSurcharge*O.Freight*T.TaxableWeight),"#0.00")
as AdditionalCosts,format((O.Freight*T.TaxableWeight +O.Forwarding + C.FixedFee +
O.Freight*T.TaxableWeight*C.MgmtSurcharge/100 +
O.Freight*T.TaxableWeight*C.FixedFuelSurcharge/100+(
Switch
(
1.85<C.FuelReferencePrice,
C.FuelReferencePrice,1.85>C.FuelReferencePrice,1.85
)
-C.FuelReferencePrice)/1.85*C.IndexedFuelSurcharge*O.Freight*T.TaxableWeight),"#0.00")
as TotalCost
FROM Temp_TaxableWeights AS T INNER JOIN (Weight_Ranges AS W INNER JOIN (Carriers AS C
INNER JOIN [OBPT_Groupage&LorryOwner] AS O ON C.[ID] = O.[CarrierID]) ON W.ID =
O.WeightRangeID) ON T.CarrierID = C.ID
WHERE (((W.WeightMin)< T.TaxableWeight) AND ((W.WeightMax)>= T.TaxableWeight) AND
((O.DistrictID)=35)) AND O.RateTypeID=8
ORDER BY TotalCost ASC
)  AS Best2Quotations;

This gives me the results I want:

Now my problem. I need to launch this query from an Excel spreadsheet, because it won't be static as I wrote it in Access to test: some of the values are picked up from the sheet itself. Howevere, I'm not able to run even the static one. I'm trying with this code:

Public Sub btnCalcQuotations_Click()
Dim stSQL As String
Dim rstTemp As ADODB.Recordset
Dim RealWeight As Double, Volume As Double

stSQL = "SELECT TOP 2 * FROM (SELECT C.Name, format(O.Freight," & Chr(34) & "#0.00" & Chr(34) & ") as Freight, format((O.Forwarding + C.FixedFee + O.Freight*C.MgmtSurcharge/100 + O.Freight*C.FixedFuelSurcharge/100+(Switch(1.85<C.FuelReferencePrice,C.FuelReferencePrice , 1.85 > C.FuelReferencePrice, 1.85)" & _
            "- C.FuelReferencePrice)/1.85*C.IndexedFuelSurcharge*O.Freight)," & Chr(34) & "#0.00" & Chr(34) & ") as AdditionalCosts,format((O.Freight+(O.Forwarding + C.FixedFee + O.Freight*C.MgmtSurcharge/100 + O.Freight*C.FixedFuelSurcharge/100+ (Switch(1.85<C.FuelReferencePrice,C.FuelReferencePrice , 1.85 > C.FuelReferencePrice, 1.85)" & _
            "- C.FuelReferencePrice)/1.85*C.IndexedFuelSurcharge*O.Freight))," & Chr(34) & "#0.00" & Chr(34) & ") as TotalCost,W.WeightMin, W.WeightMax, C.FuelReferencePrice,C.IndexedFuelSurcharge FROM Temp_TaxableWeights AS T INNER JOIN (Weight_Ranges AS W INNER JOIN (Carriers AS C INNER JOIN [OBPT_Groupage&LorryOwner] AS O ON C.[ID] = O.[CarrierID])" & _
            "ON W.ID = O.WeightRangeID) ON T.CarrierID = C.ID WHERE (((W.WeightMin) < T.TaxableWeight) And ((W.WeightMax) >= T.TaxableWeight) And ((O.DistrictID) = 35)) And O.RateTypeID = 4 UNION SELECT C.Name, format(O.Freight*T.TaxableWeight," & Chr(34) & "#0.00" & Chr(34) & ") as Freight, format((O.Forwarding + C.FixedFee + O.Freight*T.TaxableWeight*C.MgmtSurcharge/100 +" & _
            "O.Freight*T.TaxableWeight*C.FixedFuelSurcharge/100+ (Switch(1.85<C.FuelReferencePrice,C.FuelReferencePrice , 1.85 > C.FuelReferencePrice, 1.85) - C.FuelReferencePrice)/1.85*C.IndexedFuelSurcharge*O.Freight*T.TaxableWeight)," & Chr(34) & "#0.00" & Chr(34) & ") as AdditionalCosts,format((O.Freight*T.TaxableWeight +O.Forwarding + C.FixedFee + O.Freight*T.TaxableWeight*C.MgmtSurcharge/100 + O.Freight*T.TaxableWeight*C.FixedFuelSurcharge/100+" & _
            "(Switch(1.85<C.FuelReferencePrice,C.FuelReferencePrice, 1.85 > C.FuelReferencePrice, 1.85)- C.FuelReferencePrice)/1.85*C.IndexedFuelSurcharge*O.Freight*T.TaxableWeight)," & Chr(34) & "#0.00" & Chr(34) & ") as TotalCost,W.WeightMin, W.WeightMax, C.FuelReferencePrice, C.IndexedFuelSurcharge FROM Temp_TaxableWeights AS T INNER JOIN (Weight_Ranges AS W INNER JOIN (Carriers AS C INNER JOIN [OBPT_Groupage&LorryOwner] AS O ON C.[ID] = O.[CarrierID]) ON W.ID = O.WeightRangeID) ON T.CarrierID = C.ID" & _
            "WHERE (((W.WeightMin) < T.TaxableWeight) And ((W.WeightMax) >= T.TaxableWeight) And ((O.DistrictID) = 35)) And O.RateTypeID = 8 ORDER BY TotalCost ASC)"
Set rstTemp = Read_Recordset(stSQL)
With rstTemp
    If Not .EOF Then
        r = Application.WorksheetFunction.Match("Trasportatore", Columns(24), 0) + 2
        .MoveFirst
        While Not .EOF
            Cells(r, 24) = !Name
            Cells(r, 25) = !Freight
            Cells(r, 26) = !AdditionalCost
            Cells(r, 27) = !TotalCost
            .MoveNext
        Wend
    End If
End With
End Sub

I can't get it working, in the moment of actually reading the data, so this line of the previous Read_Recordset VBA function:

 .Open stSQL1, cnt 'Create the recordset.

it is returning me a runtime error which says:

"JOIN expression not supported" (or whatever similar, mine is in italian)

I'm quite struggling right, after a lot of time spent in buiding the query in Access I couldn't stand the idea of not being able to launch it from Excel. Any suggestions or alternative solution? Anything would be greatly appreciated. Regards,

Marco

SQL which uses Access-specific functions will not run via a "vanilla" query mechanism such as Excel uses to read your Access database. I suspect that's your problem. — Tim Williams
– Tim Williams, Commented Aug 30, 2013 at 16:54
mmmm...thx for the answer. But what is the access-specific function you're talking about? maybe the switch? Can I replace it with with some std SQL code? — marcob8986
– marcob8986, Commented Aug 31, 2013 at 0:35
I don't think ADO can use Access functions so I doubt that's the problem. I think I would probably tackle this problem by trying to simplify the query. A query that complicated would probably keep me from sleeping at night. — HK1
– HK1, Commented Aug 31, 2013 at 14:21
Suppose there's no way to simplify the query. I built the database looking forward to make this kind of query cuz it was the only way to do it that came at my mind. The "switch" function is Access specific? May I substitute it with a select case? — marcob8986
– marcob8986, Commented Sep 1, 2013 at 12:20
I got it working!! I tried out a simpler query but with an identical structure (so the UNION of 2 different query with the SWITCH function in each one) and was working so I sorted out the problem in the bigger query: a missing " " (space) character, LOL! Anyway thx! — marcob8986
– marcob8986, Commented Sep 2, 2013 at 7:12

TehJake · Accepted Answer · 2014-01-16 14:48:43Z

For future reference, queries built in Access can be activated from ADO as follows (Note: Only need to append parameters if your query actually has them.)

    'Create Variables
    dim cmd as new adodb.command, cn as new adodb.connection

    'Establish db connection
    cn.connectionstring = "Data Source=MyDataSource.accdb;Provider=Microsoft.ACE.OLEDB.12.0;"
    cn.Open

    'Create and assign parameter values
    Set parStartDate = .CreateParameter("Enter Start Date", adDate, adParamInput, 10)
    Set parEndDate = .CreateParameter("Enter End Date", adVarChar, adParamInput, 10)
        parStartDate.Value = sStartDate
        parEndDate.Value = sEndDate

    'Set up command attribute, assign param objects to command object 
    'so that these are passed through as well

    With cmd
    .CommandType = adCmdStoredProc
    .Parameters.Append parStartDate
    .Parameters.Append parEndDate
    Set .ActiveConnection = cn
    .CommandText = "MyQueryName"
    .Execute
    End With

This will work for an action query in access to enact changes in your DB, but if you need to return a recordset, then just open a recordset as you would normally, but open it with the command object as opposed to an sql string:

rs.Open(cmd)

Hope this helps :)

Collectives™ on Stack Overflow

Run nested Access SQL Query from Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related