EXEC sp_whatever @var_1, @var_2 from Access SQL

[CybOrg]

I have a large SQL Server database that I access via MS Access. What I'd like to do is execute a stored procedure, passing parameters from a user form. Kinda like:

PARAMETERS Forms!Main!fromdate DateTime, forms!main!todate DateTime;
EXEC sp_Total_Time_For_Period @date1=forms!main!fromdate,@date2=forms!main!todate;

It seems like it ought to be possible but I haven't found any documentation to prove it... Clues anyone? If it isn't possible, please let me know too... ;-)

 

[TheInsider]

This would be done through ADO using a 'Command' object. You can find good documentation on Microsoft's website.

 

[CybOrg]

Thanks - Can I use ADO inside Access97, and run a report off the result set?

 

[TheInsider]

Yes you can use ADO in Access 97. Depending on the way your stored procedure works... if it returns a recordset then you might need to dump the results into a temporary table and report off of that.

 

[cmmrfrds]

If you want to return a recorset from the stored procedure, then you can put it in your form recordset object by setting the rst to the cmd.execute

Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
' your logic to set up the command text
Set rst = cmd.Execute
' now you can work with the recordset

 

[tlbroadbent]

You may want to check the article at the following link. It explains "How to Simulate Parameters in an SQL Pass-Through Query."

http://support.microsoft.com/support/kb/articles/Q131/5/34.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[CybOrg]

Thanks, all!

Just what I needed!! :-D

/Cy