sql - 如何使用 VBA 从 MS Access 2007-2016 中的大型全局 CORP SQL Server 13.xxx 中的存储过程返回值

Question

我在 Visual Studio 中用 C# 创建了一个 DLL，它可以从我们公司的生产 SQL Server 返回良好的产品输出。

我有一个 MS Access 数据库，我有 ADMIN 权限，我在前端有 VBA 编码了一个 DLL 对象调用，此参数请参见报告阅读Biop_ProductionCountersInUTC器数据库中名为POINTDBReport.

我猜它是用于只读目的的真实数据库的克隆数据库。

这些是Biop_ProductionCountersInUTC存储过程中的参数设置：

• @Equipment(varchar(255), input, No default) aka '62' 在下面的查询中
• @Counter(varchar(255), input, No default) aka ''OutputGood' 在下面的查询中
• @Start_time（日期时间，输入，无默认值）在下面的查询中又名@FROM
• @End_time（日期时间，输入，无默认值）在下面的查询中又名@TO

存储过程返回一个整数。

从 Access 数据库中的 VBA 调用的 DLL 函数运行良好，但我想将对 SQL Server 的调用从 DLL 更改为 Access 数据库中的 VBA 代码。经过几个小时的 Google / Youtube 搜索后，我很喜欢这个 VBA 代码。

我在这个 SQL Server 数据库中没有特权，例如创建一个新的存储过程，我可以从 QueryDef 方法或类似方法调用它，只有下面列出的 SQL Server Query 之类的查询调用或某种类型的查询传递也许。

然后我执行这个从 SQL Server Management Studio v18.7.1 中的 DLL 源复制和粘贴的查询作为新查询，我将按预期将“输出”值作为总和。所以这个 SQL Server 查询工作得很好。

这个 SQL Server 查询，和 DLL 中的一样。

DECLARE @FROM AS DATETIME2 
DECLARE @TO AS DATETIME2 

SET @FROM = '2021-01-22 05:00:00' 
SET @TO = '2021-01-22 13:15:00' 

DECLARE @dt AS TABLE([Timestamp] DATETIME, [Value] INT, [Format] INT, [EquipmentCounterID] NVARCHAR(255)) 

INSERT INTO @dt 
EXEC Biop_ProductionCountersInUTC '62','OutputGood', @FROM , @TO 

SELECT 
SUM([Value]) AS 'OUTPUT' 
FROM @dt

但随后我在调试模式下运行此 VBA 代码，我将收到代码下方列出的错误代码 3078 消息。错误发生在 SQL 连接之后，当代码点击“设置记录集”时，与 SQL Server 的连接似乎工作得很好

这是到目前为止的 VBA 代码：

Function GetGoodOutoutFromPoint()
On Error GoTo Errhandler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim conn As String
Dim lsql As String

lsql = "DECLARE @FROM AS DATETIME2 " & vbNewLine & _
    "DECLARE @TO AS DATETIME2" & _
     vbNewLine & _
    "SET @FROM = '2021-01-22 05:00:00'" & vbNewLine & _
    vbNewLine & _
    vbNewLine & _
    "SET @TO = '2021-01-22 13:15:00'" & vbNewLine & _
    vbNewLine & _
    "DECLARE @dt AS TABLE([Timestamp] DATETIME, [Value] INT, [Format] INT, [EquipmentCounterID]
 NVARCHAR(255))" & vbNewLine & _
    vbNewLine & _
    "INSERT INTO @dt" & vbNewLine & _
    "EXEC Biop_ProductionCountersInUTC '62','OutputGood', @FROM , @TO" & vbNewLine & _
    vbNewLine & _
    "SELECT" & vbNewLine & _
    "SUM([Value]) AS 'OUTPUT'" & vbNewLine & _
    "FROM @dt"

conn = "Driver={SQL Server Native Client 11.0};Server=xxx;Database=POINTDBReport;Uid=xx;Pwd=xxx"
Set db = OpenDatabase("", False, False, conn)
Set rs = db.OpenRecordset(lsql, dbOpenDynaset, dbSeeChanges) ' Here occurs the error 3078

'Get the good output data an due something with it.
Set rs = Nothing
Set db = Nothing

Errhandler:
If Err.Number <> 0 Then
    MsgBox "Error retrieving data from Point SQL Server" & vbNewLine & vbNewLine & "error code " & 
Err.Number & " - " & "error decriptipon " & Err.Description _
    & vbNewLine & vbNewLine & "Contact Admin", vbCritical, "Error"
    
 If Err.Number = 3078 Then Set db = Nothing
  End If
End Function

'****************************************************** ***********************************

这是我得到的错误：

错误代码 3078 - 错误描述
Microsoft Access 数据库引擎找不到输入表或查询
'DECLARE @FROM AS DATETIME2
DECLARE @TO AS DATETIME2
SET @FROM = '2021-01-22 05:00:00'
SET @TO = ' 2021-01-22 13:15:00'

