Select count(*) does not work from code

[JA]

I am running the code below on a closed query to determine the count of
records. It works on some, but not all queries. If I manually open the
query records are returned, but the function returns 0 on some queries.

Function GetCount2(sTbl As String) As Long
Dim strSql As String
strSql = "SELECT COUNT(*) FROM " & sTbl
GetCount2 = CurrentProject.Connection.Execute(strSql).Fields(0)
End Function

 

[Michel Walsh]

Maybe it is because of the table/query name + error trapping 'on error
resume next'. Have you tried:



strSql = "SELECT COUNT(*) FROM [" & sTbl & "]"
GetCount2 = CurrentProject.Connection.Execute(strSql).Fields(0).Value
Debug.Assert Err.Number = 0



Vanderghast, Access MVP

 

[Roger Carlson]

What's different between the tables where it works and those that it
doesn't?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[JA]

Yes, I tried, but no error is occurring, it just returns 0 for recordcount,
but when I open the query manually, records are returned.

Maybe it is because of the table/query name + error trapping 'on error
resume next'. Have you tried:



strSql = "SELECT COUNT(*) FROM [" & sTbl & "]"
GetCount2 = CurrentProject.Connection.Execute(strSql).Fields(0).Value
Debug.Assert Err.Number = 0



Vanderghast, Access MVP

I am running the code below on a closed query to determine the count of
records. It works on some, but not all queries. If I manually open the
query records are returned, but the function returns 0 on some queries.

Function GetCount2(sTbl As String) As Long
Dim strSql As String
strSql = "SELECT COUNT(*) FROM " & sTbl
GetCount2 = CurrentProject.Connection.Execute(strSql).Fields(0)
End Function

 

[JA]

What's different between the tables where it works and those that it
doesn't?

--

I am attempting to determine how many records a query will return when it is
run. I am actually running the code against a query in Access.

 

[Michel Walsh]

If this is not a matter of disconnected data or something similar, if it is
standard native data in Access, I highly suspect that GetCount2 is never
assigned and just returns its initial value of 0 it implicitly got when the
function stated; in other words, I highly suspect error trapping. Can you
disable it, and add something like:



Function GetCount2(sTbl As String) As Long
Dim strSql As String
ON ERROR GOTO 0 ' <----
strSql = "SELECT COUNT(*) FROM " & sTbl
GetCount2 = CurrentProject.Connection.Execute(strSql).Fields(0).Value

strSql="SELECT * FROM " & sTbl ' <----
Debug.Print CurrentProject.Connection.Execute(strSql).Fields(0).Value
'<---

End Function



and try it with a sTbl which creates a problem (while having records in it).
The second execute should pump the value of the first field, of the first
record, if there is at least one record.




Vanderghast, Access MVP

Yes, I tried, but no error is occurring, it just returns 0 for
recordcount, but when I open the query manually, records are returned.

Maybe it is because of the table/query name + error trapping 'on error
resume next'. Have you tried:



strSql = "SELECT COUNT(*) FROM [" & sTbl & "]"
GetCount2 = CurrentProject.Connection.Execute(strSql).Fields(0).Value
Debug.Assert Err.Number = 0



Vanderghast, Access MVP

I am running the code below on a closed query to determine the count of
records. It works on some, but not all queries. If I manually open the
query records are returned, but the function returns 0 on some queries.

Function GetCount2(sTbl As String) As Long
Dim strSql As String
strSql = "SELECT COUNT(*) FROM " & sTbl
GetCount2 = CurrentProject.Connection.Execute(strSql).Fields(0)
End Function

 

[JA]

That returned the following error:

Either BOF or EOF is True, or the current record has been deleted. Requested
operation requires a current record.

If this is not a matter of disconnected data or something similar, if it
is standard native data in Access, I highly suspect that GetCount2 is
never assigned and just returns its initial value of 0 it implicitly got
when the function stated; in other words, I highly suspect error trapping.
Can you disable it, and add something like:



Function GetCount2(sTbl As String) As Long
Dim strSql As String
ON ERROR GOTO 0 ' <----
strSql = "SELECT COUNT(*) FROM " & sTbl
GetCount2 = CurrentProject.Connection.Execute(strSql).Fields(0).Value

