Debbie:
=====
I'm a NEWBIE, but this worked for my. You must have "Microsoft ActiveX Data Objects" included in your references (Library List) for this to work. You'll just have to insert your own names and mung with the "Debug.Print" statements. This is working code that I tested this morning.
Code:
' Create an ADODB RecordSet from a Table in the Current Database
Sub ADO_RST()
'From MS Access Programmer's Handbook
'http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html/deovropeningaccessdatabasebyusingado.asp
    Dim myCon As ADODB.Connection
    Dim myRST As ADODB.Recordset
    Dim mySQL As String
    
    'Define the Query for the RecordSet data from the table
    mySQL = "SELECT * FROM TEST_EXTRACT"
    
    'Test "New" keyword HERE
    Set myCon = New ADODB.Connection
    'Define the intrinsic connection to the current database
    Set myCon = CurrentProject.Connection
    Debug.Print myCon.ConnectionString  'This is what it looks like
    'Create the RecordSet for the data from the table and open it
    Set myRST = New ADODB.Recordset
    myRST.Open mySQL, myCon, adOpenForwardOnly, adLockReadOnly
    'Scan and look at the Recordset data from my table
    With myRST
    While Not .EOF
        Debug.Print .Fields(0), .Fields(1), .Fields(2), .Fields(3)
        .MoveNext
    Wend
    End With
    
    'Clean House
    myRST.Close
    Set myRST = Nothing
    'myCon.Close  < Don't close the intrinsic connection
    Set myCon = Nothing
End Sub
Good Luck and Good Learning!