We help IT Professionals succeed at work.

Stored procedure in access 97

serigne
serigne asked
on
I have an access 97 database linked to a SQL server 7.0 database, how can I call stored procedure with parameter in access.
Comment
Watch Question

Commented:
Create a pass-through query.  Use standard SQL and Procedure calls in the SQL property of the query.

Commented:
You can also design a function that will be universal to all stored Proc and can be used to call any one of them.

Dim intRetCode As Integer
intRetCode = RetExecuteSP("Exec sp_UpdateQTY",iODBCTimeout)
inside the function it creates a temp qry def and assigns the values.

iODBCTimeout = 0 or what ever you yant when calling the function

Example
   Set MyQry = mydb.CreateQueryDef("")
   With MyQry
      .Connect = gcConnect  'this is a global string holding the connect string
      .ReturnsRecords = False
      .SQL = strSQL
      .ODBCTimeout = iODBCTimeout
      .Execute
   End With