Connect to Oracle with VBA

I have a Microsoft Access2002 database that needs to connect to an Oracle
Database. I need to map 2 tables from the Oracle DB to retrieve the proper data.

If I use the Microsoft driver for Oracle, is it possible to connect to an Oracle DB using straight VBA code inside of Access and not having to mess with configuring the user's workstations with an ODBC data source, TNSNames.ORA file, etc.?

Any assistance is appreciated.

Using Visual Basic 6.0, below is the code I used in a simple VB app to connect to the Oracle database, and I just can't seem to get it translated to ADO in Access/VBA.

Here is the VB code that connects to my Oracle database without the use of and ODBC data source:
<begin code>

Private Sub Form_Load()
Dim cnConn As Connection
Dim rsTemp As Recordset
Dim strDB As String, strTable As String, strMsg As String, strSQL As String

strDB = "MyUniqueDB"
strlogin = "MyUniqueLogin"
strpass = "MyUniquePass"
strTable = "MyUniqueTable"

Set rsTemp = New Recordset
Set cnConn = New Connection
cnConn.ConnectionString = "Provider=OraOLEDB.Oracle.1;" & "Password=" &

strpass & "; User ID=" & strlogin & ";Data Source=" & strDB & "; Persist
Security Info=True"
cnConn.CursorLocation = adUseClient
cnConn.Open

strSQL = "SELECT Count(*) as results FROM " & strTable

        rsTemp.Open "SELECT Count(*) as results FROM " &
        strTable, cnConn, adOpenStatic, adLockReadOnly

Debug.Print "rsTemp!Results are: " & rsTemp!Results Debug.Print "rsTemp.Recordcount is: " & rsTemp.RecordCount

rsTemp.Close
Set rsTemp = Nothing
End Sub

<end code>

RLN Received on Wed Mar 01 2006 - 14:36:39 CST