VBA Code to query Access DB

I already have my ADO stuff taken care of and know how to update, write and delete fields in an Access database from Excel VBA, but I cant for the life of me figure out how to get the results of a query from excel vba.. just looking to create a simple If then statement based on the record count of a particular table. Im sure this is much easier than what Im making it out to be....

Thanks!!

Are you asking how to read the data from an Access table? That would just be
a simple
"Select * From table" SQL command.

--

HTH

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


"durex" <[email protected]> wrote in
message news:[email protected]...
>
> I already have my ADO stuff taken care of and know how to update, write
> and delete fields in an Access database from Excel VBA, but I cant for
> the life of me figure out how to get the results of a query from excel
> vba.. just looking to create a simple If then statement based on the
> record count of a particular table. Im sure this is much easier than
> what Im making it out to be....
>
> Thanks!!
>
>
> --
> durex
> ------------------------------------------------------------------------
> durex's Profile:
http://www.excelforum.com/member.php...o&userid=27857
> View this thread: http://www.excelforum.com/showthread...hreadid=489259
>

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

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>