Recordset.AbsolutePosition returns value of -1

[Trompomerson]

Hello guys,

I am running the script below and am able to load my recordset and cycle through the values in the fields. However I want to use the .AbsolutePosition property to help place the data into specific cells in Excel. The problem is no matter what I seem to do the AbsolutePosition property alwas returns a value of -1. I know that would normally mean there are no records in my recordset but the fld.Value does show the correct data from my SQL query. I am using an ADODB Recordset.

With DataIO.rs
    .MoveFirst
 
    Do While Not .EOF
        For Each fld In .Fields
            Debug.Print fld.Value
            Debug.Print .AbsolutePosition
        Next
        .MoveNext
 
    Loop
End With

Please Help....

[ADezii]

Hello guys,

I am running the script below and am able to load my recordset and cycle through the values in the fields. However I want to use the .AbsolutePosition property to help place the data into specific cells in Excel. The problem is no matter what I seem to do the AbsolutePosition property alwas returns a value of -1. I know that would normally mean there are no records in my recordset but the fld.Value does show the correct data from my SQL query. I am using an ADODB Recordset.

With DataIO.rs
    .MoveFirst
 
    Do While Not .EOF
        For Each fld In .Fields
            Debug.Print fld.Value
            Debug.Print .AbsolutePosition
        Next
        .MoveNext
 
    Loop
End With

Please Help....

1. It is very posible that the Recordset does not support the AbsolutePosition Property, but this can be found out ahead of time:
   The Supports Method of an ADODB Recordset
2. If you are simply traversing the Recordset, you can create a 'pseudo' AbsolutePosition Property:
   

   Dim intAbsolutePosition As String
    
   intAbsolutePosition = 0
    
   With DataIO.rs
     .MoveFirst
      intAbsolutePosition = intAbsolutePosition + 1
       Do While Not .EOF
         For Each fld In .Fields
           Debug.Print fld.Value
           Debug.Print intAbsolutePosition
         Next
           .MoveNext
       Loop
   End With

[Trompomerson]

Thanks for the insight on that, it never occured to me that all of the available object properties for a recordset would not be accessible. Other than arbitrarily needing to check every recordset for compatiblity is there something in the data set that determines this? Such as how the table is set up in Access or the like? That would maybe help to determine if there is some way to change the table so that a recordset query could use the properties.

[ADezii]

Thanks for the insight on that, it never occured to me that all of the available object properties for a recordset would not be accessible. Other than arbitrarily needing to check every recordset for compatiblity is there something in the data set that determines this? Such as how the table is set up in Access or the like? That would maybe help to determine if there is some way to change the table so that a recordset query could use the properties.

I'm not exactly sure what the Parameters are, but I am under the impression that they are confusing and relatively complex. Why not simply check for this up front, then adjust the code accordingly?

Dim intAbsolutePosition As String
 
intAbsolutePosition = 0
 
If DataIO.rs.Supports(adApproxPosition) Then      'supports AbsolutePosition
  With DataIO.rs
    .MoveFirst
      Do While Not .EOF
        For Each fld In .Fields
          Debug.Print fld.Value
          Debug.Print .AbsolutePosition
        Next
          .MoveNext
      Loop
  End With
Else      'does not support AbsolutePosition
  With DataIO.rs
    .MoveFirst
     intAbsolutePosition = intAbsolutePosition + 1
      Do While Not .EOF
        For Each fld In .Fields
          Debug.Print fld.Value
          Debug.Print intAbsolutePosition
        Next
          .MoveNext
      Loop
  End With
End If

[slacker709]

I have a similar issue. I'm trying to determine the number of rows returned by the query before I shove the data into excel. I had originally written the code below, only to find that my recordset does not support it (rs.Supports(adApproxPosition) = FALSE). The goal is to notify the user when they run a query that returns more data than Excel 2003 can handle (65,536) and exit the program. I could run this on the SQL side, using TOP, but that would involve running the query twice and would hurt performance (take 2x as long). Any ideas are appreciated.

 
rs.Open cmdText, cnn1
 
'Copy the table data into the spreadsheet
If rs.RecordCount > 65500 Then  'Check for more rows than Excel 2003 can handle (65,535)
    rs.Close
    cnn1.Close
    MsgBox "The Query is returning too much data.  Please reduce your date range and try again.", vbOKOnly
    Exit Sub
Else    'Copy the table data into the spreadsheet
    Sheets("Data").Select
    Range("B2").CopyFromRecordset rs
End If
 
rs.Close
cnn1.Close
 
 

[ADezii]

I have a similar issue. I'm trying to determine the number of rows returned by the query before I shove the data into excel. I had originally written the code below, only to find that my recordset does not support it (rs.Supports(adApproxPosition) = FALSE). The goal is to notify the user when they run a query that returns more data than Excel 2003 can handle (65,536) and exit the program. I could run this on the SQL side, using TOP, but that would involve running the query twice and would hurt performance (take 2x as long). Any ideas are appreciated.

 
rs.Open cmdText, cnn1
 
'Copy the table data into the spreadsheet
If rs.RecordCount > 65500 Then  'Check for more rows than Excel 2003 can handle (65,535)
    rs.Close
    cnn1.Close
    MsgBox "The Query is returning too much data.  Please reduce your date range and try again.", vbOKOnly
    Exit Sub
Else    'Copy the table data into the spreadsheet
    Sheets("Data").Select
    Range("B2").CopyFromRecordset rs
End If
 
rs.Close
cnn1.Close
 
 

I'm sorry but I'm a little confused, what is rs.RecordCount returning?

[Stewart Ross]

Hi ADezii. The Recordcount property of a DAO recordset returns the total number of records in the recordset (at least it does if a MoveLast is done before referring to it, otherwise the count can be inaccurate).

-Stewart