Calling a stored procedure from an Access 2000 form

[jcfrasco]

I'm using Access 2000 as a front end to SQL Server 7 and I've created a stored procedure to update a history table when certain information changes. My problem is how to correctly call the stored procedure from a form when a certain action takes place, such as OnUpdate.

 

[tlbroadbent]

Use the RunSQL method of DoCmd.

docmd.runsql("exec sp_HistoryProc&quot

If your Stored Procedure needs parameters...

docmd.runsql("exec sp_HistoryProc param1,param2,...,paramN&quot

Put single quotes (') around character type parameters. Terry

;-) The single biggest challenge to learning SQL programming is unlearning procedural programming. -Joe Celko

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[Quing]

Hi Everyone,

I am also experiencing the same problem but with Access 97. I created a query definition recordset and use this statements to execute to the SQL backend.

Set Canvass = Createquerydef (Pipeline)

With Canvass
.connection = "ODBC CONNECTION
.str="Select * from Test"
.Returnvalue = True
end canvass

*Note: not the exact statements.


When I changed to .str="Exec Sampleprocedure", it doesn't create the querydef. However when I use an existing query and manually replace the sql statement to "Exec Sampleprocedure", it works.

If I use the docmd.runsql statement, how do I get the access the returned data?

Thank you.

 

[tlbroadbent]

You can only use RunSQL for queries that do not return records. Terry

;-) USER, n.: The word computer professionals use when they mean "idiot." -Dave Barry

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm