Getting RecordCount [Resolved] (almost)

[dglienna]

How do I refer to the Count() field in my RS ? I forgot that I had already done it with the complex query. Now I need it for the simple query. If I can add the count like szlammy said, I'd like to do that.

EDIT: I have this

rs.Open "Delete TempTable .* from TempTable ", cn, adOpenKeyset, adLockOptimistic, adCmdText
 
  rs.Open "INSERT INTO TempTable (RecCount) " & _
     "SELECT  Count(*)" & _
     "From FarmingTemp", cn, adOpenKeyset, adLockOptimistic, adCmdText
  RecCount = rs.Fields(1)

So how do I get it back out. I've also tried RecCount = rs.fields("RecCount") but that returns an error.

[dglienna]

I think I figured it out:

rs.Open "select RecCount from TempTable", cn, adOpenKeyset, adLockOptimistic, adCmdText
 
  RecCount = rs.Fields("RecCount")

[dglienna]

Still like to know how to use the example that slammy suggested.

[szlamany]

Still like to know how to use the example that slammy suggested.

I am not familiar with the WORD object you are using - it appears that you don't have access to the RS from the code you posted...

Where is the execute or open of the recordset?

[dglienna]

word takes care of it all.

it I do a separate query, how do I access the count()? Where is it placed?
The only way I could get a count is my later code that placed it into a seperate field in a new table. If I use it like you did? How do I retrieve it?

I think that I see that you are putting it into an extra field in the table? I'm not sure, though.

[szlamany]

rs.Open "SELECT  Count(*) From FarmingTemp" _
    , cn, adOpenKeyset, adLockOptimistic, adCmdText
RecCount = rs.Fields(0)

OR

rs.Open "SELECT  Count(*) "RecCount" From FarmingTemp" _
    , cn, adOpenKeyset, adLockOptimistic, adCmdText
RecCount = rs.Fields("RecCount")

[dglienna]

No, I meant from THIS CODE:

Select [First Name], [Last Name], [Mailing Address], City, State, Zip
,(Select Count(*) From [My Temp]) From [MyTemp]

[szlamany]

Actually - I didn't mean to leave the FROM as FARMINGTEMP - the FROM should be the real table you are getting this data from.

There is no need for a temporary table in this example.