Re: Stored Procedure from Access

"scott" <sbailey@xxxxxxxxxxxxxxx> wrote in message
news:%23b35wcpYGHA.3832@xxxxxxxxxxxxxxxxxxxx
it doesn't return records, just transfers data.

can you give me some syntax? it's a hard topic to search the web for.

"Dirk Goldgar" <dg@xxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ugJSbcoYGHA.1764@xxxxxxxxxxxxxxxxxxxxxxx
"scott" <sbailey@xxxxxxxxxxxxxxx> wrote in message
news:eGCmnXoYGHA.4144@xxxxxxxxxxxxxxxxxxxx
Is there any way to execute a SPROC residing on the server from an
access db on my workstation? Also, the access front-end isn't an
access project, just a regular database file.

I plan to link to the sql data tables from access, but didn't want
to create a project.

Yes. You can do it with a pass-through query, or using an ADO
Command object. As pass-through query is easiest, if you don't need
to supply parameters. Does the procedure in question return records?

For a pass-through query, you just write the SQL to execute the sproc in
the syntax of the server database. The only trick there is that you
have to compose in SQL View, set the query type to Pass-Through, and set
the query's ODBC Connect Str property to point to the server database.

To use an ADO Command object, you can use code similar to this, cribbed
from one of my applications:

'------ start of code ------
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command

Dim lngReturnCode As Long
Dim blnUpdateError As Boolean

On Error GoTo Err_Handler

' 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()

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

' Set up and execute a call to the server to execute the appropriate
' 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")

If lngReturnCode <> 0 Then
blnUpdateError = True
End If


Exit_Point:
On Error Resume Next
Set cmd = Nothing
If Not cnn Is Nothing Then
cnn.Close
Set cnn = Nothing
End If

' If the update was successful, return to the calling form.
If Not blnUpdateError Then
' Just make this form invisible so that the calling form can
' retrieve data from it.
Me.Visible = False
End If

Exit Sub
'------ end of code ------


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

Relevant Pages

  • Re: Auf einzelne Elemente einer Collection innerhalb einer Klasse zugr
    ... Dim cnn As ADODB.Connection ... Dim rstFirma As ADODB.Recordset ... Dim objFirma As clsFirma ... Dim rstFirmAkt As ADODB.Recordset ...
    (microsoft.public.de.access)
  • Re: Catastrophic Failure
    ... be a bug in the SQL Server ODBC driver rather than anything in your code. ... Dim cnn As ADODB.Connection ... Dim prm As ADODB.Parameter ...
    (microsoft.public.access.forms)
  • Re: Catastrophic Failure
    ... Make a copy of your MDB first, ... Dim cnn As ADODB.Connection ... Dim prm As ADODB.Parameter ...
    (microsoft.public.access.forms)
  • Re: Catastrophic Failure
    ... switch and made a fresh db with all the objects imported. ... Dim cnn As ADODB.Connection ... Dim prm As ADODB.Parameter ...
    (microsoft.public.access.forms)
  • Re: ADO, pls help!
    ... Dim RECSET As ADODB.Recordset ... Dim cnn As ADODB.Connection ... Set cnn = New ADODB.Connection ... upload the first record but the db is still in "edit" mode (the new records ...
    (microsoft.public.excel.programming)
Loading