Stored Procedure Arguments 1

[bajo71]

Hello,
I'm trying to get the following statement which works fine in SQL Server:
EXEC spEVA_Export @EndDate='3/31/2007',@Account1='All'

to work in a VBA Access module which calls for similarly named VBA variables to be passed as arguments.

This does not work
DoCmd.OpenStoredProcedure "spEVA_Export" & "('EndDate', 'Account1')"

Nor does this...
DoCmd.OpenStoredProcedure "spEVA_Export @EndDate='" & EndDate & ", '@Account1='" & Account1

Would it be better to use some ADO method?
Thank you..........

 

[JoeAtWork]

Here's a procedure that uses ADO Command and Parameter objects to run a stored procedure. Sorry I don't have time to simplify it.

Public Sub Load(BusinessID As Long, DealerNumber As String, _
 BusinessName As String, ContactTypeID As Long, _
 IncludeDeletedRecs As Boolean)
'Purpose:   Loads data from database to put in the collection.
    Dim rsContacts As ADODB.Recordset
    Dim adoStoredProc As ADODB.Command
    Dim strProcName As String
    Dim adoParam As ADODB.Parameter
    Dim objContact As Contact
    
    On Error GoTo ErrHandler
    
    strProcName = gstrSPPrefix & "GetContacts"
    
    Set mcolContacts = New Collection
    
    mlngBusinessID = BusinessID
    mstrDealerNum = DealerNumber
    mstrBusinessName = BusinessName
    mlngContactTypeID = ContactTypeID
    mbolIncludeDeleted = IncludeDeletedRecs
    
    'Retrieve records from a stored procedure
    Set adoStoredProc = New ADODB.Command
    adoStoredProc.CommandText = strProcName
    adoStoredProc.CommandType = adCmdStoredProc
    adoStoredProc.ActiveConnection = gobjDBConn
    
    'BusinessID parameter
    Set adoParam = adoStoredProc.CreateParameter(, adInteger, _
     adParamInput, , BusinessID)
    adoStoredProc.Parameters.Append adoParam
    
    'ContactTypeID parameter
    Set adoParam = adoStoredProc.CreateParameter(, adInteger, _
     adParamInput, , ContactTypeID)
    adoStoredProc.Parameters.Append adoParam

    'IncludeDeleted parameter
    Set adoParam = adoStoredProc.CreateParameter(, adBoolean, _
     adParamInput, , IncludeDeletedRecs)
    adoStoredProc.Parameters.Append adoParam
     
    Set rsContacts = adoStoredProc.Execute
    
    'Fill collection with recordset data
    If Not (rsContacts.BOF And rsContacts.EOF) Then
        If rsContacts.BOF Then rsContacts.MoveFirst
        
        Do While Not rsContacts.EOF
            Set objContact = New Contact
                            
            objContact.Load ContactTypeID, BusinessID, _
             rsContacts("ContactNameID"), rsContacts("FirstName"), _
             nz(rsContacts("LastName"), ""), nz(rsContacts("Title"), ""), _
             nz(rsContacts("Phone"), ""), nz(rsContacts("CellPhone"), ""), _
             nz(rsContacts("Fax"), ""), nz(rsContacts("Email"), ""), _
             nz(rsContacts("Comments"), ""), _
             CBool(rsContacts("IsDeleted")), mobjMessenger
             
            mcolContacts.Add objContact, _
             ID_PREFIX & objContact.RecordID
             
            rsContacts.MoveNext
        Loop
    End If
    
    'This flag enables clients to ask if data was ever loaded
    'into the collection (IsInitialized property)
    mbolIsInitialized = True
    
    rsContacts.Close
    
Exit_Routine:
    Set objContact = Nothing
    Set rsContacts = Nothing
    Set adoParam = Nothing
    Set adoStoredProc = Nothing
    
    Exit Sub
    
ErrHandler:
    RaiseUnhandledError "Contacts", "Load", Err.Number, Err.Description
    GoTo Exit_Routine
    
End Sub

 

[bajo71]

Thanks much Joe, I will deduce the needed components!