将@dt 声明为表（[时间戳] DATETIME，[值] INT，[格式] INT，[EquipmentCounterID] NVARCHAR（255））

插入@dt
EXEC Biop_ProductionCountersInUTC '62','OutputGood', @FROM , @TO

从@dt'中选择总和
（[值]）作为'输出'。

确保它存在并且其名称拼写正确。

社区中是否有人可以解决这个问题，也许可以稍微调整一下我的 VBA 代码

标签： sqlsql-servervbams-access

Answer 1

  Dim strFrom       As String
  Dim strTo         As String
  Dim SumResult     As Currency
  
  strFrom = "'2021-01-22 05:00:00'"
  strTo = "'2021-01-22 13:15:00'"
  
  With CurrentDb.QueryDefs("qryPT")
     .SQL = "EXEC MyGetSum" & strFrom & "," & strTo
     SumResult = .OpenRecordset()(0)
  End With

现在当然上面假设您有一个存储过程保存为 MyGetSum。

它看起来像这样：

CREATE PROCEDURE MyGetSum
   @FROM DATETIME2,
   @TO DATETIME2
AS
BEGIN
   SET NOCOUNT ON;
   DECLARE @dt AS TABLE([Timestamp] DATETIME, [Value] INT, [Format] INT, [EquipmentCounterID] NVARCHAR(255)) 

   INSERT INTO @dt 
   EXEC Biop_ProductionCountersInUTC '62','OutputGood', @FROM , @TO 

   SELECT 
   SUM([Value]) AS 'OUTPUT' 
   FROM @dt
END

现在，当然，您很可能没有保存的存储过程服务器端，并且由于某种原因（例如世界贫困），您必须拥有所有代码，如上所述的行？

好的，那么我将在 Access 中创建两个 pt 查询。第一个是我们的基础 t-sql + sql 代码。你抓住它，设置值，然后在第二个 pt 查询中运行它。我们再次这样做，因为在 VBA 中编写所有内联 t-sql 是一团糟（试图在这里拯救世界贫困）。

因此，我将在 Access 中创建第一个 PT 查询。您可以（必须）在查询中输入自由格式的文本（但在 MS-ACCESS SQL 视图模式下）。所以你有这个：

DECLARE @FROM AS DATETIME2 
DECLARE @TO AS DATETIME2 

SET @FROM = '@@FROM'
SET @TO = '@@TO'

DECLARE @dt AS TABLE([Timestamp] DATETIME, [Value] INT, [Format] INT, [EquipmentCounterID] NVARCHAR(255)) 

INSERT INTO @dt 
EXEC Biop_ProductionCountersInUTC '62','OutputGood', @FROM , @TO 

SELECT 
SUM([Value]) AS 'OUTPUT' 
FROM @dt

所以我们将上面的内容放在我们的 PT 查询中。我们可以“认为”这个查询作为我们的源查询。

所以，它在 MS-access 中看起来像这样：

好的，我们将上面的内容保存为 ptSumS（我们在末尾标记一个“s”作为源）。

事实上，上面的内容不一定是 PT 查询，但这会防止访问查询编辑器像我们上面那样弄乱 sql 文本。

现在，创建第二个 PT 查询（即我们将实际运行的查询）。

所以，现在我们的代码是这样的：

  Dim strFrom       As String
  Dim strTo         As String
  
  Dim SumResult     As Currency
  Dim strSQL        As String
  
  strSQL = CurrentDb.QueryDefs("ptSumS").SQL
  
  Debug.Print strSQL
  
  
  strFrom = "2021-01-22 05:00:00"
  strTo = "2021-01-22 13:15:00"
  
  strSQL = Replace(strSQL, "@@FROM", strFrom)
  strSQL = Replace(strSQL, "@@TO", strTo)
  
  
  With CurrentDb.QueryDefs("qrySum")
     .SQL = strSQL
     SumResult = .OpenRecordset()(0)
  End With

因此，无论哪种方式，使用 PT 查询都是可行的方法。

第一个示例-调用现有存储过程（因此，如果您有能力编写+创建+保存该服务器端存储过程，那么您可以在第一个示例中调用+使用+使用它。

但是，如果您没有能力保存+创建存储过程，那么我们使用第二种方法。我将 T-SQL 创建为 pt 查询，因为尝试在 VBA 编辑器中编写所有这些垃圾太痛苦了——只需在“方便的花花公子”pt 查询中输入您拥有的 t-sql。我们从不运行第一个 PT 查询 - 但将其用作 t-sql 代码的占位符。这也意味着我们可以修改该 t-sql - 而不必更改 VBA 代码。

