By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,663 Members | 2,157 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,663 IT Pros & Developers. It's quick & easy.

Select Count(*) problem

P: n/a
MP
vb6,ado,mdb,win2k
i pass the sql string to the .Execute method on the open connection to
Table_Name(const) db table
fwiw
(the connection opened via class wrapper:)
msConnString = "Data Source=" & msDbFilename
moConn.Properties("Persist Security Info") = False
moConn.ConnectionString = msConnString
moConn.CursorLocation = adUseClient
moConn.Mode = adModeReadWrite' or using default...same result
moConn.Open
can't seem to get my sql right
here's a few versions of the many ways i've tried with the resulting error
codes following each

' sSql = "Select Count(*) As 'Total' From " & TABLE_NAME & _
' " WHERE fldType.Value = '" & sType & "' AND " & _
' " fldCondName.Value = '" & sCond & "' AND " & _
' " fldCondVar.Value = '" & sCondVar & "' AND " & _
' " fldCondLBR.Value = '2L'"
'ErrState: -2147217904 (No value given for one or more required parameters.)

sSql = "Select Count(*) As 'Total' From " & TABLE_NAME & _
" WHERE fldTypeName.Value = '" & sType & "' AND " & _
" fldCondName.Value = '" & sCond & "' AND " & _
" fldCondVar.Value = '" & sCondVar & "' AND " & _
" fldCondLBR.Value = '2L' ORDER BY fldTypeName"
'ErrState: -2147217887 <You tried to execute a query that does not include
the specified expression 'fldTypeName' as part of an aggregate function.>
' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE fldTypeName.Value = '" & sType & "' AND " & _
' " fldCondName.Value = '" & sCond & "' AND " & _
' " fldCondVar.Value = '" & sCondVar & "' AND " & _
' " fldCondLBR.Value = '2L'"
'ErrState: -2147217904 (No value given for one or more required parameters.)

' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE " & TABLE_NAME & ".Fields.Item('fldTypeName').Value = '"
& sType & "' AND " & _
' TABLE_NAME & ".Fields.Item('fldCondName').Value = '" & sCond & "'
AND " & _
' TABLE_NAME & ".Fields.Item('fldCondVar').Value = '" & sCondVar &
"' AND " & _
' TABLE_NAME & ".Fields.Item('fldCondLBR').Value = '2L'"
'ErrState: -2147217900 (Invalid use of '.', '!', or '()'. in query
expression 'tblTypeCond.Fields.Item('fldTypeName').Value = 'A' AND
tblTypeCond.Fields.Item('fldCondName').Value = '1' AND
tblTypeCond.Fields.Item('fldCondVar').Value = '3' AND
tblTypeCond.Fields.Item('fldCondLBR').Value = '2L''.)

' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE " & TABLE_NAME & ".Fields.Item('fldTypeName').Value = '"
& sType & "' AND " & _
' TABLE_NAME & ".Fields.Item('fldCondName').Value = '" & sCond & "'
AND " & _
' TABLE_NAME & ".Fields.Item('fldCondVar').Value = '" & sCondVar &
"' AND " & _
' TABLE_NAME & ".Fields.Item('fldCondLBR').Value = '2L'"
'ErrState: -2147217900 (Invalid use of '.', '!', or '()'. in query
expression 'tblTypeCond.Fields.Item('fldTypeName').Value = 'A' AND
tblTypeCond.Fields.Item('fldCondName').Value = '1' AND
tblTypeCond.Fields.Item('fldCondVar').Value = '4' AND
tblTypeCond.Fields.Item('fldCondLBR').Value = '2L''.)

' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE " & TABLE_NAME & ".fldTypeName.Value = '" & sType & "'
AND " & _
' TABLE_NAME & ".fldCondName.Value = '" & sCond & "' AND " & _
' TABLE_NAME & ".fldCondVar.Value = '" & sCondVar & "' AND " & _
' TABLE_NAME & ".fldCondLBR.Value = '2L'"
'ErrState: -2147217904 (No value given for one or more required parameters.)
' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE " & TABLE_NAME & ".Fields.Item('fldTypeName').Value = '"
& sType & "' AND " & _
' TABLE_NAME & ".Fields.Item('fldCondName').Value = '" & sCond & "'
AND " & _
' TABLE_NAME & ".Fields.Item('fldCondVar').Value = '" & sCondVar &
"' AND " & _
' TABLE_NAME & ".Fields.Item('fldCondLBR').Value = ''2L''"
'ErrState: -2147217900 (Syntax error (missing operator) in query expression
'tblTypeCond.Fields.Item('fldTypeName').Value = 'A' AND
tblTypeCond.Fields.Item('fldCondName').Value = '1' AND
tblTypeCond.Fields.Item('fldCondVar').Value = '4' AND
tblTypeCond.Fields.Item('fldCondLBR').Value = ''2L'''.)

eliminate the where clause maybe that's the problem?
'Try execute sql <Select Count(*) As 'Total' From tblTypeCond>
'ErrState: ErrNum 3265 (Item cannot be found in the collection corresponding
to the requested name or ordinal.)

yikes...what's the problem???

i'm running out of ideas
any help?
Thanks
Mark
Oct 25 '06 #1
Share this Question
Share on Google+
22 Replies


P: n/a

MP wrote:
vb6,ado,mdb,win2k
i pass the sql string to the .Execute method on the open connection to
Table_Name(const) db table
fwiw
(the connection opened via class wrapper:)
msConnString = "Data Source=" & msDbFilename
moConn.Properties("Persist Security Info") = False
moConn.ConnectionString = msConnString
moConn.CursorLocation = adUseClient
moConn.Mode = adModeReadWrite' or using default...same result
moConn.Open
can't seem to get my sql right
here's a few versions of the many ways i've tried with the resulting error
codes following each

' sSql = "Select Count(*) As 'Total' From " & TABLE_NAME & _
' " WHERE fldType.Value = '" & sType & "' AND " & _
' " fldCondName.Value = '" & sCond & "' AND " & _
' " fldCondVar.Value = '" & sCondVar & "' AND " & _
' " fldCondLBR.Value = '2L'"
'ErrState: -2147217904 (No value given for one or more required parameters.)

sSql = "Select Count(*) As 'Total' From " & TABLE_NAME & _
" WHERE fldTypeName.Value = '" & sType & "' AND " & _
" fldCondName.Value = '" & sCond & "' AND " & _
" fldCondVar.Value = '" & sCondVar & "' AND " & _
" fldCondLBR.Value = '2L' ORDER BY fldTypeName"
'ErrState: -2147217887 <You tried to execute a query that does not include
the specified expression 'fldTypeName' as part of an aggregate function.>
' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE fldTypeName.Value = '" & sType & "' AND " & _
' " fldCondName.Value = '" & sCond & "' AND " & _
' " fldCondVar.Value = '" & sCondVar & "' AND " & _
' " fldCondLBR.Value = '2L'"
'ErrState: -2147217904 (No value given for one or more required parameters.)

' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE " & TABLE_NAME & ".Fields.Item('fldTypeName').Value = '"
& sType & "' AND " & _
' TABLE_NAME & ".Fields.Item('fldCondName').Value = '" & sCond & "'
AND " & _
' TABLE_NAME & ".Fields.Item('fldCondVar').Value = '" & sCondVar &
"' AND " & _
' TABLE_NAME & ".Fields.Item('fldCondLBR').Value = '2L'"
'ErrState: -2147217900 (Invalid use of '.', '!', or '()'. in query
expression 'tblTypeCond.Fields.Item('fldTypeName').Value = 'A' AND
tblTypeCond.Fields.Item('fldCondName').Value = '1' AND
tblTypeCond.Fields.Item('fldCondVar').Value = '3' AND
tblTypeCond.Fields.Item('fldCondLBR').Value = '2L''.)

' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE " & TABLE_NAME & ".Fields.Item('fldTypeName').Value = '"
& sType & "' AND " & _
' TABLE_NAME & ".Fields.Item('fldCondName').Value = '" & sCond & "'
AND " & _
' TABLE_NAME & ".Fields.Item('fldCondVar').Value = '" & sCondVar &
"' AND " & _
' TABLE_NAME & ".Fields.Item('fldCondLBR').Value = '2L'"
'ErrState: -2147217900 (Invalid use of '.', '!', or '()'. in query
expression 'tblTypeCond.Fields.Item('fldTypeName').Value = 'A' AND
tblTypeCond.Fields.Item('fldCondName').Value = '1' AND
tblTypeCond.Fields.Item('fldCondVar').Value = '4' AND
tblTypeCond.Fields.Item('fldCondLBR').Value = '2L''.)

' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE " & TABLE_NAME & ".fldTypeName.Value = '" & sType & "'
AND " & _
' TABLE_NAME & ".fldCondName.Value = '" & sCond & "' AND " & _
' TABLE_NAME & ".fldCondVar.Value = '" & sCondVar & "' AND " & _
' TABLE_NAME & ".fldCondLBR.Value = '2L'"
'ErrState: -2147217904 (No value given for one or more required parameters.)
' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE " & TABLE_NAME & ".Fields.Item('fldTypeName').Value = '"
& sType & "' AND " & _
' TABLE_NAME & ".Fields.Item('fldCondName').Value = '" & sCond & "'
AND " & _
' TABLE_NAME & ".Fields.Item('fldCondVar').Value = '" & sCondVar &
"' AND " & _
' TABLE_NAME & ".Fields.Item('fldCondLBR').Value = ''2L''"
'ErrState: -2147217900 (Syntax error (missing operator) in query expression
'tblTypeCond.Fields.Item('fldTypeName').Value = 'A' AND
tblTypeCond.Fields.Item('fldCondName').Value = '1' AND
tblTypeCond.Fields.Item('fldCondVar').Value = '4' AND
tblTypeCond.Fields.Item('fldCondLBR').Value = ''2L'''.)

eliminate the where clause maybe that's the problem?
'Try execute sql <Select Count(*) As 'Total' From tblTypeCond>
'ErrState: ErrNum 3265 (Item cannot be found in the collection corresponding
to the requested name or ordinal.)

yikes...what's the problem???

i'm running out of ideas
any help?
Thanks
Mark
You don't need to dereference the column names to determine or set the
value. Taking your first example:

sSql = "Select Count(*) As 'Total' From " & TABLE_NAME & _
' " WHERE fldType = '" & sType & "' AND " & _
' " fldCondName = '" & sCond & "' AND " & _
' " fldCondVar = '" & sCondVar & "' AND " & _
' " fldCondLBR = '2L'"
etc. Obviously I can't vouch for the actual efficacy of this, since
you don't provide table defs.

Edward

Oct 25 '06 #2

P: n/a
MP

<te********@hotmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...
>
You don't need to dereference the column names to determine or set the
value. Taking your first example:

sSql = "Select Count(*) As 'Total' From " & TABLE_NAME & _
' " WHERE fldType = '" & sType & "' AND " & _
' " fldCondName = '" & sCond & "' AND " & _
' " fldCondVar = '" & sCondVar & "' AND " & _
' " fldCondLBR = '2L'"

etc. Obviously I can't vouch for the actual efficacy of this, since
you don't provide table defs.

Edward
Thanks for looking at it.
I knew I didn't *need* to explicitly use .Value but I always heard it's best
not to rely on default properties.
I'm not sure its' *wrong* to explicitly specify the .Value in a Select
statement either...but maybe it is.
In any case it still doesn't work, the error running the above is:
'ErrState: ErrNum 3265 (Item cannot be found in the collection corresponding
to the requested name or ordinal.)

There was a typo in the first example but i fixed it before running this
again ... fldType is fldTypeName
the actual string run was:
' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE fldTypeName = '" & sType & "' AND " & _
' " fldCondName = '" & sCond & "' AND " & _
' " fldCondVar = '" & sCondVar & "' AND " & _
' " fldCondLBR = '2L'"

Earlier in the prog I run a string to get a distinct group
sSql = "SELECT DISTINCT fldTypeName, fldCondName, fldCondvar, fldcondlbr"
& _
" FROM " & TABLE_NAME & _
" ORDER BY fldTypeName, fldCondName, fldCondvar, fldcondlbr"
FormReport "Try execute sql <" & sSql & ">"
Set oRs = moDb.Execute(sSql)
that returns me a set with several records so the field names are right
(Table_Name is a string constant for the table name.)

now i'm trying to use the same open connection to get another recordset with
the count
maybe you cant' reuse a connection?????

i'm looping through the above recordset to try to get a count for each group
within it
While Not oRs.EOF
sType = oRs.Fields.Item("fldTypeName").Value
sCond = oRs.Fields.Item("fldCondName").Value
sCondVar = oRs.Fields.Item("fldCondVar").Value
FormReport "Check " & sType & sCond & sCondVar
so now i build the sql string using those variables
sSql = "Select Count(*) From " & TABLE_NAME & _
" WHERE fldTypeName = '" & sType & "' AND " & _
" fldCondName = '" & sCond & "' AND " & _
" fldCondVar = '" & sCondVar & "' AND " & _
" fldCondLBR = '2L'"

