ansaurus

Question

Answer 1

+2 A:

65536 is significant as its 1 more than the maximum value that can be stored in a 16bit unsigned integer .. so something is overflowing somewhere.

This won't be a VBA integer as they are signed, but I still would replace the CInt()s with CLng() and ensure counter variables like i are declared as long

Have you run it with error handling disabled to see if any errors are raised?

As for debugging, you can swap to ADO, run it once and save the results to disk (RS.Save) then RS.Open that file for subsequent runs.

Alex K. 2010-05-18 12:24:31

The reason it's significant is because until Excel 2007, that was the maximum number of rows in a spreadsheet.

David-W-Fenton 2010-05-18 22:36:50

I found out what was causing the yellow rows to appear. When the new co-op started she tried to run the report but didn't have the DSN setup, which in turn returned an empty recordset. Which rs.EOF wasn't catching for some reason. From there it continued to loop until it crashed, which then save the template file (for some reason???) and then every subsequent run would use that template file and have that number of rows. Yes the fix for the reports was just deleting the rows in the template file haha.

Gage 2010-05-19 17:44:13

That's a reason to change the test for Not rs.EOF (to see if the recordset is empty) to rs.RecordCount <>0.

David-W-Fenton 2010-05-19 23:17:11

Answer 2

+2 A:

A few observations, none of which constitutes an answer to your question, but might point you in the right direction:

Change your tests for empty recordset/when to stop looping.

Replace this code:

  If Not rs.EOF Then
     rs.MoveFirst
     Do While Not rs.EOF And Not rs.BOF 
       [...]
       rs.MoveNext

...with this:

  If rs.RecordCount<> 0
     rs.MoveFirst
     Do While Not rs.EOF
       [...]
       rs.MoveNext

Change the way the second recordset is used.

Don't open it once for every row, filtered for that row, but open it unfiltered and sorted by the value you were previously filtering on and use FindFirst to navigate it:

  Set rs = CurrentDb.OpenRecordset("SELECT * FROM DailySummaryMain")
  Set rs2 = CurrentDb.OpenRecordset("SELECT dbo_StatusType.StatusTypeID, dbo_StatusType.Name FROM dbo_StatusType ORDER BY dbo_StatusType.StatusTypeID")
  [...]
  rs2.FindFirst "[StatusTypeID]=" & rs.Fields(3)

...Or make the second recordset obsolete.

Better, yet, it looks like there's a single value matching here, since rs2 is never navigated past the first match, so why not see if you can alter the saved QueryDef "DailySummaryMain" to join to dbo_StatusType so that the value is right there in the single recordset? Then you wouldn't need rs2 at all.

It's usually pretty unwise to refer to fields by ordinal number.

It's way too easy to completely hose your routine by adding a new field to the source SELECT statement anywhere other than the end of the SELECT statement. So, change the ordinal numbers to actual field names, so that rs(0) becomes rs("NameOfFirstField").

Use SELECT CASE instead of chained If/Then/ElseIf/Else.

Change this code:

  If CInt(rs.Fields(2).Value) = 1 Then
     row = row1
  ElseIf CInt(rs.Fields(2).Value) = 2 Then
     row = row2
  ElseIf CInt(rs.Fields(2).Value) = 3 Then
     row = row3
  Else
     row = row4
  End If

...to this:

  Select Case rs.Fields(2)
    Case 1
      row = row1
    Case 2
      row = row2
    Case 3
      row = row3
    Case 4
      row = row4
  End Select

Or, because all but one case can be constructed from the value, do this:

  If rs.Fields(2) = 4 Then
     row = row4
  Else
     row = Eval("row" & rs.Fields(2))
  End If

The context is not entirely clear (the meaning of the row and rowN items is not clear -- are they variables are objects of some kind?), so maybe that last won't work (Eval() doesn't always work in case where it seems it should), so I'd probably go with the SELECT CASE.

Excel may need .Value but Access doesn't.

Change this:

  xlapp.Range("A" & i).Value = rs.Fields(0).Value

...to this:

  xlapp.Range("A" & i).Value = rs.Fields(0)

You may not need it for the Excel side of the equation, either.

David-W-Fenton 2010-05-18 23:06:31

Thank you for the optimizations I will be implementing them in time. Like I said I didn't code this I'm just trying to fix it :P.

Gage 2010-05-19 17:45:38

BTW, I didn't see any reason at all in the code you posted why it should be looping 2^16 times. The performance issue would be greatly impacted by eliminating the second recordset, I'd think.

David-W-Fenton 2010-05-19 23:16:14

When the query for rs returned nothing it was not able to check for the end of the file for some reason. I couldn't see any properties of rs either, it said the object didn't exist. Which caused it to just keep looping until it crashed because it would never reach the end of the file.

Gage 2010-05-21 12:44:38

If the recordset didn't exist, it couldn't have looped because the loop structure itself in your code is dependent on the recordset existing. So far as I can tell, having your first test be against .RecordCount<>0, then inside that looping with Do Until rs.EOF should have done the trick. That's the way I code EVERY recordset loop and unless I leave out the rs.MoveNext, I never get infinite loops!

David-W-Fenton 2010-05-21 19:47:25

ansaurus

tags:

views:

answers:

Weird Excel Formatting