strSql="SELECT * FROM " & sTbl ' <----
Debug.Print CurrentProject.Connection.Execute(strSql).Fields(0).Value
'<---

End Function



and try it with a sTbl which creates a problem (while having records in
it). The second execute should pump the value of the first field, of the
first record, if there is at least one record.




Vanderghast, Access MVP

Yes, I tried, but no error is occurring, it just returns 0 for
recordcount, but when I open the query manually, records are returned.

Maybe it is because of the table/query name + error trapping 'on error
resume next'. Have you tried:



strSql = "SELECT COUNT(*) FROM [" & sTbl & "]"
GetCount2 = CurrentProject.Connection.Execute(strSql).Fields(0).Value
Debug.Assert Err.Number = 0



Vanderghast, Access MVP


I am running the code below on a closed query to determine the count of
records. It works on some, but not all queries. If I manually open the
query records are returned, but the function returns 0 on some queries.

Function GetCount2(sTbl As String) As Long
Dim strSql As String
strSql = "SELECT COUNT(*) FROM " & sTbl
GetCount2 = CurrentProject.Connection.Execute(strSql).Fields(0)
End Function

 

[JA]

Does anybody have a better method to return the number of records a query
will return when run?

 

[Douglas J. Steele]

Anything wrong with using the DCount function?

 

[JA]

Anything wrong with using the DCount function?

--

That worked, thanks!

 

[Michel Walsh]

So indeed, the connection does not see any record being returned by sTbl.


If sTbl is a saved query name, or a table name, can you try:

? DCount("*", sTbl)


