将记录集传递给MS访问中的存储过程

So, I am currently working on an app where I need to call a stored procedure that takes a User defined table as a parameter. In .NET I am able to just pass the data in a datatable, but in MS Access VBA I am having difficulty passing an ADO recordset to the stored procedure as the user defined table.

所以，我目前正在开发一个应用程序，我需要调用一个存储过程，该过程将用户定义的表作为参数。在.NET中，我只能传递数据表中的数据，但在MS Access VBA中，我很难将ADO记录集作为用户定义的表传递给存储过程。

Private Sub DeleteRecords()
On Error GoTo Err_DeleteRecords

Dim cnn As ADODB.Connection, cmd As New ADODB.Command, param As New ADODB.Parameter

Dim i As Integer
Dim rs As ADODB.Recordset
Dim text As String
Dim gs As String

Set cnn = CurrentProject.Connection

gs = "DECLARE @StagingRecsToDelete StagingRecsToDelete_UDT; SELECT * FROM @StagingRecsToDelete"

Set rs = New ADODB.Recordset

rs.Open gs, cnn, adOpenKeyset, adLockOptimistic

If rs.RecordCount = 0 Then
    For i = 0 To Me.lstFingerPrintServiceStaging.ListCount - 1
        If Me.lstFingerPrintServiceStaging.Selected(i) Then
             text = text + Me.lstFingerPrintServiceStaging.Column(0, i)
             rs.AddNew
             rs!intFingerPrintServiceStagingID = text
        End If
    Next i
End If

If rs.RecordCount > 0 Then
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cnn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "dbo.SPFingerPrintDeleteErrors"

    Set param = cmd.CreateParameter("@ReturnValue", adInteger, adParamReturnValue)
    cmd.Parameters.Append param
    Set param = cmd.CreateParameter("StagingRecsToDelete_UDT", adVarient, adParamInput, , rs)
    cmd.Parameters.Append param
    Set param = cmd.CreateParameter("@ErrNbr", adInteger, adParamOutput)
    cmd.Parameters.Append param
    Set param = cmd.CreateParameter("@ErrMsg", adVarChar, adParamOutput, 8000)
    cmd.Parameters.Append param

    cmd.Execute
End If

Select Case cmd.Parameters("@ReturnValue").Value
    Case 1 'System error
        MsgBox cmd.Parameters("@ErrMsg").Value, vbOKOnly + vbInformation, gApplicationTitle
        Exit Sub
    Case 0 'Succeeds
        MsgBox "The Selected records have been successfully deleted.", vbOKOnly + vbInformation, gApplicationTitle
    Case -1 'User Error
        MsgBox cmd.Parameters("@ErrMsg").Value, vbOKOnly + vbInformation, gApplicationTitle
        Exit Sub
End Select

cnn.Close

Set cnn = Nothing

Exit_DeleteRecords:
 Exit Sub

Err_DeleteRecords:
 MsgBox "Form=" & Me.Name & ", Function=DeleteRecords" & vbCrLf & "Err#=" & 
Err & " " & Error$
 Resume Exit_DeleteRecords
End Sub

For the most part, all of it works, but I do not know what datatype to set the UDT to when I pass it to the stored procedure.

在大多数情况下，它都可以工作，但我不知道在将UDT传递给存储过程时将UDT设置为什么数据类型。

1 个解决方案

#1

The MS Access (DAO) does not support it.

MS Access（DAO）不支持它。

You can create an XML with your data and pass it as SP param or insert it into a temporary table one by one fisrt.

您可以使用数据创建XML并将其作为SP参数传递，或者将其逐个插入到临时表中。

#1