我可以通过VBA创建传递查询来调用参数化的tSQL UDF并向其发送动态参数以将结果返回给Access吗？-Java 学习之路

我目前有一个SQL 2008 R2数据库后端，其Access 2013 accdb前端具有ODBC无DSN连接和链接表 . 在SQL中，我创建了许多参数化的tSQL UDF，用于将数据提供给报表（目前在我的Access 2010 adp前端中运行良好） . 报告很复杂：多个tSQL UDF运行计算，然后输入到为相应报告提供的最终UDF . 我想将UDF保留在服务器上 - 重写Access访问查询将是一个糟糕的解决方案 . 我的问题是我无法弄清楚如何正确编写VBA以发送传递查询来调用tSQL UDF并为其提供一个参数，该参数将针对每个报告进行更改 . 我知道传递查询是只读的，没关系 . 我已经读过我可以从VBA调用存储过程（SP），但是我可以调用UDF而不是必须将每个转换为SP或创建SP只是为了调用UDF以便我可以从VBA调用SP . 基于我的研究，我想我可能要创建一个SP来调用UDF或将UDF转换为SP以使VBA工作（即返回结果而不会出错） . 它是否正确？

我发现了这个讨论：https://social.msdn.microsoft.com/Forums/office/en-US/898933f5-73f9-44e3-adb9-6aa79ebc948f/calling-a-sql-udf-from-access?forum=accessdev，但是它有相互矛盾的说法“你可以_或者用tsql写的任何其他东西 . ”而且，他们的代码用ADO而不是DOA编写，所以对我来说有点神秘，因为我已经到目前为止只写了DAO，但我得到的一般要点是他们将UDF转换为SP .

我发现这篇文章读得很好，但我的问题再次没有得到明确的“是”：http://technet.microsoft.com/en-us/library/bb188204(v=sql.90).aspx

可以从服务器端删除参数并将其添加到类似于Access 2007 forms with parameterized RecordSource的Access端，但这不会导致Access在过滤之前加载整个数据集，而不是在服务器端进行处理 - 可能导致性能问题是什么？

如果我为其提供常量参数，我可以在Access接口中成功创建传递查询，例如“Select * from udf_FinalReport（2023）”，但我真正需要的是能够传递动态参数 . 例如，参数将来自Forms！Control！[txtboxValue] . 我可以这样做吗？下面的代码就是我正在使用的 - 如果我在第9行中使用SQL中的表名（例如，“SELECT * FROM Table WHERE tblYear =”＆intYear），那么它可以工作，所以我觉得我的所有代码都是正确的，但是当我将我的UDF放在SQL中，如下所示，我收到错误＃3131“FROM子句中的语法错误 . ”（我确实验证了我不应该使用前缀架构（dbo . ） - 这会给错误3024“找不到文件” . ）这个用户错误还是只是告诉我我不能用这种方式调用UDF？

