Passing an array or recordset to a stored procedure

[Raul]

I need to pass an array of data to a stored procedure
that will evaluate the data and insert it into the
database. I have created a VBA routine that generates
the array. I have also written a stored procedure using
local variables that does what I need to on the database
side. Now I need some help figuring out how pass the
data to the stored procedure.

Any suggestions?

Thanks,
Raul

 

[Robin Hammond]

There is a solution I use for a similar problem, but it may not be optimal
since we are using a 3 tier architecture through a web server. This is to
create a recordset at the client containing the equivalent of your array,
upload the recordset into an empty table at the database, then have the
stored proc access the table using a cursor to work through the record and
do any processing. You don't mention what the stored proc does, so difficult
to say if this would work for you.

Robin Hammond
www.enhanceddatasystems.com

 

[Paul Robinson]

Hi
The VBA function that generates the array should give the array as a
Variant output

e.g.
Public Function MakeArray(your input variables) as Variant

loads of code to create MyArray
MakeArray = MyArray (now a Variant)

end Function

Your procedure now uses this Variant as input. The function is Public,
so you can call it throughout your Project.

Sub ProcessArray(MyVariant as Variant)

do stuff
end sub

and is called by

ProcessArray MakeArray(your input variables)

Note that the Variant you are processing is a 1 based array, and you
treat it as such. You can get its dimensions using the UBound function
and refer to its elements in the normal way e.g. MyVariant(1,3)

regards
Paul

 

[Jamie Collins]

I need to pass an array of data to a stored procedure

The usual way is to pass as a delimited string.

Jamie.

--

 

[Guest]

Robin,
Creating the recordset and uploading it to a table what I would like to do;
working out the issuses is the hard part.

Thanks,
Raul

 

[Guest]

Thanks,
Raul

The usual way is to pass as a delimited string.

Jamie.

 

[Guest]

Thanks,
Raul

Hi
The VBA function that generates the array should give the array as a
Variant output

e.g.
Public Function MakeArray(your input variables) as Variant

loads of code to create MyArray
MakeArray = MyArray (now a Variant)

end Function

Your procedure now uses this Variant as input. The function is Public,
so you can call it throughout your Project.

Sub ProcessArray(MyVariant as Variant)

do stuff
end sub

and is called by

ProcessArray MakeArray(your input variables)

Note that the Variant you are processing is a 1 based array, and you
treat it as such. You can get its dimensions using the UBound function
and refer to its elements in the normal way e.g. MyVariant(1,3)

regards
Paul