因此，我们替换了两个日期参数，然后运行第二个示例。并且通过使用 pt-queries，我们消除了对代码中连接字符串的所有需求（同样——如果尝试在整个地方喷出连接字符串代码，代码真的很混乱）。

虽然我将结果推送到货币 VBA 类型中，但我们可以这样做：

  With CurrentDb.QueryDefs("qrySum")
     .SQL = strSQL
  End With

现在就在上面——因为我们设置了 t-sql，我们现在可以运行一个报告，甚至是一个基于上述 pt“qrySum”的简单访问查询。从 Access 的角度来看，该查询将像任何其他平面简查询一样工作。因此，在上面之后，我们可以基于 qrySum 启动报告，例如：

docmd.OpenReport "rptMyReport",acViewPreview.

或者我们可以将结果推送到这样的记录集中：

  Dim rst        As DAO.Recordset
  
  With CurrentDb.QueryDefs("qrySum")
     .SQL = strSQL
     Set rst = .OpenRecordset()
  End With

  debug.print "sum result = " & rst(0)

因此，您可以在代码中构建 strSQL，并且仍然使用上面的最终代码片段，但也可以将整个 sql mess 输入到查询中，对两个值执行替换，然后将生成的 sql 推入第二个PT 查询。我们不修改第一个，因为如果我们“替换”两个日期参数，那么在那之后，我们怎么知道最后一个值是什么——因此不能一遍又一遍地使用源查询。

但是，无论如何，您必须采用本机 17 或更高版本的驱动程序，因为当 sql server 使用 datetime2（而不是 datetime）时，标准的遗留 odbc 驱动程序将 datetime2 值作为字符串返回 - 这是史诗般的混乱。报告和 Access 中数据的一般使用也是如此 - 您确实必须确保使用较新的本机 17 odbc 驱动程序。或者我想您可以修改服务器端表并将 datetime2 列类型更改为 datetime （但这在几乎所有情况下都不是一个实用的建议，因为它会与现有的服务器端代码和查询混淆）。

编辑：从Access/内部设置连接。

因此，我们在 Access 中有两个传递查询。如前所述，FIRST 查询只是 t-ssql 代码的占位符。我们不需要（甚至不需要）连接字符串。创建第一个 PT 查询：

它现在将提示输入一个表格 - 关闭该对话框。

所以，现在点击这个：

现在将视图更改为 SQL 视图 - 使用以下命令：

好的，现在我们可以粘贴我们的第一个（源 t-sql）。这将为我们提供我在上面的原始屏幕截图。

所以我们有这个：

保存上面的 - 我们称之为 qrySumS

现在，关闭它，并创建我们的第二个查询。

再次，PT 查询，但这次这样做：

所以我们显示属性表。这是我们将设置连接字符串的地方。（如前所述，我们可以在代码中执行/拥有/设置连接字符串 - 但是，用户界面的工作量较少。

因此，在属性表中，单击连接构建器：

This will launch the ODBC connector dialogs - and that is WHERE you can setup + enter the same previous information you shared.

Select new, and then select the ODBC driver.

AS WARNED - try and select a native 17 driver.

You CAN try using "SQL Server" from that list (if you don't have native 17 installed). But that can cause issues with datetime2. "sql server" as a odbc driver is built into ALL copies of windows. If you choose native 17, then you have to install the native 17 driver on all workstations that use the access application.

Ok, so work though the dialogs.

In your case, the server name + sql instance looks to be:

SQLDKBA185

and then user + password

And DO NOT skip the database selection - often so many do!!!

This one:

When all is said and done, you wind up back to the original starting point, but with the name of the connection you typed in

And along the way, there is a "test connection" - do try it.

so, you COULD type in, or even cut+paste in the connection string, but the above connection builder (gee, I use it 10 times a day), should result in this:

Now, this 2nd query can be left blank, since our VBA code fills it out (copies from the first query - replaces the params. We save and called this qrySum in our example code.

So you can type/paste in the connection string. and you can of course use VBA code, but it makes the most sense to just use the connection builder. Note the syntax is VERY similar to the .net connection - but is often a wee bit different - so that's why I suggested to use the connection builder. (we using ODBC as opposed to .net sql provider - but you can often look at one, and translate - re-type to the format required by Access (ODBC format).

编辑 #2 如果您可以确定 datetime 列不是 datetime2，则将 t-sql 代码更改为使用 datetime，并且您可以使用“sql 驱动程序”，因此不必采用 + 安装本机 17（或更高版本）odbc要使用此应用程序的每个工作站上的驱动程序。因此，如果您使用“sql server”，那么这应该/可以在所有工作站上工作，而无需安装后来的本机 17 驱动程序。但是，如果服务器上的相关表使用 datetime2 而不是 datetime 列，则需要这样做。不要在这里忽略这个建议——如果你不解决 datetime2 与 datetime 的问题，将会导致很多痛苦和痛苦。