Link to Oracle tables with VBA

[RLN]

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.

I read somewhere (quite a while back) that if you use ADO in Access, you
can connect without the need for ODBC, TNSNAMES.ORA and the Oracle
client installed on the user's desktop.

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 Visual Basic code that connects to my Oracle database
without the use of an 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 = "MyUniqueLo gin"
strpass = "MyUniquePa ss"
strTable = "MyUniqueTa ble"

Set rsTemp = New Recordset
Set cnConn = New Connection
cnConn.Connecti onString = "Provider=OraOL EDB.Oracle.1;" & "Password=" &
strpass & "; User ID=" & strlogin & ";Data Source=" & strDB & "; Persist
Security Info=True"
cnConn.CursorLo cation = adUseClient
cnConn.Open

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

Debug.Print "rsTemp!Res ults are: " & rsTemp!Results
Debug.Print "rsTemp.Recordc ount is: " & rsTemp.RecordCo unt

rsTemp.Close
Set rsTemp = Nothing
End Sub

<end code>

After the DB connects, you would normally use Linked Table Manager to
link to the tables you need. Using VBA has anyone out there tried to
-interrogate to see if the application already has a link to TableX and
Table Y
-and if the table is not currently linked, use VBA code to link the
tables before the first form loads?

Thanks for the assistance.

RLN
----------
"Perseveran ce - there is no substitute for hard work."
- Thomas Alva Edison

*** Sent via Developersdex http://www.developersdex.com ***