VBA Code to query Access DB

[durex]

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!!

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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)