'try to get another recordset via same connection...mayb that the problem?
Set oRsCount = moDb.Execute(sSql)
and thats when i get the errors

I can't even drop all the wheres' and get it to work
sSql = "Select Count(*) From " & TABLE_NAME

Try execute sql <Select Count(*) From tblTypeCond>
ErrState: ErrNum 3265 (Item cannot be found in the collection corresponding
to the requested name or ordinal.)

I know i don't have a field named Count or one named * but I thought that
was a generic function that should run on any table...
do you see what i'm doing wrong?

I spent about 4 hours on google reading tons of posts on Select Count(*) etc
and don't see what I'm doing wrong.

Thanks
mark
Oct 25 '06 #3

P: n/a
MP

"MP" <no****@Thanks.comwrote in message
news:JR******************@tornado.rdc-kc.rr.com...
>
<te********@hotmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...

You don't need to dereference the column names to determine or set the
value. Taking your first example:
I should have tried this earlier...
the problem isn't in the select count statement it's in reusing the
connection...
if i isolate the count to a single call on the connection it works
sSql = "Select Count(*) From " & TABLE_NAME
FormReport "Try execute sql <" & sSql & ">"
Set oRs = moDb.Execute(sSql)
lCount2 = oRs.Fields(0).Value
FormReport "Lcount2: <" & lCount2 & ">"

result:
Try execute sql <Select Count(*) From tblTypeCond>
Lcount2: <37>

so I guess I have to establish multiple connections to get multiple
recordsets???
seems I cant do
Set oRs1 = oConn.Execute(sql1)
while not ors1.eof
set ors2 = oconn.execute(sql2)
etc
ors1.movenext
wend

i guess i need something like
Set oRs1 = oConn1.Execute(sql1)
Set oConn2 = new connnection to same database
while not ors1.eof
sql2 = based on ors1 values
set ors2 = oconn2.execute(sql2)
etc
ors1.movenext
wend

is that right?
thanks
Oct 25 '06 #4

P: n/a
"MP" <no****@Thanks.comwrote in
news:JR******************@tornado.rdc-kc.rr.com:
I knew I didn't *need* to explicitly use .Value but I always heard
it's best not to rely on default properties.
There is no Value property in a SQL statement. The fields in a
recordset have a Value property, but not in SQL.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Oct 25 '06 #5

P: n/a
"MP" <no****@Thanks.comwrote in
news:JR******************@tornado.rdc-kc.rr.com:
I spent about 4 hours on google reading tons of posts on Select
Count(*) etc and don't see what I'm doing wrong.
I don't think you need to do that. Look at my other post about the
self-join. You might want to do the Count() in the virtual table I
suggested in that post if that number is important and you want only
one record returned by the self-join. But I think you need both the
count and the value assigned to your last field, no?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Oct 25 '06 #6

P: n/a
"MP" <no****@Thanks.comwrote in
news:Zj******************@tornado.rdc-kc.rr.com:
so I guess I have to establish multiple connections to get
multiple recordsets???
[]
is that right?
I don't do ADO. There are none of these problems in DAO at all.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Oct 25 '06 #7

P: n/a
MP
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
"MP" <no****@Thanks.comwrote in
news:JR******************@tornado.rdc-kc.rr.com:
I knew I didn't *need* to explicitly use .Value but I always heard
it's best not to rely on default properties.

There is no Value property in a SQL statement. The fields in a
recordset have a Value property, but not in SQL.

--
AH HA!
:-)
Thanks for that.
But i dont' think that was my *only* problem <g>
somethings wrong in the loop i'm calling this in
(see other posts this thread)

even with no fieldnames in the sql it wasnt' working
(see other posts this thread)

if i call it outside of a loop...on it's own it works....
still looking for the solution
Oct 25 '06 #8

P: n/a
MP
David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
"MP" <no****@Thanks.comwrote in
news:JR******************@tornado.rdc-kc.rr.com:
I spent about 4 hours on google reading tons of posts on Select
Count(*) etc and don't see what I'm doing wrong.

I don't think you need to do that. Look at my other post about the
self-join. You might want to do the Count() in the virtual table I
suggested in that post if that number is important and you want only
one record returned by the self-join. But I think you need both the
count and the value assigned to your last field, no?
I'm studying that post now and will post back when I get it figured out.
Thanks again
Oct 25 '06 #9

P: n/a
MP
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
"MP" <no****@Thanks.comwrote in
news:Zj******************@tornado.rdc-kc.rr.com:
so I guess I have to establish multiple connections to get
multiple recordsets???

[]
is that right?

I don't do ADO. There are none of these problems in DAO at all.
ok ok, don't rub it in :-)
you're eventually gonnna talk me into having to learn dao now eh?
<vbg>


Oct 25 '06 #10

P: n/a
"MP" <no****@Thanks.comwrote in
news:UZ*******************@tornado.rdc-kc.rr.com:
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
>"MP" <no****@Thanks.comwrote in
news:Zj******************@tornado.rdc-kc.rr.com:
so I guess I have to establish multiple connections to get
multiple recordsets???

[]
is that right?

I don't do ADO. There are none of these problems in DAO at all.

ok ok, don't rub it in :-)
you're eventually gonnna talk me into having to learn dao now eh?
You'll find it very easy. The only thing that's different in the
code you've already done is how you open your connections and how
you initialize your recordsets. And ADO has only Find for
navigation, whereas DAO has FindFirst and FindNext. Other than that
in this context, they are similar enough that it wouldn't take long
to port the code at all.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Oct 25 '06 #11

P: n/a
MP
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
>
You'll find it very easy. The only thing that's different in the
code you've already done is how you open your connections and how
you initialize your recordsets. And ADO has only Find for
navigation, whereas DAO has FindFirst and FindNext. Other than that
in this context, they are similar enough that it wouldn't take long
to port the code at all.
Ok this seems to give the counts
Now I want to read back the fields manually to make sure i'm getting the
right count.

to that end i hoped to be abel to get the count as well as the fields back
in a recordset

If the sql "sSql = "Select Count (*) From " & TABLE_NAME & " Where ....etc "
creates a one field record containing the count of records matching the
criteria,
is there a way to get it to also return the fields themselves?

