VBA - Recordset to Array - Catch Overflow error & set to empty

[mmohon]

I have this code to drop a recordset in an array:

For r = 1 To rst.RecordCount
           For c = 1 To rst.Fields.Count
               If IsNull(rst(c - 1)) Or IsError(TempArray(r, c) = rst(c - 1)) Then
                   TempArray(r, c) = Empty
               Else
                   TempArray(r, c) = rst(c - 1)
               End If
           Next
           rst.MoveNext

I get overflows, I think because some fields in the Access query result in #Error. It would really make my life easy if I could catch it as it's going into the array and set it to Empty.

I tried with the IsError(rst(c-1)) and IsError(TempArray(r, c) = rst(c - 1)). Neither of those approaches worked.

Is there a better way to catch the overflow and set it to an empty cell?

 

[Norie]

How have you declared the array?

Also, where do you get the overflow?

You mention something about a range but I can't see anything about a range in the code.

By the way, you can get an array from a RecordSet using GetRows.

 

[mmohon]

I declared it like this in the top of my code,

Dim TempArray()

I grab a record set from an external Access database.

I ReDim the Array to match the size of the record set:

ReDim TempArray(1 To rst.RecordCount, 1 To rst.Fields.Count)
       For r = 1 To rst.RecordCount
           For c = 1 To rst.Fields.Count
               If IsNull(rst(c - 1)) Or IsError(TempArray(r, c) = rst(c - 1)) Then
                   TempArray(r, c) = Empty
               Else
                   TempArray(r, c) = rst(c - 1)
               End If
               
           Next


           rst.MoveNext


       Next
ws.Range(rng).Resize(rst.RecordCount, rst.Fields.Count).Value = TempArray

I get the overflow on queries where there are #Errors due to some division by Zeroes.

 

[Norie]

There are errors in the recordset?

Have you tried checking for the error message string rather than using IsError?

 

[mmohon]

Yeah,

There are queries that divide by zero and return an #Error. Unfortunately I have no control over these databases, they are fed to me from another source, and I feed query data into my excel template.

My whole setup is a table with query names, parameters, and destination ranges. My script loops through the table, reaching into a specified database for those queries, and pulls the results back to a range I have specified.

It works great, until there is a division by zero~

 

[Norie]

I tried it using ADO and a simple Access query.

The query had an expression that returned division by 0 for each record.

When I looped through the recordset it just returned blanks for those.

I never got the chance to check it they were Nulls or whatever, my laptop decided to shutdown.

 

[mmohon]

Thats ok,

I just did an "On Error Resume Next"
Skipped over the call to set the array value.

Seemed to work just fine.

Looped through my 30+ queries and filled out my excel template.

 

[Norie]

Oh, well if that works that's the important thing.

Wonder what the problem is though.