[AccessD] It's About Time I Learned This

Thanks.  Although I'm a DAO guy not and ADO guy.  Can it be done in DAO?
What is the name of the stored query, then "vbEmployeeVacCancelList"?

Rocky


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Doug Barnes
Sent: Friday, October 09, 2009 10:26 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] It's About Time I Learned This

Rocky,
you could adapt from this. This is from a vb project that has queries in a
Access database.

Public Sub LoadCancelList(ByVal pBadgeNbr As Long, ByVal pVacationYear As
Long, pCutoffDateSingleDays As Date, pCutOffDateWeeks)
    Dim mConn As ADODB.Connection
    Dim mCmd As ADODB.Command
    Set mCmd = New ADODB.Command
    With mCmd
        .CommandText = "vbEmployeeVacCancelList"
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("@BadgeNbr", adInteger,
adParamInput, , pBadgeNbr)
        .Parameters.Append .CreateParameter("@Vacationyear", adInteger,
adParamInput, , pVacationYear)
        .Parameters.Append .CreateParameter("@CutOffDateSingleDays", adDate,
adParamInput, , pCutoffDateSingleDays)
        .Parameters.Append .CreateParameter("@CutOffDateWeeks", adDate,
adParamInput, , pCutOffDateWeeks)
        SetConnection mConn, True, True, mpdbLocal
        Set .ActiveConnection = mConn
    End With
    ResetRST mrstCancelList
    With mrstCancelList
        .Open mCmd, , adOpenStatic, adLockReadOnly
        Set .ActiveConnection = Nothing

    End With
    mConn.Close
    Set mConn = Nothing
    Set mCmd = Nothing

End Sub

This is what the query looks like in Access:

PARAMETERS [@BadgeNbr] Long, [@VacationYear] Long, [@CutoffDateSingleDays]
DateTime, [@CutoffDateWeeks] DateTime; SELECT vacLog.VRID, vacLog.BadgeNbr,
vacLog.VRType, vacLog.VRDate, vacLog.VROccCode, vacLog.VRCrew,
vacLog.VRStatus, vacLog.VRComments, vacLog.VRDays, vacLog.vrYear,
vacLog.vrModUserID, vacLog.vrModTimestamp, vacLog.RelatedVRID,
vacLog_1.VRType AS [Related Type], vacLog_1.VRDate AS [Related Date] FROM
LKUP_VRTypes INNER JOIN (LKUP_VRStatus INNER JOIN (vacLog LEFT JOIN vacLog
AS vacLog_1 ON vacLog.RelatedVRID=vacLog_1.VRID) ON
LKUP_VRStatus.VRStatus=vacLog.VRStatus) ON LKUP_VRTypes.VRType=vacLog.VRType
WHERE (((vacLog.BadgeNbr)=[@BadgeNbr]) AND
((vacLog.VRDate)>[@CutOffDateSingleDays]) AND
((vacLog.vrYear)=[@VacationYear]) AND ((LKUP_VRStatus.VRAllowCancel)=True)
AND ((LKUP_VRTypes.VRIndividualType)=True)) OR
(((vacLog.BadgeNbr)=[@BadgeNbr]) AND ((vacLog.VRDate)>[@CutOffDateWeeks])
AND ((vacLog.vrYear)=[@VacationYear]) AND
((LKUP_VRStatus.VRAllowCancel)=True) AND
((LKUP_VRTypes.VRIndividualType)=False))
ORDER BY vacLog.VRDate;


Douglas Barnes

Starn Technical Services
P.O. Box 1172
1057 French Street
Meadville, PA  16335

P: 814.724.1045
F: 814.337.3460

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com