I don't think you can do a record count via the SQL query, I don't think the
Access data provider supports that, but you could query the table and count
them, like so

Sub GetData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "SELECT * From Contacts"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
ary = oRS.getrows
MsgBox UBound(ary) & " records retrieved"
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"durex" wrote in message
...

No.. sorry fopr not being more specific... I need to perform that sql
statement (record count) in VBA, from excel and assign it to a
variable. Im already able to update, delete and add fields to an
access database from vba in excel, but I cant figure out how to do a
query from it.

Thanks again


--
durex
------------------------------------------------------------------------
durex's Profile:
http://www.excelforum.com/member.php...o&userid=27857
View this thread: http://www.excelforum.com/showthread...hreadid=489259