0

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

Improve this question
3
  • I would have tried adVariant except MS docs states does not support ADO learn.microsoft.com/en-us/office/client-developer/access/…. I think answer to your question is "no". Commented Jul 31, 2023 at 17:39
  • What about creating the parameter first and then try to append it? Commented Jul 31, 2023 at 22:08
  • i think that sql server parameter can accept data table only not adodb.recordset Commented Aug 1, 2023 at 8:18

2 Answers 2

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

OK I got it , but i need a real effective solution , because i need to send multiple tables data to Sql Server first , then server will validate data after check with stored procedure , then server insert data without any errors, i found XML Files effective with API , but not with normal daily data entry
Unfortunately there aren't any really great solutions. A linked table would probably be the easiest but performance will be terrible if there is a lot of data. Another way would be to loop though a recordset and send the records one at a time to a table on the server using an insert command. Use command parameters rather than dynamic sql for the best performance but it still won't be great.
0

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

Improve this answer

1 Comment

I tried but still same error , i need a way to send my multiple data to sql server and let the server validate and insert data

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.