1 Sub AnnualSummary()
2 Dim dbs As DAO.Database
3  Dim qdfPoint As DAO.QueryDef
4  Dim rstPoint As DAO.Recordset
5  Dim intYear As Integer
6  intYear = Reports!Annual_Delineation_Summary!txtYear
7 Set dbs = OpenDatabase("", False, False, "ODBC;DRIVER=sql server;SERVER=******;APP=Microsoft 
8 Office 2010;DATABASE=*******;Network=DBMSSOCN")
9 Set qdfPoint = dbs.CreateQueryDef("", "Select * from udf_AnnualReport(" & intYear& ")")
10 GetPointTemp qdfPoint
11 ExitProcedure:
12  On Error Resume Next
13    Set qdfPoint = Nothing
14    Set dbs = Nothing
15    Set rstPoint = Nothing
16 Exit Sub
17 End Sub
18
19 Function GetPointTemp(qdfPoint As QueryDef)
20  Dim rstPoint As Recordset
21  With qdfPoint
22   Debug.Print .Name
23   Debug.Print " " & .SQL
24   Set rstPoint = .OpenRecordset(dbOpenSnapshot)
25   With rstPoint
26     .MoveLast
27     Debug.Print " Number of records = " & _
28      .RecordCount
29     Debug.Print
30     .Close
31   End With
32  End With
33 End Function

我也尝试使用以下代码而不是第5,6和9行来编写代码 . 当我在select语句中使用表名时，这也有效，但是当我使用UDF名称时出现错误＃3131：

Set qdfPoint = dbs.CreateQueryDef("", "Parameters year int; Select * from Point_Info where 
 year(Sample_Date) = year")
 qdfPoint.Parameters("year").Value = intYear

如果我尝试在tSQL SELECT语句中使用SQL视图的名称，那么这两种代码变体也会起作用 .

2 回答

我不知道这是否是通过传递查询将变量参数传递给函数并将结果返回到Access的最有效方法，因为我仍然是相对较新的Access，但我之前遇到过这个问题 . 尝试类似的问题 . 我通过创建几个在SQL服务器中执行函数并返回结果的传递查询来管理它 . 然后，我创建了一个小型VBA脚本，每次我想要更改它并执行它们时，都会使用新变量重写传递查询 .

Private Sub btn_Click()
EditSQLqtr 9
End Sub


Private Sub EditSQLqtr(qtr As Integer)
CurrentDb.QueryDefs("qryLatestBatchCreatedBy").SQL = "select dbo.latest_batch_created_by(" & qtr & ");"
CurrentDb.QueryDefs("qryLatestBatchFinishedBy").SQL = "select dbo.latest_batch_Finished_by(" & qtr & ");"
End Sub


Private Sub update_dd_sent_Click()

Dim tag As String
Dim ansExp As Integer
Dim rs_crtd As DAO.Recordset
Dim rs_txnd As DAO.Recordset

Set rs_crtd = CurrentDb.OpenRecordset("qryLatestBatchCreatedBy", dbOpenDynaset)
Set rs_txnd = CurrentDb.OpenRecordset("qryLatestBatchTransactionedBy", dbOpenDynaset)

If IsNull(rs_txnd!Expr1000) = False Then
  MsgBox "This file has already been registered by " & rs_txnd!Expr1000 & ".", vbokay, "File already submitted"

ElseIf rs_crtd!Expr1000 = Environ("USERNAME") Then
   ansExp = MsgBox("As you were the user to create the latest file for this period, it is advisable that another team member registers the it as done.", , "File created by " & rs_crtd!Expr1000)
   ansExp = 0
End If

End Sub

我使用OpenRecordset将结果返回，并将其存储为字符串，以便在我的其余代码中使用 .

回复于 2024-04-20T01:35:13+08:00

0
我的共识是使用ADO语言而不是DAO来编写传递查询效果很好 . 但是，我发现执行存储过程仍然可能比尝试调用UDF更好 . 以下是最适合我的代码:(我的ADO Connection使用公共变量strUID和strPWD）
```
Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim strPoint As String

   strPoint = Forms!FRM_Vegetation_Strata!Point_ID

   Set cn = New ADODB.Connection

   cn.Open "Provider = sqloledb;Data Source=imperialis.inhs.illinois.edu;" & _
           "Initial Catalog=WetlandsDB_SQL;User Id=" & strUID & ";Password=" & strPWD

   Set rs = New ADODB.Recordset
   With rs
      Set .ActiveConnection = cn
      .Source = "sp_Report_VegWorksheet '" & strPoint & "'"
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .CursorLocation = adUseClient
      .Open
   End With

   Set Me.Recordset = rs
```
在旁注中，我发现要设置.Recordset来填充子表单，将此代码放在子表单的“Open”事件中 .

然后清理你的连接：
```
Private Sub Form_Unload(Cancel As Integer)  'use "unload", not "close"
   'Close the ADO connection we opened
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
   Set cn = Me.Recordset.ActiveConnection
   cn.Close
   Set cn = Nothing
   Set rs = Nothing
   Set Me.Recordset = Nothing

End Sub
```
此方法不适用于填充报表 . “Set Me.Recordset”仅适用于表单 . 我相信我必须调用存储过程然后填充临时表以用作报告记录集 .

编辑：我发现我可以使用DOA从Access中的VBA调用SQL UDF或SP . 当想要从复杂的SQL函数/过程中提取数据并将其放入Access端临时表时，这尤其有用 . 请参阅Juan Soto的博客https://accessexperts.com/blog/2012/01/10/create-temp-tables-in-access-from-sql-server-tables/#comment-218563此代码将信息放入临时表中，这是我想要填充报告的内容 . 我使用他的代码示例和以下来调用sub：要作为SP执行： CreateLocalSQLTable "testTBL","exec dbo.sp_Report_WetDet_point '1617-1-1A'",False 要调用UDF： CreateLocalSQLTable "testTBL","Select * from dbo.QryReport_Main('1617-2-2A')",False
回复于 2024-04-20T01:35:13+08:00

我可以通过VBA创建传递查询来调用参数化的tSQL UDF并向其发送动态参数以将结果返回给Access吗？

2 回答

相关问题