run stored proc from access

  • Thread starter Thread starter Jamie Ewart
  • Start date Start date
J

Jamie Ewart

Hi

I am new to SQL Server 2000 and I was wondering if someone could tell me
how to run a stored proc on my SQL Server from an access database.

Thanks


Jamie
 
Jamie,
You can't do it through a normal query(Access queries
require that the statement start with SELECT, INSERT,
DELETE, etc...) because the keyword EXECUTE is not
available. Stored procedures must be executed from code
using ADO or DAO. The following is not the only syntax,
but it is the most clear. For example using ADO:

Dim dbs as New ADODB.Connection
Dim dbsCMD as New ADODB.Command
Dim rst as ADODB.Recordset

Dbs.Mode = adModeShareDenyNone

Dbs.CursorLocation = adUseClient

Dbs.Open "SQLSERVER_NAME"

Set DbsCMD.ActiveConnection = Dbs

DbsCMD.CommandType = adCmdText
DbsCMD.CommandText = "spYOUR_SP_NAME"

set rst = dbsCMD.Execute

Hope that helps,

Gerard
Programmer / Processor
 
Back
Top