so i can do
While Not rs.eof
For Each Fld in ors.Flds
Debug.print fld.Name & fld.Value
Next
Ors.MoveNext
Wend

so I can see an actual printout of the values and convince myself the count
is correct?

my failed attempts at the syntax follow at bottom of post
<g>

or do I need to loop twice, once to get the count and a second time to get
the fields to read?
there's some trick about aggregate functions and what they return that I
don't get yet....based on various error codes i get when trying different
things

Thanks.
fwiw heres' the dao code to get the counts of L per distinct 3 field group
'(sRpt is a string var set elsewhere, FormReport is my logging routine,
other vars also set elsewhere, Option Explicit is always on)
Sub DAOOpenRecordset2()
Dim db As DAO.Database, rst As DAO.Recordset, rst2 As DAO.Recordset,
fld As DAO.Field
Set db = DBEngine.OpenDatabase(DATABASE_NAME)

sSql = "SELECT DISTINCT fldTypeName, fldCondName, fldCondVar FROM " &
TABLE_NAME & " ORDER BY fldTypeName, fldCondName, fldCondVar"
Set rst = db.OpenRecordset (sSql, dbOpenForwardOnly, dbReadOnly)
While Not rst.EOF
sType = rst.Fields("fldTypeName").Value
sCond = rst.Fields("fldCondName").Value
sCondVar = rst.Fields("fldCondVar").Value

sSql = "Select Count (*) From " & TABLE_NAME & " Where fldTypeName =
'" & sType & "' AND fldCondName = '" & sCond & "' AND fldCondVar = '" &
sCondVar & "' AND fldCondLbr = '2L'"

Set rst2 = db.OpenRecordset(sSql, dbOpenForwardOnly, dbReadOnly)

sRpt = sRpt & rst2.Fields(0).Value & " 2L hits for " & sType & sCond &
sCondVar & vbCrLf
rst2.Close
Set rst2 = Nothing
rst.MoveNext
Wend

FormReport sRpt

' Close the recordset
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing

End Sub 'DAOOpenRecordset2

and here's the report

0 2L hits for A10
3 2L hits for A11
0 2L hits for A12
0 2L hits for A13
0 2L hits for A14
I tried this to get both count and fields but it didn't like the string ( i
show *condition* here just to eliminate clutter here - the acutal condition
is ok it's the count i cant' get right)

sSql = "Select fldTypeName, fldCondName, fldCondVar, fldCondLbr, Count (*)
AS TotalCount From " & TABLE_NAME & " Where *condition*
ErrState: 3122 (You tried to execute a query that does not include the
specified expression 'fldTypeName' as part of an aggregate function.)

also tried count first and fields after, still no joy
sSql = "Select Count (*) AS TotalCount, fldTypeName, fldCondName,
fldCondVar, fldCondLbr From " & TABLE_NAME & " Where *condition*
ErrState: 3122 (You tried to execute a query that does not include the
specified expression 'fldTypeName' as part of an aggregate function.)

based on previous error message i tried to include the field name in the
count function ... wild guessing obviously :-)
sSql = "Select Count (fldTypeName) AS TotalCount, fldTypeName,
fldCondName, fldCondVar, fldCondLbr From " & TABLE_NAME & " Where
*condition*
ErrState: 3122 (You tried to execute a query that does not include the
specified expression 'fldTypeName' as part of an aggregate function.)

so i thought maybe i needed all the field names (even though it said i
didn't use the one I did use)
sSql = "Select Count (fldTypeName, fldCondName, fldCondVar,
fldCondLbr) AS TotalCount, fldTypeName, fldCondName, fldCondVar, fldCondLbr
From " & TABLE_NAME & " Where *condition*
ErrState: 3075 (Wrong number of arguments used with function in query
expression 'Count (fldTypeName, fldCondName, fldCondVar, fldCondLbr)'.)

so at this point i have to admit I don't know how to use the Count()
function to get both a count and some records at the same time

now i'm looping through twice, once to get the count and a second time to
read the records...that works but seems redundant...(and it did confirm that
the count was returning correctly)

surely there's a way to get records and also a count in one statement?
Oct 25 '06 #12

P: n/a
"MP" <no****@Thanks.comwrote in
news:Vk*******************@tornado.rdc-kc.rr.com:
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
>>
You'll find it very easy. The only thing that's different in the
code you've already done is how you open your connections and how
you initialize your recordsets. And ADO has only Find for
navigation, whereas DAO has FindFirst and FindNext. Other than
that in this context, they are similar enough that it wouldn't
take long to port the code at all.

Ok this seems to give the counts
Now I want to read back the fields manually to make sure i'm
getting the right count.

to that end i hoped to be abel to get the count as well as the
fields back in a recordset

If the sql "sSql = "Select Count (*) From " & TABLE_NAME & " Where
....etc "
creates a one field record containing the count of records
matching the
criteria,
is there a way to get it to also return the fields themselves?

so i can do
While Not rs.eof
For Each Fld in ors.Flds
Debug.print fld.Name & fld.Value
Next
Ors.MoveNext
Wend

so I can see an actual printout of the values and convince myself
the count is correct?
Not with that SQL, which has only one field.

I still think the self-join would work better.
my failed attempts at the syntax follow at bottom of post
<g>

or do I need to loop twice, once to get the count and a second
time to get the fields to read?
There's nothing to loop, because you aren't returning the data in
the SQL above.
there's some trick about aggregate functions and what they return
that I don't get yet....based on various error codes i get when
trying different things
In Access, I'd likely use one of the domain functions, but you're
not in Access, so that's not an option.

You could open a recordset to get the count. It will be very fast.
It's faster than opening the full recordset and moving to the last
record to get it from .RecordCount (it's not guaranteed to be
accurate until you move to the last record in the recordset). So the
SELECT Count(*) is going to be the best way to get the count. And it
will be very fast. You'll want to do something like this:

SELECT f1, f2, f3, Count(*) AS RecordCount
FROM table1
WHERE f4 Like "?R"
GROUP BY f1, f2, f3

If you want to do both L and R, then add "And f4 Like "?L", and then
you can put f4 in the SELECT and GROUP BY clause.

If you had Access to write you SQL in it would be a lot easier!

