Evlich
asked on
Queries that return values from Access using DAO 3.6
Hello, I am in VB and using the DAO 3.6 and I have a database that I want to run queries that return things other than recordsets. For instance, I have this:
Dim i As Integer
i = DB.Execute("SELECT COUNT (pID) FROM students;")
I want i to be the number of records in student. Is there a way that I can do this so that I don't have to open a recorset and then use rs.Count? Thanks a lot.
~evlich
Dim i As Integer
i = DB.Execute("SELECT COUNT (pID) FROM students;")
I want i to be the number of records in student. Is there a way that I can do this so that I don't have to open a recorset and then use rs.Count? Thanks a lot.
~evlich
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I though DB.Execute does not return a value.
~evlich
~evlich
It doesn't return a value but a recordset, using .Fields(0).Value at the end will not save this recordset to an object but return the value of the first field in the recordset by holding the recordset in memory but never actually committing it to an object variable. It doesn't change the behaviour of the execute method but extends it as you would normally.
thate does in one line, hat my code did in two, bit the oe line will be harder to debug, and less flexible. The choice is up to you.
'select count(pID) as fieldname from tablename' will return a recordset with one field called fieldname from the database.
barendb---that is EXACTLY what both TimCoffee and I have already stated.
Oops, sorry, was a bit to quick for myself there. Didn't read properly.
ASKER
Thanks a lot, sorry that it took so long. I have been strapped for time hand have not been able to test it.
~evlich
~evlich
Dim i As Integer
i = DB.Execute("SELECT COUNT (pID) FROM students;").Fields(0).Valu
This uses the implicit fields collection of the returned recordset and returns the value of the first (in this case only) field.