Select count(*) does not work from code

  • Thread starter Thread starter JA
  • Start date Start date
J

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
 
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
 
Yes, I tried, but no error is occurring, it just returns 0 for recordcount,
but when I open the query manually, records are returned.

Michel Walsh said:
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


JA said:
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
Click to expand...
Click to expand...
 
Roger Carlson said:
What's different between the tables where it works and those that it
doesn't?

--
Click to expand...

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.
 
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



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

Michel Walsh said:
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


JA said:
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
Click to expand...
Click to expand...
Click to expand...
 
That returned the following error:

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


Michel Walsh said:
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



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

Michel Walsh said:
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
Click to expand...
Click to expand...
Click to expand...
 
Does anybody have a better method to return the number of records a query
will return when run?
 
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



JA said:
That returned the following error:

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


Michel Walsh said:
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



JA said:
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
Click to expand...
Click to expand...
Click to expand...
 
Docount was the answer I needed, thanks!

Michel Walsh said:
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



JA said:
That returned the following error:

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


Michel Walsh said:
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
Click to expand...
Click to expand...
Click to expand...
 
(resending, as my original response hasn't appeared)

JA said:
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
Click to expand...

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.
 
Back
Top