Databases
StackOverflow2013
Postsdbo
PORun nested Access SQL Query from Excel VBA
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORun nested Access SQL Query from Excel VBA
    primarykey
    data
    text
    <p>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:</p> <pre><code>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;" _ &amp; "Data Source=" &amp; stDB &amp; ";" 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 </code></pre> <p>Now, in the Access DB I already built the query I need, which is quite complicated but working flawlessy:</p> <pre><code>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&lt;C.FuelReferencePrice, C.FuelReferencePrice,1.85&gt;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&lt;C.FuelReferencePrice, C.FuelReferencePrice,1.85&gt;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&amp;LorryOwner] AS O ON C.[ID] = O.[CarrierID]) ON W.ID = O.WeightRangeID) ON T.CarrierID = C.ID WHERE (((W.WeightMin)&lt; T.TaxableWeight) AND ((W.WeightMax)&gt;= 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&lt;C.FuelReferencePrice, C.FuelReferencePrice,1.85&gt;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&lt;C.FuelReferencePrice, C.FuelReferencePrice,1.85&gt;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&amp;LorryOwner] AS O ON C.[ID] = O.[CarrierID]) ON W.ID = O.WeightRangeID) ON T.CarrierID = C.ID WHERE (((W.WeightMin)&lt; T.TaxableWeight) AND ((W.WeightMax)&gt;= T.TaxableWeight) AND ((O.DistrictID)=35)) AND O.RateTypeID=8 ORDER BY TotalCost ASC ) AS Best2Quotations; </code></pre> <p>This gives me the results I want:</p> <p><img src="https://s18.postimg.org/lglwmon7t/query.png" alt="query results"></p> <p>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:</p> <pre><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," &amp; Chr(34) &amp; "#0.00" &amp; Chr(34) &amp; ") as Freight, format((O.Forwarding + C.FixedFee + O.Freight*C.MgmtSurcharge/100 + O.Freight*C.FixedFuelSurcharge/100+(Switch(1.85&lt;C.FuelReferencePrice,C.FuelReferencePrice , 1.85 &gt; C.FuelReferencePrice, 1.85)" &amp; _ "- C.FuelReferencePrice)/1.85*C.IndexedFuelSurcharge*O.Freight)," &amp; Chr(34) &amp; "#0.00" &amp; Chr(34) &amp; ") as AdditionalCosts,format((O.Freight+(O.Forwarding + C.FixedFee + O.Freight*C.MgmtSurcharge/100 + O.Freight*C.FixedFuelSurcharge/100+ (Switch(1.85&lt;C.FuelReferencePrice,C.FuelReferencePrice , 1.85 &gt; C.FuelReferencePrice, 1.85)" &amp; _ "- C.FuelReferencePrice)/1.85*C.IndexedFuelSurcharge*O.Freight))," &amp; Chr(34) &amp; "#0.00" &amp; Chr(34) &amp; ") 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&amp;LorryOwner] AS O ON C.[ID] = O.[CarrierID])" &amp; _ "ON W.ID = O.WeightRangeID) ON T.CarrierID = C.ID WHERE (((W.WeightMin) &lt; T.TaxableWeight) And ((W.WeightMax) &gt;= T.TaxableWeight) And ((O.DistrictID) = 35)) And O.RateTypeID = 4 UNION SELECT C.Name, format(O.Freight*T.TaxableWeight," &amp; Chr(34) &amp; "#0.00" &amp; Chr(34) &amp; ") as Freight, format((O.Forwarding + C.FixedFee + O.Freight*T.TaxableWeight*C.MgmtSurcharge/100 +" &amp; _ "O.Freight*T.TaxableWeight*C.FixedFuelSurcharge/100+ (Switch(1.85&lt;C.FuelReferencePrice,C.FuelReferencePrice , 1.85 &gt; C.FuelReferencePrice, 1.85) - C.FuelReferencePrice)/1.85*C.IndexedFuelSurcharge*O.Freight*T.TaxableWeight)," &amp; Chr(34) &amp; "#0.00" &amp; Chr(34) &amp; ") 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+" &amp; _ "(Switch(1.85&lt;C.FuelReferencePrice,C.FuelReferencePrice, 1.85 &gt; C.FuelReferencePrice, 1.85)- C.FuelReferencePrice)/1.85*C.IndexedFuelSurcharge*O.Freight*T.TaxableWeight)," &amp; Chr(34) &amp; "#0.00" &amp; Chr(34) &amp; ") 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&amp;LorryOwner] AS O ON C.[ID] = O.[CarrierID]) ON W.ID = O.WeightRangeID) ON T.CarrierID = C.ID" &amp; _ "WHERE (((W.WeightMin) &lt; T.TaxableWeight) And ((W.WeightMax) &gt;= 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 </code></pre> <p>I can't get it working, in the moment of actually reading the data, so this line of the previous Read_Recordset VBA function:</p> <pre><code> .Open stSQL1, cnt 'Create the recordset. </code></pre> <p>it is returning me a runtime error which says:</p> <p>"JOIN expression not supported" (or whatever similar, mine is in italian)</p> <p>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,</p> <p>Marco</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USMarcoBi
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PORun nested Access SQL Query from Excel VBA
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSQL 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.
      singulars
      1. PORun nested Access SQL Query from Excel VBA
      1. USTim Williams
    2. COmmmm...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?
      singulars
      1. PORun nested Access SQL Query from Excel VBA
      1. USMarcoBi
    3. COI 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.
      singulars
      1. PORun nested Access SQL Query from Excel VBA
      1. USHK1
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload