×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Stored Proc in .mdb ms access file not project file

Stored Proc in .mdb ms access file not project file

Stored Proc in .mdb ms access file not project file

(OP)
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?

RE: Stored Proc in .mdb ms access file not project file

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

RE: Stored Proc in .mdb ms access file not project file

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close