(That expression cannot be used if sTbl is something like " table1 WHERE
field1=444", ie, something else that a table name or saved query name ).


If DCount does not return 0, and if the CurrentProject (ADO) is the same
database that CurrentDb (DAO), then you have a serious bug. Maybe a
corrupted database.

If DCount also returns 0, then there is no record under sTbl, ADO and DAO
both agree on that. Instead of opening the query, can you paste the content
of sTbl in the RowSource property of a ListBox control in a form to see if
the list box display something. If it also display nothing, clearly, what
sTbl hold is a table or a query returning nothing. Can you then describe
what you do to manually open the query? maybe there is a typo of some sort?


Vanderghast, Access MVP

That returned the following error:

Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record.

If this is not a matter of disconnected data or something similar, if it
is standard native data in Access, I highly suspect that GetCount2 is
never assigned and just returns its initial value of 0 it implicitly got
when the function stated; in other words, I highly suspect error
trapping. Can you disable it, and add something like:



Function GetCount2(sTbl As String) As Long
Dim strSql As String
ON ERROR GOTO 0 ' <----
strSql = "SELECT COUNT(*) FROM " & sTbl
GetCount2 = CurrentProject.Connection.Execute(strSql).Fields(0).Value

strSql="SELECT * FROM " & sTbl ' <----
Debug.Print CurrentProject.Connection.Execute(strSql).Fields(0).Value
'<---

End Function



and try it with a sTbl which creates a problem (while having records in
it). The second execute should pump the value of the first field, of the
first record, if there is at least one record.




Vanderghast, Access MVP

Yes, I tried, but no error is occurring, it just returns 0 for
recordcount, but when I open the query manually, records are returned.

Maybe it is because of the table/query name + error trapping 'on error
resume next'. Have you tried:



strSql = "SELECT COUNT(*) FROM [" & sTbl & "]"
GetCount2 = CurrentProject.Connection.Execute(strSql).Fields(0).Value
Debug.Assert Err.Number = 0



Vanderghast, Access MVP


I am running the code below on a closed query to determine the count of
records. It works on some, but not all queries. If I manually open
the query records are returned, but the function returns 0 on some
queries.

Function GetCount2(sTbl As String) As Long
Dim strSql As String
strSql = "SELECT COUNT(*) FROM " & sTbl
GetCount2 = CurrentProject.Connection.Execute(strSql).Fields(0)
End Function

 

[JA]

Docount was the answer I needed, thanks!

So indeed, the connection does not see any record being returned by sTbl.


If sTbl is a saved query name, or a table name, can you try:

? DCount("*", sTbl)


(That expression cannot be used if sTbl is something like " table1 WHERE
field1=444", ie, something else that a table name or saved query name ).


If DCount does not return 0, and if the CurrentProject (ADO) is the same
database that CurrentDb (DAO), then you have a serious bug. Maybe a
corrupted database.

If DCount also returns 0, then there is no record under sTbl, ADO and
DAO both agree on that. Instead of opening the query, can you paste the
content of sTbl in the RowSource property of a ListBox control in a form
to see if the list box display something. If it also display nothing,
clearly, what sTbl hold is a table or a query returning nothing. Can you
then describe what you do to manually open the query? maybe there is a
typo of some sort?


Vanderghast, Access MVP

That returned the following error:

Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record.

If this is not a matter of disconnected data or something similar, if it
is standard native data in Access, I highly suspect that GetCount2 is
never assigned and just returns its initial value of 0 it implicitly got
when the function stated; in other words, I highly suspect error
trapping. Can you disable it, and add something like:



Function GetCount2(sTbl As String) As Long
Dim strSql As String
ON ERROR GOTO 0 ' <----
strSql = "SELECT COUNT(*) FROM " & sTbl
GetCount2 = CurrentProject.Connection.Execute(strSql).Fields(0).Value

strSql="SELECT * FROM " & sTbl ' <----
Debug.Print
CurrentProject.Connection.Execute(strSql).Fields(0).Value '<---

End Function



and try it with a sTbl which creates a problem (while having records in
it). The second execute should pump the value of the first field, of the
first record, if there is at least one record.




Vanderghast, Access MVP



Yes, I tried, but no error is occurring, it just returns 0 for
recordcount, but when I open the query manually, records are returned.

Maybe it is because of the table/query name + error trapping 'on error
resume next'. Have you tried:



strSql = "SELECT COUNT(*) FROM [" & sTbl & "]"
GetCount2 =
CurrentProject.Connection.Execute(strSql).Fields(0).Value
Debug.Assert Err.Number = 0



Vanderghast, Access MVP


I am running the code below on a closed query to determine the count
of records. It works on some, but not all queries. If I manually
open the query records are returned, but the function returns 0 on
some queries.

Function GetCount2(sTbl As String) As Long
Dim strSql As String
strSql = "SELECT COUNT(*) FROM " & sTbl
GetCount2 = CurrentProject.Connection.Execute(strSql).Fields(0)
End Function

 

[Dirk Goldgar]

(resending, as my original response hasn't appeared)

I am running the code below on a closed query to determine the count of
records. It works on some, but not all queries. If I manually open the
query records are returned, but the function returns 0 on some queries.

Function GetCount2(sTbl As String) As Long
Dim strSql As String
strSql = "SELECT COUNT(*) FROM " & sTbl
GetCount2 = CurrentProject.Connection.Execute(strSql).Fields(0)
End Function

My guess would be that the queries where it doesn't work are those that use
the Like operator. You're opening your recordset using ADO, and ADO uses
ANSI wild-card characters "%" and "_" where DAO and Access, by default, use
the JET wild-card characters "*" and "?". So if, for example, you have a
table like this:

Table MyTable
---------------------
Field MyField (text)

MyField
------------------------
Alfred
Allison
Beth
John

and a stored query with SQL like this:

SELECT * FROM MyTable WHERE MyField Like "'A*";

.... that query will return records

Alfred
Allison

when opened by Access or using DAO, but will rerun no records when opened
using DAO.

You could rewrite your function to use DAO, like this:

'----- start of code -----
Function GetCountDAO(sTbl As String) As Long

Dim strSql As String

strSql = "SELECT COUNT(*) FROM " & sTbl

With CurrentDb.OpenRecordset(strSQL)
GetCountDAO = .Fields(0)
.Close
End With

End Function
'----- end of code -----

Or you could rewrite the queries to use the not-very-well-documented ALike
operator along with the ANSI wild-card characters. Or, of course, you could
just use the DCount function, as others have already suggested.