Calling a Stored Procedure

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can someone point me to a good example of how to call a stored procedure from
an MS SQL db? I'm using Access 2k2.

Thanks in advance.

Mark
 
MChrist said:
Can someone point me to a good example of how to call a stored
procedure from an MS SQL db? I'm using Access 2k2.

Thanks in advance.

Mark
Click to expand...

You can create a pass-through query that executes the stored procedure,
with SQL like this:

EXEC YourStoredProcedureName

If the procedure doesn't return records, set the query's ReturnsRecords
property (on its property sheet in design view) to No.

When you've created the query, you can treat it like any other query.
 
The only problem is that I need to pass parameters to the sp, and I don't
want the user to have to modify the query.

Any other ideas?

Thanks,

Mark
 
MChrist said:
The only problem is that I need to pass parameters to the sp, and I
don't want the user to have to modify the query.

Any other ideas?
Click to expand...

You can use ADO objects to run the stored procedure. Here's an example
from one of my applications:

'---- start of code snippet ----

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim strConnect As String
Dim lngReturnCode As Long

' Open a connection to the back-end database and set up a
' command object to work with it.

Set cnn = New ADODB.Connection

cnn.Open fncGetConnectString()
' The above function, fncGetConnectString(), returns the connect
' string needed to access the SQL Server back-end database,
' extracting it from a linked table's Cronnect property. You
' can hard-code your connect string if you want.

Set cmd = New ADODB.Command

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn

' Set up and execute a call to the server to execute the
' stored procedure and get the result.

cmd.CommandText = "spInactivatePosition"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters("@PositionID") = m_lngOriginalPositionID
cmd.Parameters("@EndDate") = Me!txtEndDate
cmd.Parameters("@User") = CurrentUser()

cmd.Execute , , adExecuteNoRecords

' Get the proc's return code from the Command object.
lngReturnCode = cmd.Parameters("@return_value")

'---- end of code snippet ----

Note that I chose to Refresh the command object's Parameters collection,
rather than creating my own Parameter objects and adding them to the
collection. The method I used does require another round trip to the
server, so you may choose not to do that.
 
Dirk,

Thanks again for your help. I had pieced together something very similar to
the code snippet that you gave me, but now I'm having just one problem with
it.

I'm getting an error that says User does not have permission to perform this
operation on table... I'm assuming it's talking about the TRUNCATE TABLE
statement at the beginning of the sp. I've granted rights to this user for
select/update/insert/delete.

I know this isn't specifically an Acess question, but if you have any idea
what I could be doing wrong here, I would appreciate the assistance.

Thanks again

Mark
 
MChrist said:
Dirk,

Thanks again for your help. I had pieced together something very
similar to the code snippet that you gave me, but now I'm having just
one problem with it.

I'm getting an error that says User does not have permission to
perform this operation on table... I'm assuming it's talking about
the TRUNCATE TABLE statement at the beginning of the sp. I've
granted rights to this user for select/update/insert/delete.

I know this isn't specifically an Acess question, but if you have any
idea what I could be doing wrong here, I would appreciate the
assistance.
Click to expand...

SQL Server Books Online says of the TRUNCATE TABLE statement, "TRUNCATE
TABLE permissions default to the table owner, members of the sysadmin
fixed server role, and the db_owner and db_ddladmin fixed database
roles, and are not transferable." I'm not an expert on SQL Server, but
I interpret that to mean that non-admin, non-owner users won't be able
to execute that statement. Is there any reason not use the DELETE
statement to delete all records from the table intead? It would be
somewhat less efficient, but would be more flexible, and reversible.
 
Thanks Dirk,

I had the same thought to check the permissions for Truncate and discovered
that. I rewrote the line with a delete statement and with worked fine.

Thanks for your help.

Have a great day.

Mark
 
Back
Top