Simple SQL and Access problem

[mjmartin]

Ok, all I want to do is perform an SQL query in access to count the number of records in a query and return that to an integer. I want to run an SQL query like:

Dim SQL as string
Dim RecordCount as integer
SQL = "SELECT COUNT(TrackID) FROM tracks WHERE FacilityID = " & FacilityID.Value

RecordCount = DoCmd.RunSQL SQL, 0
MsgBox RecordCount

The problem is... Access hates me and won't let me run something like that.

Does anyone know how to solve this one?

 

[pbaldy]

Access doesn't hate you...it hates all of us.

DoCmd.RunSQL will only work with an action query, not a simple select. You either need to open a recordset with that SQL and get the value from the recordset or use a DCount formula.

 

[mjmartin]

Ok, so I have a subform which runs a relational query and all I want to do is count the amount of records returned from that query, is there any way I can do that with out have to set-up a query?

 

[Mile-O]

Don't create this query in VBA - just create it in the query design grid, use DAO to access it and feel the benefit.

 

[mjmartin]

It's too late, done it now. I used:

Public Function CountRecords(FacilityID As Integer, RecordExpr As String, Table As String) As Integer
    'On Error Resume Next
    CountRecords = Access.DCount(RecordExpr, Table, "[FacilityID] = " & FacilityID & "")
End Function

The reason why I'd prefer VBA is that I've got about 20 tables in this database and can't be bothered to create it in a query design grid.