英文:
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)
答案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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论