CODEWITHSUNDEEP
ms-accessms-access-2010ms-access-2007
A.J
A.J

Reputation: 45

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

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)

enter image description here

Upvotes: 0

Views: 188

Answers (2)

Galaxiom
Galaxiom

Reputation: 303

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.

Upvotes: 1

mits
mits

Reputation: 926

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

Upvotes: 0

Related Questions