[AccessD] It's About Time I Learned This

Should be able to do DAO, although I'm not that keen on the DAO. Yes stored
query is called vbEmployeeVacCancelList

Douglas Barnes

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

P: 814.724.1045
F: 814.337.3460


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Rocky Smolin
Sent: Friday, October 09, 2009 2:26 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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

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