Recordset Returns No Records; SQL Returns Records

[time2hike]

I am continuing to run into this issue so, I am hoping someone else has run into it and knows how to resolve it.

I have a command on a form that runs a SQL statment in a recordset and exports it to Excel. This works 99% of the time, the other 1% of the time the SQL statment loads records (1889 records) when run as an Access query but does not load records when opened as a recordset (rst.BOF=true And rst.EOF=true). I included the full code to aide in understanding, but it stops running at Line 115 after it determines the recordset is empty.

I have tried changing my Cursor Type for the recordset with the same results. The last time I ran into this I resolved it by making a temporary table and having the recordset pull from the temporary table then deleting the temporary table, and I can go that route again if I have to but I would like to understand what is causing this and how to resolve it in the future.

    Dim conn As ADODB.Connection
    Dim stPath As String
    Dim rst As ADODB.Recordset
    Dim sSQL As String
    Dim strErrMsg As String
    Dim stName As String
    Dim oxl As Excel.Application
    Dim sFilename As String
    Dim varXLSName As Variant
    Dim sSelect As String
    Dim sFrom As String
    Dim sWhere As String
    Dim sOrder As String
    Dim stDvlpYr As Double
    Dim stWrkGrp As String
    Dim stEmp As String
    Dim stPrjtID As String
    Dim stType As String
    Dim stVrsn As String
 
    If IsNull([Forms]![frmEstRpt_Analysis]![cmdDvlpYr]) Then
        MsgBox "Please select the development year before exporting to Excel", vbExclamation + vbOKOnly, "Missing Required Information"
        Exit Sub
    Else
        stDvlpYr = [Forms]![frmEstRpt_Analysis]![cmdDvlpYr]
    End If
    If IsNull([Forms]![frmEstRpt_Analysis]![cmbWrkGrp]) Then
        stWrkGrp = "*"
    Else
        stWrkGrp = [Forms]![frmEstRpt_Analysis]![cmbWrkGrp]
    End If
    If IsNull([Forms]![frmEstRpt_Analysis]![cmbName]) Then
        stEmp = "*"
    Else
        stEmp = [Forms]![frmEstRpt_Analysis]![cmbName]
    End If
    If IsNull([Forms]![frmEstRpt_Analysis]![cmbWFSbprjtType]) Then
        stType = "*"
    Else
        stType = [Forms]![frmEstRpt_Analysis]![cmbWFSbprjtType]
    End If
    If IsNull([Forms]![frmEstRpt_Analysis]![cmbProjectID]) Then
        stPrjtID = "*"
    Else
        stPrjtID = [Forms]![frmEstRpt_Analysis]![cmbProjectID]
    End If
    If ([Forms]![frmEstRpt_Analysis]![cmdDvlpYr]) < 2014 Then
        stVrsn = 3
    Else
        stVrsn = 1
    End If
 
'    stName = "qrptEst_LaborHoursAnalysis"
'    sFilename = "LaborHours" & "_" & Year(Date) & Month(Date) & Day(Date)
'    strErrMsg = ""
' Set the string to the path of your database
    stPath = CurrentDb.Name
    Debug.Print stPath
