How to run a SQL Stored Procedure From Access 2000

  • Thread starter Thread starter Jane
  • Start date Start date
J

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?
 
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.
 
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)
 
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!
 
Back
Top