VBA Code to query Access DB

  • Thread starter Thread starter durex
  • Start date Start date
D

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!!
 
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)
 
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)
 
Back
Top