Can I run Stored Procedure from Access97 1

[educate889]

I have a SQL2000 datawarehouse with a A97 FE used primarily for reporting . . . however, I have a complex report where we decided to create a stored procedure that creates a temp table and use that table for the basis of the report.

If there is a way to launch the stored procedure from within a form in Access I would appreciate some help with the syntax. I have spent that last hour and half scouring this site for any references and have come up empty.

Some help would be highly appreciated as my deadline grows closer.

Thanks in advance for your posts.

 

[psemianonymous]

Make a new query. Set it to SQL Specific->Pass-through query. Now do whatever SQL is required to run the s.p. You'll have to deal with the ODBC/DNS settings yourself, but I assume you already have something like that with your linked tables.

Now you can "open" (run) the "query" (stored procedure) just like any other query, using the DoCmd.OpenQuery command.

 

[educate889]

Thanks for the reply but I have some questions.

What did you mean by . . . "Now do whatever SQL is required to run the s.p."

This is the spot I am confused! I set up the pass-through but now I'm looking at a blank dialogue box waiting for me to put in the perfect SQL statement. This is where I am stuck. . . I have no idea how to write the statement to fire off the SP ?????

If anyone can please help with some examples I would be forever grateful!

 

[psemianonymous]

Here's a MSKB article on the subject:

http://support.microsoft.com/default.aspx?scid=kb;en-us;106492

As for direct SQL Server examples, I have none. I work with Oracle when I do at all.

 

[mibeach7]

add a button to a form to run a macro, RunCode().

add this code to a module.
Public Function DATA_PULL()

Dim MyCon As ADODB.Connection
'Dim MyDB As Database

'****************************************************************
Set MyCon = New ADODB.Connection
MyCon.ConnectionString = "DSN=PHARM92;UID=PHARM;PWD=DRUG;"
MyCon.CommandTimeout = 0

'Set MyDB = CurrentDb

'****************************************************************
MyCon.Open
MyCon.Execute ("TRUNCATE TABLE OPS.CARDS_PROCESSING;&quot
MyCon.Execute ("BEGIN OPS.CARDS_PULL(); END;&quot'Oracle Proc.
MyCon.Close

Set MyCon = Nothing
'Set MyDB = Nothing


End Function