How to run a SQL Stored Procedure From Access 2000

[Jane]

I have an Access 2000 app that needs to run a stored
procedure against a SQL database in a remote location. I
don't administer the SQL database and they've created a
stored procedure for me to get at the information. I have
the server name and know that it's SQL. I also have the
name of the procedure, the parameters it expects and what
it should return. What is the syntax I need to use?

 

[BJ Freeman]

You will need a userID, password Database name, IP address of the SQL server
from the SQL Server Admin.
using the connection obj

con.open ("PROVIDER=SQLOLEDB;" _
&"network=dbmsscon;" _
&"PASSWORD=SQLPassword;" _
&"PERSIST SECURITY INFO=TRUE;" _
&"USER ID=SQLUserID ;" _
&"INITIAL CATALOG=SQLDatabse;" _
&"DATA SOURCE=IP")

then if you want a readonly
use con.execute ("SPname")
if you want a writeable recordset then use the normal RS.open format.

you also have to have the sQL drivers installed on the machine using the
mdb.

 

[Van T. Dinh]

If your A2K app is an ADP, then you only need:

CurrentProject.Connection.Execute ...

(Check Access VB Help for arguments of the Execute Method).

If your A2K app is an MDB, you can either

1. (By code) create a Connection to the SQL Server and
then run the Execute as above.

2. (By Pass-Through Query) You can create a PTQ with the
Connection String pointing to the SQL Server database and
the SQL String of your PTQ can be as simply as:

EXEC dbo.SPName ...

HTH
Van T. Dinh
MVP (Access)

 

[Jane]

Thanks for your help. I think I've got it now. I'm
running the stored procedure just with a pass through
query. The problem turned out to be that the codes I was
looking for weren't in this test database. Duh. Thanks!