And in the end, I think what would really be useful would be a
crosstab, with columns for each of the values in the 4th field. I've
posted that at the end of this message.
fwiw heres' the dao code to get the counts of L per distinct 3
field group '(sRpt is a string var set elsewhere, FormReport is my
logging routine, other vars also set elsewhere, Option Explicit is
always on) Sub DAOOpenRecordset2()
Dim db As DAO.Database, rst As DAO.Recordset, rst2 As
DAO.Recordset,
fld As DAO.Field
Set db = DBEngine.OpenDatabase(DATABASE_NAME)

sSql = "SELECT DISTINCT fldTypeName, fldCondName, fldCondVar
FROM " &
TABLE_NAME & " ORDER BY fldTypeName, fldCondName, fldCondVar"
Set rst = db.OpenRecordset (sSql, dbOpenForwardOnly,
dbReadOnly) While Not rst.EOF
sType = rst.Fields("fldTypeName").Value
sCond = rst.Fields("fldCondName").Value
sCondVar = rst.Fields("fldCondVar").Value

sSql = "Select Count (*) From " & TABLE_NAME & " Where
fldTypeName =
'" & sType & "' AND fldCondName = '" & sCond & "' AND fldCondVar =
'" & sCondVar & "' AND fldCondLbr = '2L'"

Set rst2 = db.OpenRecordset(sSql, dbOpenForwardOnly,
dbReadOnly)
Forward-type recordsets are not faster. You can open a Dynaset and
it will be faster, because the forward-type recordset will populate
itself to the last record before you can do anything with it, while
the Dynaset will let you start walking through the records while the
end of the recordset is still loading.

This is counterintuitive, but it's the way it works.
sRpt = sRpt & rst2.Fields(0).Value & " 2L hits for " & sType
& sCond &
sCondVar & vbCrLf
rst2.Close
Set rst2 = Nothing
Since you're looping and re-opening the recordset, move the Set rst2
= Nothing outside your loop.
rst.MoveNext
Wend
Here's where Set rst2 = Nothing would go.
FormReport sRpt

' Close the recordset
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
I'd check that each of this was Not Nothing before closing them. If
they aren't open you'll get an error.
End Sub 'DAOOpenRecordset2

and here's the report

0 2L hits for A10
3 2L hits for A11
0 2L hits for A12
0 2L hits for A13
0 2L hits for A14
I tried this to get both count and fields but it didn't like the
string ( i show *condition* here just to eliminate clutter here -
the acutal condition is ok it's the count i cant' get right)

sSql = "Select fldTypeName, fldCondName, fldCondVar, fldCondLbr,
Count (*)
AS TotalCount From " & TABLE_NAME & " Where *condition*
ErrState: 3122 (You tried to execute a query that does not include
the specified expression 'fldTypeName' as part of an aggregate
function.)
Right. You need a GROUP BY clause that includes all the
non-aggregate function fields in it, i.e:

GROUP BY fldTypeName, fldCondName, fldCondVar, fldCondLbr

This goes after the WHERE clause (where ORDER BY normally goes).
also tried count first and fields after, still no joy
sSql = "Select Count (*) AS TotalCount, fldTypeName,
fldCondName,
fldCondVar, fldCondLbr From " & TABLE_NAME & " Where *condition*
ErrState: 3122 (You tried to execute a query that does not include
the specified expression 'fldTypeName' as part of an aggregate
function.)
You haven't told it what batch of values to count.
based on previous error message i tried to include the field name
in the count function ... wild guessing obviously :-)
sSql = "Select Count (fldTypeName) AS TotalCount,
fldTypeName,
fldCondName, fldCondVar, fldCondLbr From " & TABLE_NAME & " Where
*condition*
ErrState: 3122 (You tried to execute a query that does not include
the specified expression 'fldTypeName' as part of an aggregate
function.)
Gotta have a GROUP BY in this case. The only time Count(*) works
without it is if you have no fields returned except the Count(*).
so i thought maybe i needed all the field names (even though it
said i didn't use the one I did use)
sSql = "Select Count (fldTypeName, fldCondName, fldCondVar,
fldCondLbr) AS TotalCount, fldTypeName, fldCondName, fldCondVar,
fldCondLbr From " & TABLE_NAME & " Where *condition*
ErrState: 3075 (Wrong number of arguments used with function in
query expression 'Count (fldTypeName, fldCondName, fldCondVar,
fldCondLbr)'.)
Count() takes a valid expression that evaluates to a single value,
and the comma-separated list of values doesn't. You could
concatenate them, but that really wouldn't get you what you want, as
it would be counting the same thing you'd get with a SELECT DISTINCT
on those fields, rather than a count of the rows with certain values
specified in your WHERE clause.
so at this point i have to admit I don't know how to use the
Count() function to get both a count and some records at the same
time

now i'm looping through twice, once to get the count and a second
time to read the records...that works but seems redundant...(and
it did confirm that the count was returning correctly)
Take what you've got and add the GROUP BY clause and it should work.

To do a crosstab, it would be like this:

TRANSFORM Count(*) AS RecordCount
SELECT f1, f2, f3
FROM tblInventory
GROUP BY f1, f2, f3
PIVOT f4;

This will give you the date in the first three columns followed by a
number of columns that will be as many as there are unique values in
f4. You may want to Pivot on an expression, though, such as
Right(f4,1). Yes, just tested and you could do:

TRANSFORM Count(*) AS RecordCount
SELECT f1, f2, f3
FROM tblInventory
GROUP BY f1, f2, f3
PIVOT Right(f4,1);

Or maybe Mid(2,1).

I'm pretty sure those will work via DAO outside of Access, but
you'll have to test. I know that Left() and Right() work, as well as
IIf(), but not Nz(). This is because, apparently, Nz() is an Access
function, while the others are built into Jet at some level.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Oct 26 '06 #13

P: n/a
MP
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...

snip
I still think the self-join would work better.
I'm still studying that
I dusted off all my notes I've collected on joins from various net souces to
try and learn how to use them
so far I just have a really big headache :-) but eventually I'll get it
figured out.
or do I need to loop twice, once to get the count and a second
time to get the fields to read?

There's nothing to loop, because you aren't returning the data in
the SQL above.
What I mean by loop twice was ( and loop was a bad term to use)
1)
"Select Count (*) From " & TABLE_NAME & " Where
now i have the count
2)
"Select F1, F2 etc From " & TABLE_NAME & " Where
now i have the data

execute twice would have been a better term than loop...
You could open a recordset to get the count. It will be very fast.
It's faster than opening the full recordset and moving to the last
record to get it from .RecordCount (it's not guaranteed to be
accurate until you move to the last record in the recordset). So the
SELECT Count(*) is going to be the best way to get the count. And it
will be very fast. You'll want to do something like this:

