将记录集从Access VBA传递给SQL Server存储过程的用户定义表类型参数。

huangapple go评论65阅读模式
英文:

Pass record set from Access VBA as SQL Server stored procedure user-defined table type parameter

问题

在C#中,您可以将数据表作为SQL Server参数传递,使用用户定义的表类型。在MS Access VBA中是否也可以实现这个功能?我能够将adodb.recordset作为存储过程参数传递吗?

我尝试使用以下代码(对我来说已经适用于所有其他数据类型),但是当我尝试传递记录集时,出现了错误。如果有人能帮助我修复代码,我将不胜感激。

If ServerConOpen = False Then
    DoCmd.CancelEvent
    Exit Sub
End If
Dim cmd As New adodb.Command
Dim rs As New adodb.Recordset
rs.Open "CustData", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
cmd.ActiveConnection = cnx
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "CustDtaInsert"
cmd.Parameters.Append cmd.CreateParameter("@UserDefineTable", adUserDefined, adParamInput, , rs)
cmd.Execute
ServerConClose

我在这一行代码上得到以下错误:

cmd.Parameters.Append cmd.CreateParameter("@UserDefineTable", adUserDefined, adParamInput, , rs)
英文:

In C# you can pass data table as a user-defined table type as a SQL Server parameter. Is this possible with MS Access VBA, too? Can I pass adodb.recordset as such a stored procedure parameter?

I tried to use the following code (which already works for me with all other data types), but I got an error when I'm trying to pass record set, if some one can help with code I'd appreciate it.

If ServerConOpen = False Then
    DoCmd.CancelEvent
    Exit Sub
End If
Dim cmd    As New adodb.Command
Dim rs     As New adodb.Recordset
rs.Open "CustData", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
cmd.ActiveConnection = cnx
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "CustDtaInsert"
cmd.Parameters.Append cmd.CreateParameter("@UserDefineTable", adUserDefined, adParamInput, , rs)
cmd.Execute
ServerConClose

I get the following error on this line

cmd.Parameters.Append cmd.CreateParameter("@UserDefineTable", adUserDefined, adParamInput, , rs)

将记录集从Access VBA传递给SQL Server存储过程的用户定义表类型参数。

答案1

得分: 1

VBA 和 SQL Server 没有共同的构造来传递表格数据,因此无法直接实现。可以通过将数据写入 Access 中的链接表来完成,但这不是一个优雅的解决方案。

唯一的解决方法是将数据以文本格式(如JSON或XML)传递给存储过程,并在服务器上填充一个表格。这个技术在之前的StackOverflow问题中有详细解释。

英文:

VBA and SQL Server do not share any common constructs to pass table data so it cannot be directly achieved. It could be done by writing data to a linked table in Access but that is not an elegant solution.

The only way around it would be to pass the data to the Stored procedure in a text format such as JSON or XML and populate a table on the server. This technique was explained at length in a previous StackOverflow question.

答案2

得分: 0

我不确定这会起作用,但你可以尝试用以下方式替换引发错误的行。

set prm = cmd.CreateParameter("@UserDefineTable", adUserDefined, adParamInput, , rs)
cmd.Parameters.Append prm

首先当然要声明它。Dim prm as ADODB.Parameter

英文:

I am not certain this will work, but you could try replacing the line raising the error with the following.

set prm = cmd.CreateParameter("@UserDefineTable", adUserDefined, adParamInput, , rs)
cmd.Parameters.Append prm

First declare it of course. Dim prm as ADODB.Parameter

huangapple
  • 本文由 发表于 2023年7月31日 23:05:16
  • 转载请务必保留本文链接:https://go.coder-hub.com/76804900.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定