By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,453 Members | 961 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,453 IT Pros & Developers. It's quick & easy.

Recordset Returns No Records; SQL Returns Records

P: 68
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.

Expand|Select|Wrap|Line Numbers
  1.     Dim conn As ADODB.Connection
  2.     Dim stPath As String
  3.     Dim rst As ADODB.Recordset
  4.     Dim sSQL As String
  5.     Dim strErrMsg As String
  6.     Dim stName As String
  7.     Dim oxl As Excel.Application
  8.     Dim sFilename As String
  9.     Dim varXLSName As Variant
  10.     Dim sSelect As String
  11.     Dim sFrom As String
  12.     Dim sWhere As String
  13.     Dim sOrder As String
  14.     Dim stDvlpYr As Double
  15.     Dim stWrkGrp As String
  16.     Dim stEmp As String
  17.     Dim stPrjtID As String
  18.     Dim stType As String
  19.     Dim stVrsn As String
  20.  
  21.     If IsNull([Forms]![frmEstRpt_Analysis]![cmdDvlpYr]) Then
  22.         MsgBox "Please select the development year before exporting to Excel", vbExclamation + vbOKOnly, "Missing Required Information"
  23.         Exit Sub
  24.     Else
  25.         stDvlpYr = [Forms]![frmEstRpt_Analysis]![cmdDvlpYr]
  26.     End If
  27.     If IsNull([Forms]![frmEstRpt_Analysis]![cmbWrkGrp]) Then
  28.         stWrkGrp = "*"
  29.     Else
  30.         stWrkGrp = [Forms]![frmEstRpt_Analysis]![cmbWrkGrp]
  31.     End If
  32.     If IsNull([Forms]![frmEstRpt_Analysis]![cmbName]) Then
  33.         stEmp = "*"
  34.     Else
  35.         stEmp = [Forms]![frmEstRpt_Analysis]![cmbName]
  36.     End If
  37.     If IsNull([Forms]![frmEstRpt_Analysis]![cmbWFSbprjtType]) Then
  38.         stType = "*"
  39.     Else
  40.         stType = [Forms]![frmEstRpt_Analysis]![cmbWFSbprjtType]
  41.     End If
  42.     If IsNull([Forms]![frmEstRpt_Analysis]![cmbProjectID]) Then
  43.         stPrjtID = "*"
  44.     Else
  45.         stPrjtID = [Forms]![frmEstRpt_Analysis]![cmbProjectID]
  46.     End If
  47.     If ([Forms]![frmEstRpt_Analysis]![cmdDvlpYr]) < 2014 Then
  48.         stVrsn = 3
  49.     Else
  50.         stVrsn = 1
  51.     End If
  52.  
  53. '    stName = "qrptEst_LaborHoursAnalysis"
  54. '    sFilename = "LaborHours" & "_" & Year(Date) & Month(Date) & Day(Date)
  55. '    strErrMsg = ""
  56. ' Set the string to the path of your database
  57.     stPath = CurrentDb.Name
  58.     Debug.Print stPath
  59. ' Open connection to the database
  60.     Set conn = New ADODB.Connection
  61.     conn.Provider = "Microsoft.ACE.OLEDB.12.0;" & _
  62.         "Data Source=" & stPath & ";"
  63.     conn.Open
  64.  
  65.     sSelect = "SELECT tCPSubprojects.SubTotalGroup" _
  66.         & ", tDvlpYr.Yr1" _
  67.         & ", tDvlpYr.Yr2" _
  68.         & ", tEmpPrjtHrs.VersionID AS Version" _
  69.         & ", tEmpPrjtHrs.OrgID" _
  70.         & ", [tEmpPrjtHrs]![OrgID] & IIf([ztlkpCPLowOrgCrossWalk]![LowOrgDescription] Is Null,Null,'-' & [ztlkpCPLowOrgCrossWalk]![LowOrgDescription]) AS Workgroup" _
  71.         & ", tEmpPrjtHrs.PosnNmbr" _
  72.         & ", tEmpPrjtHrs.EmpNmbr" _
  73.         & ", tEmpPrjtHrs.Employee" _
  74.         & ", IIf([tCPSubprojects]![SubTotalGroup]='OH Details',Null,[tEmpPrjtHrs]![WFSbprjtType]) AS [Project Type]" _
  75.         & ", tEmpPrjtHrs.WFSubprojectID AS [Project ID]" _
  76.         & ", tEmpPrjtHrs.WFSbprjtTitle AS Project" _
  77.         & ", Nz([tEmpPrjtHrs]![Yr1Hours],0) AS Yr1Hours" _
  78.         & ", Nz([tEmpPrjtHrs]![Yr2Hours],0) AS Yr2Hours" _
  79.         & ", [tEmpPrjtHrs]![Yr1Hours]*[tEmpPrjtHrs]![Yr1LoadedRate] AS Yr1Amt" _
  80.         & ", [tEmpPrjtHrs]![Yr2Hours]*[tEmpPrjtHrs]![Yr2LoadedRate] AS Yr2Amt" _
  81.         & ", [tEmpPrjtHrs]![Yr1Hours]/([tVersion]![VrsnHrs]*[tEmpPrjtHrs]![FTEPrct]) AS Yr1Prct" _
  82.         & ", [tEmpPrjtHrs]![Yr2Hours]/([tVersion]![VrsnHrs]*[tEmpPrjtHrs]![FTEPrct]) AS Yr2Prct" _
  83.         & ", tEmpPrjtHrs.Assumptions" _
  84.         & ", tEmpPrjtHrs.DvlpYr" _
  85.         & ", tEmpPrjtHrs.WFSbprjtType"
  86.     sFrom = "FROM tDvlpYr INNER JOIN (tCPSubprojects INNER JOIN (ztlkpCPLowOrgCrossWalk " _
  87.         & " INNER JOIN (tVersion INNER JOIN tEmpPrjtHrs ON (tVersion.VersionID = tEmpPrjtHrs.VersionID) " _
  88.         & " AND (tVersion.DvlpYr = tEmpPrjtHrs.DvlpYr))" _
  89.         & " ON ztlkpCPLowOrgCrossWalk.SummitLowOrg = tEmpPrjtHrs.OrgID) ON (tCPSubprojects.WFSubprojectID = tEmpPrjtHrs.WFSubprojectID) " _
  90.         & " AND (tCPSubprojects.DvlpYr = tEmpPrjtHrs.DvlpYr)) ON tDvlpYr.DvlpYr = tVersion.DvlpYr"
  91.     sWhere = "WHERE (((tEmpPrjtHrs.VersionID) = " & stVrsn & ")" _
  92.         & " And((tEmpPrjtHrs.OrgID) Like '" & stWrkGrp & "')" _
  93.         & " AND ((tEmpPrjtHrs.PosnNmbr) Like '" & stEmp & "')" _
  94.         & " AND ((tEmpPrjtHrs.WFSubprojectID) Like '" & stPrjtID & "')" _
  95.         & " And ((Nz([tEmpPrjtHrs]![Yr1Hours], 0)) <> 0) " _
  96.         & " AND ((tEmpPrjtHrs.DvlpYr) = " & stDvlpYr & ") " _
  97.         & " AND ((tEmpPrjtHrs.WFSbprjtType) Like '" & stType & "'))" _
  98.         & " Or (((tEmpPrjtHrs.VersionID) = " & stVrsn & ")" _
  99.         & " And((tEmpPrjtHrs.OrgID) Like '" & stWrkGrp & "')" _
  100.         & " AND ((tEmpPrjtHrs.PosnNmbr) Like '" & stEmp & "')" _
  101.         & " AND ((tEmpPrjtHrs.WFSubprojectID) Like '" & stPrjtID & "')" _
  102.         & " And ((Nz([tEmpPrjtHrs]![Yr2Hours], 0)) <> 0) " _
  103.         & " AND ((tEmpPrjtHrs.DvlpYr) = " & stDvlpYr & ") " _
  104.         & " AND ((tEmpPrjtHrs.WFSbprjtType) Like '" & stType & "'))"
  105.  
  106.     ' Open recordset
  107.     Set rst = New ADODB.Recordset
  108.     sSQL = sSelect & vbCrLf & sFrom & vbCrLf & sWhere & ";"
  109. '    DoCmd.Hourglass True
  110.     Debug.Print sSQL
  111.     rst.Open sSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
  112.    If (rst.BOF And rst.EOF) Then
  113.         MsgBox "No Data Available to Export", vbOKOnly, "No Data"
  114.         DoCmd.Hourglass False
  115.         Exit Sub
  116.     End If
  117.     rst.MoveLast
  118.  
  119.     Debug.Print rst.RecordCount
  120.     lngRow = rst.RecordCount + 1
  121.  
  122.     ' Create an instance of Excel and add a workbook
  123.     Set xlApp = New Excel.Application
  124.     Set xlWb = xlApp.Workbooks.Add
  125.     Set xlWs = xlWb.Worksheets("Sheet1")
  126.  
  127.     'Display Excel and give user control of Excel's lifetime
  128.     xlApp.Visible = True
  129.     xlApp.UserControl = True
  130.  
  131.     ' Copy field names to the first row of the worksheet
  132.     fldCount = rst.Fields.Count
  133.     For iCol = 1 To fldCount
  134.         xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
  135.     Next
  136.  
  137.     rst.MoveFirst
  138.  
  139.     ' Copy the recordset to the worksheet, starting in cell A2
  140.     xlWs.Range("A2", "XFD" & lngRow).CopyFromRecordset rst
  141.  
  142.     ' Auto-fit the column widths and row heights
  143.     xlApp.Selection.CurrentRegion.Columns.AutoFit
  144.     xlApp.Selection.CurrentRegion.Rows.AutoFit
  145. '    xlWs.Range("L2", "L" & lngRow).NumberFormat = "$#,###;($#,###)[Red];-;-" 'Format the Amounts
  146. '    xlWs.Range("M2", "M" & lngRow).NumberFormat = "#,###;(#,###)[Red];-;-" 'Format the Hours
  147.  
  148.     'Clean up
  149.     rst.Close
  150.     Set rst = Nothing
  151.     conn.Close
  152.     Set conn = Nothing
  153.  
  154.  
Thank you for taking the time to read my post and any help you can provide.
Apr 8 '14 #1
Share this Question
Share on Google+
5 Replies


zmbd
Expert Mod 5K+
P: 5,397
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 (^_^)
Apr 8 '14 #2

P: 68
ZMDB,thanks for your reply.

1) the SQL from line 110 is:
Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
  3. 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 '*'));
  4.  
  5.  
2) Moved Line 119 to after 111 and the count was 0. Still getting records when running the SQL in an Access Query.
Apr 8 '14 #3

P: 68
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.
Apr 14 '14 #4

zmbd
Expert Mod 5K+
P: 5,397
check your database settings, sounds like it was toggled to ansi92 Access wildcard character reference (acc2003)

Examples of wildcard characters (acc2010/2013)
Apr 15 '14 #5

NeoPa
Expert Mod 15k+
P: 31,768
See ANSI Standards in String Comparisons for a rundown on pattern matching with particular reference to where/when each type is used.
Apr 15 '14 #6

Post your reply

Sign in to post your reply or Sign up for a free account.