Passing an array or recordset to a stored procedure
 

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




"Raul" wrote in message ...
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

Passing an array or recordset to a stored procedure
 

Thanks,
Raul

"Paul Robinson" wrote:

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




"Raul" wrote in message ...
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