0

Don't you hate it when a program worked last month and now it fails.

I have an Access procedure that executes this (counts rows in the table with the matching date):

StrSearchCriteria = "SELECT Ticker " _
    & "FROM FundMonthlyDetailAllHistory " _
    & "WHERE AsOfDate = #" & DDate & "#; "
rs.Open StrSearchCriteria, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
i = 1
Do Until rs.EOF
    i = i + 1
    rs.MoveNext
Loop
rs.Close

This works great; no problem.

About 300 lines down the procedure I have this:

StrSearchCriteria = "SELECT Ticker, FundStatus, Category, AsOfDate " _
    & "FROM FundMonthlyDetailCurrent " _
    & "ORDER BY Category, FundStatus"
rs.Open StrSearchCriteria, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
StrCategory = " "
i = 1
Do Until rs.EOF
    If StrCategory = rs!Category Then
        i = i + 1
    Else
        i = 1
    End If
    rs!CurrentRank = i
    StrCategory = rs!Category
    DDate = rs!AsOfDate     ' save the As of Date for later processing
    rs.MoveNext
Loop
rs.Close

This sorts and counts rows by "Category".

The loop never ends because it appears the open fails, although Access seems to think everything is fine for the open. If I try to check the value of rs.Category in the Immediate window with ?rs.Category I receive a "run timer error 3704" "Operation not allowed when the object is closed"

I have verified that the table has the data as expected and that the code was not changed since last month's run of the procedure.

I decided to try last month's data again - and this also failed at the same code.

Any thoughts are appreciated? Windows/Access update maybe? I'm at a loss.

Gustav
  • 53,498
  • 7
  • 29
  • 55
LEBoyd
  • 151
  • 12
  • Add tag for programming language - is this VBA? – June7 Feb 21 '23 at 19:36
  • @June7: It is ADO in VBA. – Gustav Feb 22 '23 at 07:13
  • I ran your code, and it runs as expected without any errors. So, you are probably not posting the full story. – Gustav Feb 22 '23 at 07:16
  • Thanks. I tried to tell the "full story". Is it possible an update to Windows or Access is causing the problem? – LEBoyd Feb 23 '23 at 13:53
  • Solved... For whatever reason that I am not aware, some of the fields in the database changed Result Type from Double to Decimal. You cannot sort on Decimal fields, so the SELECT statement was failing. – LEBoyd Feb 24 '23 at 20:29

0 Answers0