scott said:
it doesn't return records, just transfers data.
can you give me some syntax? it's a hard topic to search the web for.
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 ------
