The Recordset object needs to be told what Connection to use. If you've got
an existing Connection object open, you can use that, but you still must
associated that Connection object to the Recordset object you're creating in
your routine.

If your code and data (or linked tables) are in the same MDB or ADP, you can
use CurrentProject.Connection. For example ...

Public Sub ListData()

   Dim rst As ADODB.Recordset
   Set rst = New ADODB.Recordset
   rst.Open "SELECT * FROM Classification", CurrentProject.Connection
   Do Until rst.EOF
       Debug.Print rst.Fields(0)
       rst.MoveNext
   Loop
   rst.Close

End Sub

Otherwise, when the code is execting in an MDB or ADP that does not contain
or link to the data, you need to create and open the connection, much as you
would in ASP ...

Public Sub ListExternalData()

   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset

   Set cnn = New ADODB.Connection
   cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES\Northwind.mdb;" & _
       "Persist Security Info=False"
   cnn.Open
   Set rst = New ADODB.Recordset
   rst.Open "SELECT * FROM Employees", cnn
   Do Until rst.EOF
       Debug.Print rst.Fields(1)
       rst.MoveNext
   Loop
   rst.Close
   cnn.Close

End Sub