Pass Data Set to SQL Table Variable Parameter?

[lameid]

I will probably dig on this off and on a while from now but I would really like to be able to pass a Table Variable to a SQL Proc.

Some supporting information...

First a good article about using Table Variable Parameters in SQL...

http://www.techrepublic.com/blog/datacenter/passing-table-valued-parameters-in-sql-server-2008/168

Secondly a way to pass the data progrmmatically in C#.Net...

http://lennilobel.wordpress.com/200...nd-c-custom-iterators-a-match-made-in-heaven/

I have a disconnect in translating the C# ADO.Net Datatable to VBA... (Either I haven't played in ADO enough or it can't be done?)

 

[genomon]

What part of your problem involves Microsoft Access?

Gluais faicilleach le cupan làn

 

[lameid]

The fact that I want to pass a data set from Access to the SQL stored proc.

 

[genomon]

The easiest way is to link the SQL Server table into the .mdb/.accdb
and execute some SQL to insert the data into the table.
If you are working with an SQL stored procedure, you can use an ADO command or pass-through query. I'm pretty sure Access doesn't support table variables.

Gluais faicilleach le cupan làn

 

[lameid]

I don't want to store the data which is why I'm not inserting it.

A SPT Query (SQL Pass Through) query generally does not make sense for performing "action queries" through code.

A command object does execute SQL Stored Procedures but I don't see an object that can be passed via command that is a dataset...

The unfortunate work around is of course to write the SQL Proc to run for a set of parameters that represents the columns in the dataset to load. Then the stored Proc has to be called iteratively, presumably looping through a Recordset on the Access side.

Still, I would prefer to pass a table valued parameter to a SQL proc in my circumanstance.

 

[genomon]

Sorry I missed the part about passing to a stored proc at first. What version of Access are you using? At first blush I'm thinking an ADP might show promise; I'll research that now...

Gluais faicilleach le cupan làn

 

[genomon]

Brick walls. Lots of 'em. Exactly what data do you need to get to the stored proc? Thousands of records, number of columns, datatypes, etc.?



Gluais faicilleach le cupan làn