Excel VB triggering Stored Procedures/Queries

R

robbath

I want to use Excel to operate SQL Server 2000 Stored Procedures. Doe
anyone know of anyway I can use Excel VB to trigger a sequence o
these?

I will also need to be able to pass a 'keyfield' (i
ParticiapantID)parameter from Excel to the Stored Procedures, is thi
possible to achieve?

Alternatively, if I cannot get to trigger Stored Procedures, I may b
forced to export the data to Access first then trigger a sequence of M
Access Queries. Again, can a query be triggered from Excel? I guess
could somehow open the .mdb and have an autoexec in Access trigger th
queries. Ideally Access would not be visible to the user during thi
process, can anybody tell me id this is possible?

Sorry for the number of different questions I have asked, but I hop
one of you might be able to help. Thanks in advance,

Robert Bat
 
B

Bob Phillips

Robert,

You can use ADO to invoke a SP from Excel, including parameters.

I could knock up some code myself, but I found this example on the net. Not
sure how good it is but give it a try

http://www.midrangeserver.com/mgo/mgo102203-story01.html

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
O

onedaywhen

robbath wrote ...
I want to use Excel to operate SQL Server 2000 Stored Procedures.
I will also need to be able to pass a 'keyfield' (ie
ParticiapantID)parameter from Excel to the Stored Procedures, is this
possible to achieve?
Click to expand...

Yes. The popular choice for Excel users MS Query (GUI tools) and ADO
(VBA code). The raw 'SQL' you want to execute is:

EXEC MyStoredProc Param1, Param2

If you go the ADO route you have much more control over the process.

--
 
Top