' Open connection to the database
    Set conn = New ADODB.Connection
    conn.Provider = "Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & stPath & ";"
    conn.Open
 
    sSelect = "SELECT tCPSubprojects.SubTotalGroup" _
        & ", tDvlpYr.Yr1" _
        & ", tDvlpYr.Yr2" _
        & ", tEmpPrjtHrs.VersionID AS Version" _
        & ", tEmpPrjtHrs.OrgID" _
        & ", [tEmpPrjtHrs]![OrgID] & IIf([ztlkpCPLowOrgCrossWalk]![LowOrgDescription] Is Null,Null,'-' & [ztlkpCPLowOrgCrossWalk]![LowOrgDescription]) AS Workgroup" _
        & ", tEmpPrjtHrs.PosnNmbr" _
        & ", tEmpPrjtHrs.EmpNmbr" _
        & ", tEmpPrjtHrs.Employee" _
        & ", IIf([tCPSubprojects]![SubTotalGroup]='OH Details',Null,[tEmpPrjtHrs]![WFSbprjtType]) AS [Project Type]" _
        & ", tEmpPrjtHrs.WFSubprojectID AS [Project ID]" _
        & ", tEmpPrjtHrs.WFSbprjtTitle AS Project" _
        & ", Nz([tEmpPrjtHrs]![Yr1Hours],0) AS Yr1Hours" _
        & ", Nz([tEmpPrjtHrs]![Yr2Hours],0) AS Yr2Hours" _
        & ", [tEmpPrjtHrs]![Yr1Hours]*[tEmpPrjtHrs]![Yr1LoadedRate] AS Yr1Amt" _
        & ", [tEmpPrjtHrs]![Yr2Hours]*[tEmpPrjtHrs]![Yr2LoadedRate] AS Yr2Amt" _
        & ", [tEmpPrjtHrs]![Yr1Hours]/([tVersion]![VrsnHrs]*[tEmpPrjtHrs]![FTEPrct]) AS Yr1Prct" _
        & ", [tEmpPrjtHrs]![Yr2Hours]/([tVersion]![VrsnHrs]*[tEmpPrjtHrs]![FTEPrct]) AS Yr2Prct" _
        & ", tEmpPrjtHrs.Assumptions" _
        & ", tEmpPrjtHrs.DvlpYr" _
        & ", tEmpPrjtHrs.WFSbprjtType"
    sFrom = "FROM tDvlpYr INNER JOIN (tCPSubprojects INNER JOIN (ztlkpCPLowOrgCrossWalk " _
        & " INNER JOIN (tVersion INNER JOIN tEmpPrjtHrs ON (tVersion.VersionID = tEmpPrjtHrs.VersionID) " _
        & " AND (tVersion.DvlpYr = tEmpPrjtHrs.DvlpYr))" _
        & " ON ztlkpCPLowOrgCrossWalk.SummitLowOrg = tEmpPrjtHrs.OrgID) ON (tCPSubprojects.WFSubprojectID = tEmpPrjtHrs.WFSubprojectID) " _
        & " AND (tCPSubprojects.DvlpYr = tEmpPrjtHrs.DvlpYr)) ON tDvlpYr.DvlpYr = tVersion.DvlpYr"
    sWhere = "WHERE (((tEmpPrjtHrs.VersionID) = " & stVrsn & ")" _
        & " And((tEmpPrjtHrs.OrgID) Like '" & stWrkGrp & "')" _
        & " AND ((tEmpPrjtHrs.PosnNmbr) Like '" & stEmp & "')" _
        & " AND ((tEmpPrjtHrs.WFSubprojectID) Like '" & stPrjtID & "')" _
        & " And ((Nz([tEmpPrjtHrs]![Yr1Hours], 0)) <> 0) " _
        & " AND ((tEmpPrjtHrs.DvlpYr) = " & stDvlpYr & ") " _
        & " AND ((tEmpPrjtHrs.WFSbprjtType) Like '" & stType & "'))" _
        & " Or (((tEmpPrjtHrs.VersionID) = " & stVrsn & ")" _
        & " And((tEmpPrjtHrs.OrgID) Like '" & stWrkGrp & "')" _
        & " AND ((tEmpPrjtHrs.PosnNmbr) Like '" & stEmp & "')" _
        & " AND ((tEmpPrjtHrs.WFSubprojectID) Like '" & stPrjtID & "')" _
        & " And ((Nz([tEmpPrjtHrs]![Yr2Hours], 0)) <> 0) " _
        & " AND ((tEmpPrjtHrs.DvlpYr) = " & stDvlpYr & ") " _
        & " AND ((tEmpPrjtHrs.WFSbprjtType) Like '" & stType & "'))"
 
    ' Open recordset
    Set rst = New ADODB.Recordset
    sSQL = sSelect & vbCrLf & sFrom & vbCrLf & sWhere & ";"
'    DoCmd.Hourglass True
    Debug.Print sSQL
    rst.Open sSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
   If (rst.BOF And rst.EOF) Then
        MsgBox "No Data Available to Export", vbOKOnly, "No Data"
        DoCmd.Hourglass False
        Exit Sub
    End If
    rst.MoveLast
 
    Debug.Print rst.RecordCount
    lngRow = rst.RecordCount + 1
 
    ' Create an instance of Excel and add a workbook
    Set xlApp = New Excel.Application
    Set xlWb = xlApp.Workbooks.Add
    Set xlWs = xlWb.Worksheets("Sheet1")
 
    'Display Excel and give user control of Excel's lifetime
    xlApp.Visible = True
    xlApp.UserControl = True
 
    ' Copy field names to the first row of the worksheet
    fldCount = rst.Fields.Count
    For iCol = 1 To fldCount
        xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
    Next
 
    rst.MoveFirst
 
    ' Copy the recordset to the worksheet, starting in cell A2
    xlWs.Range("A2", "XFD" & lngRow).CopyFromRecordset rst
 
    ' Auto-fit the column widths and row heights
    xlApp.Selection.CurrentRegion.Columns.AutoFit
    xlApp.Selection.CurrentRegion.Rows.AutoFit
'    xlWs.Range("L2", "L" & lngRow).NumberFormat = "$#,###;($#,###)[Red];-;-" 'Format the Amounts
'    xlWs.Range("M2", "M" & lngRow).NumberFormat = "#,###;(#,###)[Red];-;-" 'Format the Hours
 
    'Clean up
    rst.Close
    Set rst = Nothing
    conn.Close
    Set conn = Nothing
 
 

Thank you for taking the time to read my post and any help you can provide.

[zmbd]

1) Show us the SQL from line 110 when this event happens.

2) Copy line 119 and insert after line 111, include this count with your SQL. Yes, I know, it will not return an accurate count, that's not what I'm after....

