This is from VB6. It should work within Access.
Dim objConn As ADODB.Connection
Dim rsTmp As ADODB.Recordset
Dim strConnect as String
strConnect="Provider=OraOLEDB.Oracle.1;Password=some_password;Persist Security Info=True;User ID=User1;Data Source=DBname"
Set rsTmp = CreateObject("ADODB.Recordset"

Set objConn = CreateObject("ADODB.Connection"
objConn.Open sConnect
With rsTmp
.CursorLocation = adUseClient
.Open "Select * from some_table", objConn, adCursorType, adLockType
Set .ActiveConnection = Nothing
End With
Set CallingFunctionName = rsTmp