SELECT f1, f2, f3, Count(*) AS RecordCount
FROM table1
WHERE f4 Like "?R"
GROUP BY f1, f2, f3
yes that's what i was trying to do!!! now you have the count and the data in
one set right?
the part i was missing was the Group by which I didn't realize was what was
throwing the error..
as verbose and helpful as ado/dao error messages are I'm surprised they
didn't say 'Hey dummy, you need a group by here..."
:-)
Set rst2 = db.OpenRecordset(sSql, dbOpenForwardOnly,
dbReadOnly)
Forward-type recordsets are not faster.
i only used that because it's the only sample of dao code i found in my
"archives" of research from the net
not knowing anything about dao I just copied it and tweaked with my field
names and voila it worked!
amazing :-)
Since you're looping and re-opening the recordset, move the Set rst2
= Nothing outside your loop.
absolutely, that was just in there for redundant 'cleaning' in that quickie
sample

--------------------------
I'd check that each of this was Not Nothing before closing them. If
they aren't open you'll get an error.
dao does not appear to have a .State on recordset and connection objects
(I dont' find them in object browser or intellisense any way)

in ado to cleanup
If Rs.State = adStateOpen Then
Rs.Close
Set Rs = Nothing
End if

so in dao if you do Rs.Close...does that set Rs to nothing?
if not then why would "If Rs Is Nothing" ....be a way to see if it's closed?
--------------------------
Right. You need a GROUP BY clause that includes all the
non-aggregate function fields in it, i.e:
GROUP BY fldTypeName, fldCondName, fldCondVar, fldCondLbr
AH HA!!!
Thats' the secret ingregient I was missing!
Thanks for that ... it was driving me crazy! (not hard)
<g>

You haven't told it what batch of values to count.
I thought the Where did that
I thought Count(*) meant count every record (thinking of * as wild card for
'match all') matching the WHERE condition

you don't happen to have a situs where Count() is explained in detail how it
works??? I find lots of simple examples but not a detailed explanation on
any of this yet, thus my inability to figure out on my own that it was the
Group By that I was missing....I would never have found that without your
explanation!
I have a thousand examples like

SELECT Count(*) AS [Number of Invoices]
FROM tblInvoices

it shows no group by (but it had no Where clause to require one...but I
didn't know that without previous knowlege)

If I read between the lines correctly you're saying if Count includes a
Where it must also include a Group By...is that correct assumption?

To do a crosstab, it would be like this:

TRANSFORM Count(*) AS RecordCount
SELECT f1, f2, f3
FROM tblInventory
GROUP BY f1, f2, f3
PIVOT f4;

This will give you the date in the first three columns followed by a
number of columns that will be as many as there are unique values in
f4. You may want to Pivot on an expression, though, such as
Right(f4,1). Yes, just tested and you could do:

TRANSFORM Count(*) AS RecordCount
SELECT f1, f2, f3
FROM tblInventory
GROUP BY f1, f2, f3
PIVOT Right(f4,1);

Or maybe Mid(2,1).

I'm pretty sure those will work via DAO outside of Access, but
you'll have to test. I know that Left() and Right() work, as well as
IIf(), but not Nz(). This is because, apparently, Nz() is an Access
function, while the others are built into Jet at some level.
I'm going to study those inner / outer / transform / joins and see if I can
grok them
Unfortunately at my advanced age i'm quite a slow learner (he he
he...actually I was at any age <g>)

I'm thinkingsomething like
[ Select F5 Inner Join (or outer?)
[ Select Distinct F4 InnerJoin (or outer?)
[Select Distinct F1, F2, F3 as 3Field]
on 3field.something = 4Field.something Where 4Conditions] as 4field
On 4Field.something = 5Field.something Where 5conditions] As 5Field

but obviously i'm very confused at this point....need more time to
understand how the joins work

any sites with tutorials on the Transform that would go in depth would also
be appreciated if you know of any.
I have the JetSql 1,2,and 3 articles... I think from msdn... but they're
pretty thin on detail...I've read them over many times and am still
struggling to understand how to work the joins into my situation....i'm
pretty sure that's what i need to do though
Fundamental Microsoft Jet SQL for Access 2000 by Acey, James Bunch

Thanks for your unflagging perseverance!

one thing i'm seeing in reading about joins is that ON seems synonymous with
WHERE
Join x ON tbl1.F1 = tbl2.F1
Where tbl1.F2 = tbl2.F2

i don't understand the difference between the ON and the where
looks like you'd get the same from
Join x ON tbl1.F2 = tbl2.F2
Where tbl1.F1 = tbl2.F1

but as i say i'm really in the dark on this and need time to absorb it more
or find more sources with indepth explanations i can understand
Mark

Oct 26 '06 #14

P: n/a
"MP" <no****@Thanks.comwrote in
news:Nr*******************@tornado.rdc-kc.rr.com:
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
[]
>You could open a recordset to get the count. It will be very
fast. It's faster than opening the full recordset and moving to
the last record to get it from .RecordCount (it's not guaranteed
to be accurate until you move to the last record in the
recordset). So the SELECT Count(*) is going to be the best way to
get the count. And it will be very fast. You'll want to do
something like this:

SELECT f1, f2, f3, Count(*) AS RecordCount
FROM table1
WHERE f4 Like "?R"
GROUP BY f1, f2, f3

yes that's what i was trying to do!!! now you have the count and
the data in one set right?
the part i was missing was the Group by which I didn't realize was
what was throwing the error..
as verbose and helpful as ado/dao error messages are I'm surprised
they didn't say 'Hey dummy, you need a group by here..."
Well, it's not the job of the DAO/ADO error messages to teach you
SQL.
Set rst2 = db.OpenRecordset(sSql, dbOpenForwardOnly,
dbReadOnly)
Forward-type recordsets are not faster.

i only used that because it's the only sample of dao code i found
in my "archives" of research from the net
not knowing anything about dao I just copied it and tweaked with
my field names and voila it worked!
amazing :-)
You can leave of the last two arguments, which are both optional.
They default to Dynaset and Editable, if I'm not mistaken. If you're
concerned about the latter, you could keep the read-only flag and
just have nothing for the second parameter.

[]
>I'd check that each of this was Not Nothing before closing them.
If they aren't open you'll get an error.

