run stored proc from access

[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

 

[Gerard]

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

 

[Kevin3NF]

Have you looked into pass-thru queries?

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.