Calling a Procedure

[Ashish Nanda]

I need to call a update procedure written in Sql from the
Access.
My database on log on connects through ODBC and i have
the procedure stored on the server.
What is the syntax for calling it from a button?
Can someone give me the code..
Ashish Nanda

 

[Van T. Dinh]

You can create a Pass-Through Query in your Access mdb
file and use the SQL String:

Exec {YourSP} {ParameterValueList}

Set the "ODBC Connection Str" Property to an appropriate
string for your SQL Server and the "Returns Records"
Property to No if your SP does not return Records.

Then you can simply run the Pass-Through Query (Using
DoCmd.OpenQuery) to execute your SP. For example, I ran
the PT Query with:

EXEC sp_UpdatePassword @NewPassword = 'abcdef'

where "sp_UpdatePassword" is my SP.


Alternatively, (not tested) you can create an ADO
Connection Object to your SQL Server and use the Execute
method using the CommandType argument "adCmdStoredProc".

Check Access VB Help on the Execute Method of the ADO
Connection Object.

HTH
Van T. Dinh
MVP (Access)

 

[Van T. Dinh]

Isn't it a fairly drastic measure to run an SP???

IIRC, the current recommendation for MS-SQL Server back-
end is still mdb/mde file with ODBC-linked Tables.

HTH
Van T. Dinh
MVP (Access)