dao does not appear to have a .State on recordset and connection
objects (I dont' find them in object browser or intellisense any
way)
Nope.
in ado to cleanup
If Rs.State = adStateOpen Then
Rs.Close
Set Rs = Nothing
End if

so in dao if you do Rs.Close...does that set Rs to nothing?
No. That only clears the structure in memory that the variable is
pointing to. Setting the variable to Nothing then clears the
variable, too. The reason you have to do both is because VB(A) uses
reference counts and it sometimes doesn't keep good track. That is,
until you set the variable to Nothing, it may not release the memory
used by the recordset, even when you explicitly close it.

Checking for Not Nothing works in all cases except a very strange
one with database variables initialized via CurrentDB().
if not then why would "If Rs Is Nothing" ....be a way to see if
it's closed?
It works. With the exception of the one circumstance where a
database variable can be Not Nothing but the corresponding data
structure not be there, I've found no problems (and that only with
the currently open db), and certainly not with recordsets.

[]
>You haven't told it what batch of values to count.

I thought the Where did that
I thought Count(*) meant count every record (thinking of * as wild
card for 'match all') matching the WHERE condition
Yes, but you've got to tell it what unique records to count. That's
what the GROUP BY does.
you don't happen to have a situs where Count() is explained in
detail how it works??? I find lots of simple examples but not a
detailed explanation on any of this yet, thus my inability to
figure out on my own that it was the Group By that I was
missing....I would never have found that without your explanation!
There's a major Jet SQL article on MSDN. It basically replicates
what's in the Access help files.
I have a thousand examples like

SELECT Count(*) AS [Number of Invoices]
FROM tblInvoices

it shows no group by (but it had no Where clause to require
one...but I didn't know that without previous knowlege)
It's not the WHERE clause that forces the GROUP BY. It's having
fields other than the COUNT() as part of the SELECT statement.
If I read between the lines correctly you're saying if Count
includes a Where it must also include a Group By...is that correct
assumption?
You trailed off there, but if you're displaying fields and you want
to count the values that match the fields, you have to have a GROUP
BY.
>To do a crosstab, it would be like this:

TRANSFORM Count(*) AS RecordCount
SELECT f1, f2, f3
FROM tblInventory
GROUP BY f1, f2, f3
PIVOT f4;

This will give you the date in the first three columns followed
by a number of columns that will be as many as there are unique
values in f4. You may want to Pivot on an expression, though,
such as Right(f4,1). Yes, just tested and you could do:

TRANSFORM Count(*) AS RecordCount
SELECT f1, f2, f3
FROM tblInventory
GROUP BY f1, f2, f3
PIVOT Right(f4,1);

Or maybe Mid(2,1).

I'm pretty sure those will work via DAO outside of Access, but
you'll have to test. I know that Left() and Right() work, as well
as IIf(), but not Nz(). This is because, apparently, Nz() is an
Access function, while the others are built into Jet at some
level.

I'm going to study those inner / outer / transform / joins and see
if I can grok them
Unfortunately at my advanced age i'm quite a slow learner (he he
he...actually I was at any age <g>)

I'm thinkingsomething like
[ Select F5 Inner Join (or outer?)
[ Select Distinct F4 InnerJoin (or outer?)
[Select Distinct F1, F2, F3 as 3Field]
on 3field.something = 4Field.something Where 4Conditions] as
4field
On 4Field.something = 5Field.something Where 5conditions] As
5Field

but obviously i'm very confused at this point....need more time to
understand how the joins work

any sites with tutorials on the Transform that would go in depth
would also be appreciated if you know of any.
I have the JetSql 1,2,and 3 articles... I think from msdn... but
they're pretty thin on detail...I've read them over many times and
am still struggling to understand how to work the joins into my
situation....i'm pretty sure that's what i need to do though
Fundamental Microsoft Jet SQL for Access 2000 by Acey, James Bunch
This is where having Access makes life *much* easier. You can do so
many things in the Access QBE grid and then look at the SQL it
generates to understand how it works.
Thanks for your unflagging perseverance!

one thing i'm seeing in reading about joins is that ON seems
synonymous with WHERE
Join x ON tbl1.F1 = tbl2.F1
Where tbl1.F2 = tbl2.F2
Yes, and in my testing, Jet optimizes joins and WHERE clauses
identically, so there's no performance penalty for using the WHERE
clause instead of the join.
i don't understand the difference between the ON and the where
looks like you'd get the same from
Join x ON tbl1.F2 = tbl2.F2
Where tbl1.F1 = tbl2.F1

but as i say i'm really in the dark on this and need time to
absorb it more or find more sources with indepth explanations i
can understand
The problem is that I'm not sure it's entirely possible to replicate
an outer join, where you return both values from the other table and
the Nulls for records that are not matched in the other table.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Oct 26 '06 #15

P: n/a
MP
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...

I'm surprised
they didn't say 'Hey dummy, you need a group by here..."
Well, it's not the job of the DAO/ADO error messages to teach you
SQL.
<VBG>
There's a major Jet SQL article on MSDN. It basically replicates
what's in the Access help files.
pretty sure that's the one i have and have read many times .... still trying
to absorb it

>You haven't told it what batch of values to count.

I thought the Where did that
I thought Count(*) meant count every record (thinking of * as wild
card for 'match all') matching the WHERE condition
>Yes, but you've got to tell it what unique records to count. That's
what the GROUP BY does.

very confusing to me
I would think the Where told it what to count and the Group by told it how
to sort the displayed results

that's what I get for thinking ;-)

Oct 26 '06 #16

P: n/a
"MP" <no****@Thanks.comwrote in
news:Nr*******************@tornado.rdc-kc.rr.com:
in ado to cleanup
If Rs.State = adStateOpen Then
Rs.Close
Set Rs = Nothing
End if
IMO, this is entirely superfluous.

Please, post an example of a situation where omitting this code caused a
problem.

--
Lyle Fairfield
Oct 26 '06 #17

P: n/a
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in
news:Xn**********************************@127.0.0. 1:
>dao does not appear to have a .State on recordset and connection
objects (I dont' find them in object browser or intellisense any
way)

Nope.
>in ado to cleanup
If Rs.State = adStateOpen Then
Rs.Close
Set Rs = Nothing
End if

so in dao if you do Rs.Close...does that set Rs to nothing?

No. That only clears the structure in memory that the variable is
pointing to. Setting the variable to Nothing then clears the
variable, too. The reason you have to do both is because VB(A) uses
reference counts and it sometimes doesn't keep good track. That is,
until you set the variable to Nothing, it may not release the memory
used by the recordset, even when you explicitly close it.

