Execute stored procedure from vba Access

[childrenfirst]

Hi,

I am trying to run a SQL stored procedure with the output in an Access (.adp file) form. Here is my vba code which does not work when it tries to execute the stored procedure... Any advise will be greatly appreciated!

Private Sub cmdCreateAuditList_Click()

Dim strRecordSource As String
Dim strAuditCreationDate As String
Dim strListDescription As String
Dim strBillerGroup As String
Dim strNumofClaims As String
Dim strStartDate As String
Dim strEndDate As String
Dim strInsertNewList As String
Dim strReportNum As String

strAuditCreationDate = Me.txtAuditCreationDate
:
:

'pass parameters to the stored procedure and execute the stored procedure

strRecordSource = "Exec [s_SelectRandomClaim] '" & strAuditCreationDate & "','" & strBillerGroup & "', " & Me.txtClaimsPerBiller.Value & ",'" & strStartDate & "','" & strEndDate & "'"

DoCmd.OpenStoredProcedure "strRecordSource"

End Sub

 

[Darrylles]

Hi,

Have you tried to debug this? Have you analysed every string variable before the execution?

If not, have you got an error message, or error code?

ATB

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[GhostWolf]

Assuming that the database and recordset objects have been declared, how about something like:

    With dbClaim.QueryDefs("s_SelectRandomClaim")
        .Parameters("AuditDate") = strAuditCreationDate
        .Parameters("BillGroup") = strBillerGroup
        ... (Set other parameters here) ...
        set rsClaim = .OpenRecordset
    End With

Naturally, I'm just guessing at your parameter names, but this process is what I've had the best luck with when working with stored procedures.

 

[childrenfirst]

Hi,

Does QueryDefs work in both Access 2000 and 2003? Also, I have not declared database and recordset set since the application is created in Access Project, and has been connected to specific database. Do I still need to declare the database and recordset?

Thank you very much!