Use ADO to make your connection to either Access or SQL Server. This works great for VBA.
Dim MyDb, MyRec as Variant
Set MyDb = New ADODB.Connection
Set MyRec = New ADODB.Recordset
Then use the MyDb.Open to connect to your database of choice.
A good source for ADO connections is http://www.able-consulting.com/ADO_Conn.htm
From there you can open your recordset like so
MyRec.Open "SELECT * FROM Table WHERE Field = 'Condition'", MyDb, adOpenDynamic, adLockOptimistic, adCmdText
make sure to close your connections when done. I hope this helps.