Checking for Not Nothing works in all cases except a very strange
one with database variables initialized via CurrentDB().
>if not then why would "If Rs Is Nothing" ....be a way to see if
it's closed?

It works. With the exception of the one circumstance where a
database variable can be Not Nothing but the corresponding data
structure not be there, I've found no problems (and that only with
the currently open db), and certainly not with recordsets.
The ADO Defense Counsel rests, your Honour.

--
Lyle Fairfield
Oct 26 '06 #18

P: n/a
MP
"Lyle Fairfield" <ly***********@aim.comwrote in message
news:Xn*********************************@216.221.8 1.119...
"MP" <no****@Thanks.comwrote in
news:Nr*******************@tornado.rdc-kc.rr.com:
in ado to cleanup
If Rs.State = adStateOpen Then
Rs.Close
Set Rs = Nothing
End if

IMO, this is entirely superfluous.

Please, post an example of a situation where omitting this code caused a
problem.

--
Lyle Fairfield
I don't have such an example.
by now it should be obvious I'm a rank beginner. <g>
I thought that was how it was done since that's what i've seen in my
research into learning ado

for example...copied from some newsgroup posting
From: "Veign" <NO***********@veign.com>
Subject: Re: ADOX Columns
Date: Thursday, August 19, 2004 9:30 AM
<snip>

'Cleanup
If (RS.State And adStateOpen) = adStateOpen Then RS.Close
Set RS = Nothing

If (CNN.State And adStateOpen) = adStateOpen Then CNN.Close
Set CNN = Nothing

coming from that source (Chris Hanscom, MVP http://www.veign.com)
i had no reason to question the viability of the method

I assumed that if a recordset isn't open rs.close would throw an
error...haven't tested that just seemed logical
so i thought what i showed was the ultra safe clean way to dispose of an
object

it may be superfluous but is it harmful? wrong? computationally expensive?
time consuming?

Oct 26 '06 #19

P: n/a
"MP" <no****@Thanks.comwrote in
news:lK*******************@tornado.rdc-kc.rr.com:
"Lyle Fairfield" <ly***********@aim.comwrote in message
news:Xn*********************************@216.221.8 1.119...
>"MP" <no****@Thanks.comwrote in
news:Nr*******************@tornado.rdc-kc.rr.com:
in ado to cleanup
If Rs.State = adStateOpen Then
Rs.Close
Set Rs = Nothing
End if

IMO, this is entirely superfluous.

Please, post an example of a situation where omitting this code
caused a problem.

--
Lyle Fairfield

I don't have such an example.
by now it should be obvious I'm a rank beginner. <g>
I thought that was how it was done since that's what i've seen in my
research into learning ado

for example...copied from some newsgroup posting
From: "Veign" <NO***********@veign.com>
Subject: Re: ADOX Columns
Date: Thursday, August 19, 2004 9:30 AM
<snip>

'Cleanup
If (RS.State And adStateOpen) = adStateOpen Then RS.Close
Set RS = Nothing

If (CNN.State And adStateOpen) = adStateOpen Then CNN.Close
Set CNN = Nothing

coming from that source (Chris Hanscom, MVP http://www.veign.com)
i had no reason to question the viability of the method

I assumed that if a recordset isn't open rs.close would throw an
error...haven't tested that just seemed logical
so i thought what i showed was the ultra safe clean way to dispose of
an object

it may be superfluous but is it harmful? wrong? computationally
expensive? time consuming?
MVPs are often not the sharpest knives in the dishwasher.

If one includes this, why not set all your numeric variables to zero amd
your strings to a zero length?

And whatcha going to do when using ADO in a language/script/technology
which does not have a "Nothing" (nor a "Set" either?)

DAO's problems are not reasons for determining how ADO is coded.

But don't think I'm anti-DAO. DAO was good enough for my father, and my
grandfather too, and it's good enough for me.

--
Lyle Fairfield
Oct 26 '06 #20

P: n/a
"MP" <no****@Thanks.comwrote in
news:Eg*******************@tornado.rdc-kc.rr.com:
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
[]
>>You haven't told it what batch of values to count.

I thought the Where did that
I thought Count(*) meant count every record (thinking of * as
wild card for 'match all') matching the WHERE condition
>>Yes, but you've got to tell it what unique records to count.
That's
what the GROUP BY does.

very confusing to me
I would think the Where told it what to count and the Group by
told it how to sort the displayed results
The WHERE tells it which records to summarize.

The GROUP BY tells what values to group on to get the summary, in
your case, a count of the records for each group.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Oct 27 '06 #21

P: n/a
"MP" <no****@Thanks.comwrote in
news:lK*******************@tornado.rdc-kc.rr.com:
"Lyle Fairfield" <ly***********@aim.comwrote in message
news:Xn*********************************@216.221.8 1.119...
>"MP" <no****@Thanks.comwrote in
news:Nr*******************@tornado.rdc-kc.rr.com:
in ado to cleanup
If Rs.State = adStateOpen Then
Rs.Close
Set Rs = Nothing
End if

IMO, this is entirely superfluous.

Please, post an example of a situation where omitting this code
caused a problem.

I don't have such an example.
by now it should be obvious I'm a rank beginner. <g>
I thought that was how it was done since that's what i've seen in
my research into learning ado
Microsoft, Michael Kaplan and Lyle all say ADO when used with VB(A)
doesn't have the memory leaks that DAO does.

But why take the risk that you'll run into an undiscovered memory
leak?

I think many people who were accustomed to DAO kept the practice of
closing and clearing their object variables when they started coding
ADO, even though it may not be necessary.

But it's not *supposed* to be necessary in VBA with DAO, either, but
if you don't do it you run into problems.

[]
I assumed that if a recordset isn't open rs.close would throw an
error...haven't tested that just seemed logical
so i thought what i showed was the ultra safe clean way to dispose
of an object
Yes, in DAO it will throw an error (dunno about ADO).
it may be superfluous but is it harmful? wrong? computationally
expensive? time consuming?
I wouldn't say so. I say it's a belt and suspenders type of
situation.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Oct 27 '06 #22

P: n/a
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in
news:Xn**********************************@127.0.0. 1:
I wouldn't say so. I say it's a belt and suspenders type of
situation.
Yep, it's a GrandDad kinda thing, right up under your armpits!

--
Lyle Fairfield
Oct 27 '06 #23

This discussion thread is closed

Replies have been disabled for this discussion.