ca1358
asked on
Select Query using Access VBA
Trying to write a select query to count a recordset.
I include my test code.
I include my test code.
Dim strSQL As String
strSQL = "SELECT Count(*) AS [Count] " & _
"FROM Table1;"
DoCmd.RunSQL strSQL
What is the problem?
ASKER
My version
Runtime error 2342 a RunSQL action requires an argument consisting of SQL statement.
using this this
queryName = "Select * From Table1"
strSQL = DCount("*", queryName)
I am getting an MS Access database engine cannot find the input table or query "Select * From Table1" Make sure it exist and spelled correctly.
If I run the query by using Access query design, it works.
This is the SQL view
SELECT Count(*) AS [Count]
FROM Table1;
But I need to put this into VBA code.
Runtime error 2342 a RunSQL action requires an argument consisting of SQL statement.
using this this
queryName = "Select * From Table1"
strSQL = DCount("*", queryName)
I am getting an MS Access database engine cannot find the input table or query "Select * From Table1" Make sure it exist and spelled correctly.
If I run the query by using Access query design, it works.
This is the SQL view
SELECT Count(*) AS [Count]
FROM Table1;
But I need to put this into VBA code.
You sure that the table name is really TABLE1? Maybe you need to change it to the correct table name.
just use dcount
dim xCount
xCount=dcount("*","table1"
dim xCount
xCount=dcount("*","table1"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
gemarti is mostly right about using DCOUNT(). I would normally assign the result of DCOUNT() to a Long Integer variable, not a string variable. Also, note that strSQL implies that it contains a query string, when it is actually being used to contain the results of the DCOUNT() function. This is probably the second-most simple answer.
If you are only looking for a count of records in a table (your posted example), then you can get that directly from the tabledef object.
Example:
msgbox "the number of table1 records is " & currentdb.tabledefs("table
Instead of DoCmd.RunSQL, you should instantiate a recordset variable to receive the results of the Select Count query.
Example:
If you are only looking for a count of records in a table (your posted example), then you can get that directly from the tabledef object.
Example:
msgbox "the number of table1 records is " & currentdb.tabledefs("table
Instead of DoCmd.RunSQL, you should instantiate a recordset variable to receive the results of the Select Count query.
Example:
Dim rs As Recordset
Set rs = CurrentDb.Openrecordset("SELECT Count(*) AS [Count] FROM Table1;"
)
msgbox "the number of table1 records is " & rs![Count]ASKER
Thank you.
strSQL = DCount("*", queryName)