"RLN" <rlntemp-newsgroup_at_yahoo.com> wrote in message
news:1141245399.508283.264940_at_i40g2000cwc.googlegroups.com...
> 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
>
Nope you still need to install the Oracle client side stuff even if you use
MS's stuff. Why don't you download Oracle XE and use it instead of MS
Access? You can install it and build some pretty slick apps. Then your
other users will only need a web browser.
Jim
Received on Wed Mar 01 2006 - 21:50:38 CST
