• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

Select Query using Access VBA

Trying to write a select query to count a recordset.

I include my test code.
Dim strSQL As String

strSQL = "SELECT Count(*) AS [Count] " & _
"FROM Table1;"
DoCmd.RunSQL strSQL

Open in new window

0
ca1358
Asked:
ca1358
  • 2
  • 2
  • 2
  • +2
1 Solution
 
gemartiCommented:
queryName = "Select * From Table1"

strSQL = DCount("*", queryName)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What is the problem?
0
 
ca1358Author Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You sure that the table name is really TABLE1? Maybe you need to change it to the correct table name.
0
 
Rey Obrero (Capricorn1)Commented:
just use dcount

dim xCount
xCount=dcount("*","table1")

0
 
Rey Obrero (Capricorn1)Commented:
if you want to use the select statement, you can use it to open recordset

dim rs as dao.recordset
Dim strSQL As String

strSQL = "SELECT Count(*) AS [Count] " & _
"FROM Table1;"

set rs=currentdb.openrecordset(strSql)
msgbox rs("[Count]")
0
 
aikimarkCommented:
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("table1").RecordCount

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]

Open in new window

0
 
ca1358Author Commented:
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now