Reading ADO recordset returns null values

[cravincreeks]

Hello all,

I'm accessing data in an Access database using ADO and VBA .... something I have to do quite frequently. But when trying to read values from a

recordset, I'm getting null values. I'm totally stumped. It's probably easiest to explain just posting the code first

    Dim GP As Object, condb As ADODB.Connection, sConn As String, sSQL As String, rsSum As ADODB.RecordSet, lNumRecs As Long
    Dim dSumBldg As Double, dMeanBldg As Double, dSumLand As Double, dMeanLand As Double, dSumAcre As Double, dMeanAcre As Double
    sConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=G:\shared\Zendel\Redevelopment\Redevel.mdb;"
    Set condb = New ADODB.Connection
    condb.Open sConn

[COLOR=green] 'Some code that interacts with GIS software is omitted here here 
'This loop makes calls to the GIS software - this works fine.  The pRow.OID in the first SQL statement below is a long that is returned by the GIS software[/color green]

Do Until pRow Is Nothing

        sSQL = "SELECT Sum(fcParcels.APPRAISED_BLDG) AS BLDG_SUM, Avg(fcParcels.APPRAISED_BLDG) AS BLDG_MEAN, Sum(fcParcels.APPRAISED_LAND) AS 

LAND_SUM, " & _
            "Avg(fcParcels.APPRAISED_LAND) AS LAND_MEAN, Sum(fcParcels.ACREAGE) AS ACRE_SUM, Avg(fcParcels.ACREAGE) AS ACRE_MEAN " & _
            "FROM fcParcels " & _
            "WHERE (((fcParcels.CurSel)=1) AND ((fcParcels.OBJECTID)<>" & pRow.OID & "));"
        Debug.Print sSQL
        Set rsSum = New ADODB.RecordSet

    
        rsSum.Open sSQL, condb, adOpenStatic, adLockReadOnly
[COLOR=green]'        rsSum.MoveLast
'        Debug.Print rsSum.RecordCount[/color green]
        rsSum.MoveFirst
        
        [COLOR=green] 'The next line is where I get the 'Invalid Use of Null' error [/color green]
        dSumBldg = rsSum.Fields("BLDG_SUM").Value
        dMeanBldg = rsSum.Fields("BLDG_MEAN").Value
        dSumLand = rsSum.Fields("LAND_SUM").Value
        dMeanLand = rsSum.Fields("LAND_MEAN").Value
        dSumAcre = rsSum.Fields("ACRE_SUM").Value
        dMeanAcre = rsSum.Fields("ACRE_MEAN").Value
        
	rsSum.Close
        Set rsSum = Nothing
        
        [COLOR=green]' Update the values in the table using the variables immediately above[/color green]' 
        sSQL = "UPDATE fcParcels SET fcParcels.NH_IMPROV_MEAN = " & dMeanBldg & ", fcParcels.NH_IMPROV_SUM = " & dSumBldg & ", 

fcParcels.NH_LAND_MEAN = " & dMeanLand & _
            ", fcParcels.NH_LAND_SUM = " & dSumLand & ", fcParcels.NH_ACRE_MEAN = " & dMeanAcre & ", fcParcels.NH_ACRE_SUM = " & dSumAcre & _
            " WHERE ((fcParcels.OBJECTID)=" & pRow.OID & ");"
        Debug.Print sSQL
        condb.Execute sSQL
        
        sSQL = "UPDATE fcParcels SET fcParcels.CurSel = 0;"
        condb.Execute sSQL
        
        [COLOR=green]'GIS code again, ignore....[/color green]
        Set pRow = pCursor.NextRow
      
Loop

This code runs OK with no problems for several iterations of the loop. Note that I have a debug.print that prints the first SQL statement. Here

is what I copied out of the immediates window that printed just before the error when attempting to set the value of dSumBldg

SELECT Sum(fcParcels.APPRAISED_BLDG) AS BLDG_SUM, Avg(fcParcels.APPRAISED_BLDG) AS BLDG_MEAN, Sum(fcParcels.APPRAISED_LAND) AS LAND_SUM, 

Avg(fcParcels.APPRAISED_LAND) AS LAND_MEAN, Sum(fcParcels.ACREAGE) AS ACRE_SUM, Avg(fcParcels.ACREAGE) AS ACRE_MEAN FROM fcParcels WHERE 

(((fcParcels.CurSel)=1) AND ((fcParcels.OBJECTID)<>2));

If I copy that SQL statement from the immediates window and then paste it into an Access query, it returns values! There is one row (as expected)

in the query/recordset. Here are the data from the recordset:

BLDG_SUM BLDG_MEAN LAND_SUM LAND_MEAN ACRE_SUM ACRE_MEAN
7009600 212412.1211 5739900 173936.363 18.773 0.568878787878788

So the variable dSumBldg should be set to 7009600, but instead, the rsSum.Fields("BLDG_SUM").Value is Null!

When opening the recordset, I've tried a number of values/enumerations for the 3rd and 4th arguments

Any help you can provide will be greatly appreciated.

Alex

 

[Zion7]

Is it the fact that you set cursel to 0, after the 1st iteration of pRow?

Having this problem once myself, I opted to save the SQL statement as a query from the query pane. Then open a queryDef and extract the SQL property from there, and use that for my recordset.

 

[cravincreeks]

Thanks for your response. Unfortunately, setting the CurSel field back to zero isn't the problem - that's resetting the table for another iteration of necessary GIS processing. As I said, I have plugged the SQL Statement into the Access SQL query pane and it returns one row, as expected. The problem lies in reading a value from that row via ADO.

But I'll try calling the querydef. Do I have to do that thru DAO rather than ADO?

Thanks again for your feedback

Alex

 

[Zion7]

DAO

Dim qry As QueryDef

Set qry = CurrentDb.QueryDefs("qrySalesTotals")
qry.SQL = strSQL
qry.Close: Set qry = Nothing