Running SQL query from Access '97 module

[dougmcc1]

How do you run a SQL query from an Access '97 module? I need to count the number of rows in a table in the database and then assign that value to a variable.

Thanks.

 

[WayneRyan]

Doug,

SomeVariable = DCount("[SomeField]", "SomeTable")

Wayne

 

[dougmcc1]

Thanks Wayne. I also need to know how to run a SELECT query.

 

[WayneRyan]

doug,

You'll need a recordset.

Dim dbs As DAO.Database
Dim rst As Recordset
Dim lngNumberRecords As Long

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("YourQuery")
'
' How Many records?
'
If rst.EOF and rst.BOF Then
   lngNumberRecords = 0
Else
   rst.MoveFirst
   rst.MoveLast
   lngNumberRecords = rst.RecordCount
End If

Wayne