从 Excel VBA 运行嵌套的 Access SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18537462/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Run nested Access SQL Query from Excel VBA
提问by MarcoBi
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:
很抱歉标题没有像它应该的那样具体，但英语不是我的第一语言，我无法解释得更好。这不是关于如何从 Excel VBA 在 Access 数据库上运行查询的问题，我知道如何做到这一点。我请求帮助是因为我在 Access 中内置了一个工作 SQL 查询以进行测试，我需要从启用宏的 Excel 电子表格中启动它。比赛：我正在构建一个由 Access 数据库（它是“被动的”，它只存储数据）和一些与之交互的 Excel 电子表格组成的工具。我需要这样做，因为用户将不得不使用它，所以我无法改变这一点。我有一些函数可以让我与预构建我需要的字符串的数据库进行通信。在这种情况下，我想读取查询的记录集结果。实现此目的的 VBA 函数如下：
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:
现在，在 Access DB 中，我已经构建了我需要的查询，它非常复杂但工作完美：
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:
现在我的问题。我需要从 Excel 电子表格启动此查询，因为它不会像我在 Access 中编写的那样是静态的以进行测试：一些值是从工作表本身中提取的。然而，我什至无法运行静态的。我正在尝试使用此代码：
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:
在实际读取数据的那一刻，我无法让它工作，所以前面的 Read_Recordset VBA 函数的这一行：
.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)
“不支持 JOIN 表达式”（或类似的，我的是意大利语）
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,
我很挣扎，在花了很多时间在 Access 中构建查询之后，我无法忍受无法从 Excel 启动它的想法。任何建议或替代解决方案？任何事情都将不胜感激。问候，
Marco
马可
回答by TehJake
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.)
为了将来参考，可以从 ADO 激活 Access 中内置的查询，如下所示（注意：如果您的查询实际上有参数，则只需要附加参数。）
'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:
这将适用于访问制定数据库中的更改的操作查询，但如果您需要返回记录集，则只需像往常一样打开记录集，但使用命令对象而不是 sql 字符串打开它：
rs.Open(cmd)
Hope this helps :)
希望这可以帮助 ：）