3) I'll often use the count value as the test for records rather than the bof/eof as there have been occations of goofyness with these pointers in the past. Because I don't need an accurate count at this point, I use If rs.count then if there is even 1 record in the record set then this will eveluate as true, 0 records evaluates as false within this context and then one can do the movefirst/last thing to get the correct count if needed... may not be the most logical use; however, it works (^_^)

[time2hike]

ZMDB,thanks for your reply.

1) the SQL from line 110 is:

SELECT tCPSubprojects.SubTotalGroup, tDvlpYr.Yr1, tDvlpYr.Yr2, tEmpPrjtHrs.VersionID AS Version, tEmpPrjtHrs.OrgID, [tEmpPrjtHrs]![OrgID] & IIf([ztlkpCPLowOrgCrossWalk]![LowOrgDescription] Is Null,Null,'-' & [ztlkpCPLowOrgCrossWalk]![LowOrgDescription]) AS Workgroup, tEmpPrjtHrs.PosnNmbr, tEmpPrjtHrs.EmpNmbr, tEmpPrjtHrs.Employee, IIf([tCPSubprojects]![SubTotalGroup]='OH Details',Null,[tEmpPrjtHrs]![WFSbprjtType]) AS [Project Type], tEmpPrjtHrs.WFSubprojectID AS [Project ID], tEmpPrjtHrs.WFSbprjtTitle AS Project, Nz([tEmpPrjtHrs]![Yr1Hours],0) AS Yr1Hours, Nz([tEmpPrjtHrs]![Yr2Hours],0) AS Yr2Hours, [tEmpPrjtHrs]![Yr1Hours]*[tEmpPrjtHrs]![Yr1LoadedRate] AS Yr1Amt, [tEmpPrjtHrs]![Yr2Hours]*[tEmpPrjtHrs]![Yr2LoadedRate] AS Yr2Amt, [tEmpPrjtHrs]![Yr1Hours]/([tVersion]![VrsnHrs]*[tEmpPrjtHrs]![FTEPrct]) AS Yr1Prct, [tEmpPrjtHrs]![Yr2Hours]/([tVersion]![VrsnHrs]*[tEmpPrjtHrs]![FTEPrct]) AS Yr2Prct, tEmpPrjtHrs.Assumptions, tEmpPrjtHrs.DvlpYr, tEmpPrjtHrs.WFSbprjtType
FROM tDvlpYr INNER JOIN (tCPSubprojects INNER JOIN (ztlkpCPLowOrgCrossWalk  INNER JOIN (tVersion INNER JOIN tEmpPrjtHrs ON (tVersion.VersionID = tEmpPrjtHrs.VersionID)  AND (tVersion.DvlpYr = tEmpPrjtHrs.DvlpYr)) ON ztlkpCPLowOrgCrossWalk.SummitLowOrg = tEmpPrjtHrs.OrgID) ON (tCPSubprojects.WFSubprojectID = tEmpPrjtHrs.WFSubprojectID)  AND (tCPSubprojects.DvlpYr = tEmpPrjtHrs.DvlpYr)) ON tDvlpYr.DvlpYr = tVersion.DvlpYr
WHERE (((tEmpPrjtHrs.VersionID) = 1) And((tEmpPrjtHrs.OrgID) Like '*') AND ((tEmpPrjtHrs.PosnNmbr) Like '*') AND ((tEmpPrjtHrs.WFSubprojectID) Like '*') And ((Nz([tEmpPrjtHrs]![Yr1Hours], 0)) <> 0)  AND ((tEmpPrjtHrs.DvlpYr) = 2014)  AND ((tEmpPrjtHrs.WFSbprjtType) Like '*')) Or (((tEmpPrjtHrs.VersionID) = 1) And((tEmpPrjtHrs.OrgID) Like '*') AND ((tEmpPrjtHrs.PosnNmbr) Like '*') AND ((tEmpPrjtHrs.WFSubprojectID) Like '*') And ((Nz([tEmpPrjtHrs]![Yr2Hours], 0)) <> 0)  AND ((tEmpPrjtHrs.DvlpYr) = 2014)  AND ((tEmpPrjtHrs.WFSbprjtType) Like '*'));
 
 

2) Moved Line 119 to after 111 and the count was 0. Still getting records when running the SQL in an Access Query.

[time2hike]

Just to close the loop on this, I was able to resolve the issue by changing my wildcards to % and my Like to Alike. I know that I have to do this when I am pulling data from Oracle tables but, this data is being pulled from Access tables in a seperate backend database.

Does it make sense that we would need to use % instead of * to pull this data? Regardless of the sense this was the solution.

Thanks for looking at this problem for me.

[zmbd]

check your database settings, sounds like it was toggled to ansi92 Access wildcard character reference (acc2003)

Examples of wildcard characters (acc2010/2013)

[NeoPa]

See ANSI Standards in String Comparisons for a rundown on pattern matching with particular reference to where/when each type is used.