Stored Proc in .mdb ms access file not project file

[nrugado]

I know that you can run Stored Procedures from MS Access Project. Is there any way to call them from the standard MS Access MDB file?

 

[OnTheFly]

I have done this on a limited basis to add a record to a table and return the identity value. Following is the code I used if it is any help to you.

Function SaveActivity(strActProds As String, lngActOrg As Long) As Long
Dim ConnQ As ADODB.Connection
Dim prm As ADODB.Parameter
Dim rstValues As ADODB.Recordset
Dim lngActNewID As Long
Dim lngRecsSaved As Long

On Error GoTo SA_Error

'Get the dataset information from the form for the new activity
Set rstValues = New ADODB.Recordset
rstValues.Open "ttblActivities", Conn, adOpenStatic, adLockReadOnly, adCmdTableDirect

'Set up to run the stored procedure that will add the data
Set ConnQ = New ADODB.Connection
ConnQ.Open "Provider=SQLOLEDB;Data Source=CMRBACKUP;" & _
"Database=Client Manager Database;Trusted_Connection=yes;"

Set cmdQ = New ADODB.Command
cmdQ.ActiveConnection = ConnQ
cmdQ.CommandText = "Add_Activity"
cmdQ.CommandType = adCmdStoredProc

'Create the parameters and append them to the command object's parameter collection
Set prm = cmdQ.CreateParameter("ActID", adVarChar, adParamInput, 10, rstValues!ActID)
cmdQ.Parameters.Append prm
Set prm = cmdQ.CreateParameter("OrgID", adInteger, adParamInput, , lngActOrg)
cmdQ.Parameters.Append prm
Set prm = cmdQ.CreateParameter("ConID", adInteger, adParamInput, , rstValues!ConID)
cmdQ.Parameters.Append prm
Set prm = cmdQ.CreateParameter("ActCreateDate", adDate, adParamInput, , rstValues!ActCreateDate)
cmdQ.Parameters.Append prm
Set prm = cmdQ.CreateParameter("CMRStaffID", adVarChar, adParamInput, 5, rstValues!CMRStaffID)
cmdQ.Parameters.Append prm
Set prm = cmdQ.CreateParameter("ProdID", adVarChar, adParamInput, 50, strActProds)
cmdQ.Parameters.Append prm
Set prm = cmdQ.CreateParameter("TypeID", adInteger, adParamInput, , rstValues!TypeID)
cmdQ.Parameters.Append prm
Set prm = cmdQ.CreateParameter("ActClosed", adBoolean, adParamInput, , rstValues!ActClosed)
cmdQ.Parameters.Append prm
Set prm = cmdQ.CreateParameter("ActCloseDate", adDate, adParamInput, , rstValues!ActCloseDate)
cmdQ.Parameters.Append prm
Set prm = cmdQ.CreateParameter("ActDuration", adInteger, adParamInput, , rstValues!ActDuration)
cmdQ.Parameters.Append prm
Set prm = cmdQ.CreateParameter("ActNeedsBilling", adBoolean, adParamInput, , rstValues!ActNeedsBilling)
cmdQ.Parameters.Append prm
Set prm = cmdQ.CreateParameter("ActBillingComplete", adBoolean, adParamInput, , rstValues!ActNeedsBilling)
cmdQ.Parameters.Append prm
Set prm = cmdQ.CreateParameter("ActNotes", adVarChar, adParamInput, 6000, rstValues!ActNotes)
cmdQ.Parameters.Append prm
Set prm = cmdQ.CreateParameter("ActPermanent", adBoolean, adParamInput, , rstValues!ActPermanent)
cmdQ.Parameters.Append prm
Set prm = cmdQ.CreateParameter("ActNumber", adInteger, adParamOutput)
cmdQ.Parameters.Append prm

'Execute the Stored procedure
cmdQ.Execute RecordsAffected:=lngRecsSaved, Options:=adExecuteNoRecords

lngActNewID = cmdQ("ActNumber&quot

'Check to make sure record was inserted
If lngRecsSaved = 0 Then
MsgBox "There was a problem and the record was not saved. Please try again.", _
vbCritical, "Activity Not Saved"
SaveActivity = 0
Else
SaveActivity = lngActNewID
End If


Exit_SA:
Set rstValues = Nothing
Set prm = Nothing
Set cmdQ = Nothing
'ConnQ.Close
Set ConnQ = Nothing
Exit Function

SA_Error:
MsgBox "The following error has occured: " & vbCrLf & Err.Number & " - " & Error$, _
vbCritical, "Error: modSaveProcedures.SaveActivity"
SaveActivity = 0
Resume 'Exit_SA

End Function

 

[cmmrfrds]

If you need to make the sproc the datasource for a Form then put it in a pass-through query and make the pass-through query the datasource for your Form.

i.e.

query1
exec sp_help