Need to Connect to Oracle DB with no ODBC

[RLN]

I have an Access2002 database that needs to connect to an Oracle
Database. I connected to my Oracle DB in a simple VB6 app using no ODBC
data source. How do I do the same thing using VBA in Access?

Below is the code I used in the VB app to connect to the Oracle
database, and I just can't seem to get it translated to ADO in
Access/VBA.

Any assistance is appreciated.

Here is the VB code that connects to Oracle with no OBDC:
<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

----------
"Perseverance - there is no substitute for hard work."
- Thomas Alva Edison

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

[Mark]

"RLN" <no****@devdex.com> wrote:

I have an Access2002 database that needs to connect to an Oracle
Database. I connected to my Oracle DB in a simple VB6 app using no ODBC
data source. How do I do the same thing using VBA in Access?

Below is the code I used in the VB app to connect to the Oracle
database, and I just can't seem to get it translated to ADO in
Access/VBA.

It sounds like you want to use ADO and oraOLEDB together?
I haven't done this, but a Google search turned up the following
which you might find helpful:

http://msdn.microsoft.com/library/de...asp?frame=true

Here is a "tiny URL" link to the same MSDN page:

http://tinyurl.com/8ct4k

-Mark

[Tim Marshall]

RLN wrote:

I have an Access2002 database that needs to connect to an Oracle
Database. I connected to my Oracle DB in a simple VB6 app using no ODBC
data source. How do I do the same thing using VBA in Access?

You're using ADO in your code, something with which I'm not familiar.

My understanding is you cannot connect without ODBC (but see comments in
last para, below). You can, in ADO, connect without a DSN, however.
But be warned - the ADO methods used to make such a connection are such
that you cannot use the reports in an mdb.

For DSNless connections, see
http://www.carlprothman.net/Default.aspx?tabid=81

This also has OLE DB provider connections, something I once investigated
a long time ago for connecting to Oracle via VB using Oracle's 0034 or
something OLE connector. Not sure if this can be done in Access, maybe
some testing with connection on the string examples. I would love to
hear if you're successful with it.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[david epsom dot com dot au]

Access is VB. Access has a different forms engine,
but it uses the same language engine.

I don't see anything wrong with the code you posted.
It looks like ordinary VB. It looks like it would
work in VB4,VB5,VB6,VBA5,VBA6.

The code you have posted does not require translation.
If it works in an VB environment, it works in VBA6,
which is bound into Access 2002.

Perhaps, you have not set your project references
correctly? What project references are you using
in the project where the code does work?

(david)

"RLN" <no****@devdex.com> wrote in message
news:_z***************@news.uswest.net...

I have an Access2002 database that needs to connect to an Oracle
Database. I connected to my Oracle DB in a simple VB6 app using no ODBC
data source. How do I do the same thing using VBA in Access?

Below is the code I used in the VB app to connect to the Oracle
database, and I just can't seem to get it translated to ADO in
Access/VBA.

Any assistance is appreciated.

Here is the VB code that connects to Oracle with no OBDC:
<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

----------
"Perseverance - there is no substitute for hard work."
- Thomas Alva Edison

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

[TechBoy]

>>You can, in ADO, connect without a DSN, however. <<
I don't think so.

Currently I have a connection string that works in code w/ ADO.
However when I made one little change to the TNSNames file then the
connection bombed. Same happened when I would tweak the ODBC data
source. So unless I'm missing something here, connecting to Oracle via
ADO requires ODBC & a TNSNames.ORA file and cannot be done with just
VBA code alone.

My goal here is simple. Just trying to get away from having to load
and configure a TNSNames.ORA file and an ODBC data source on each
user's workstation. I understand that the Oracle 8i client has to be
loaded on the user's workstation though.

I'm looking to just simply deploy my Access application to each
workstation minus the TNSNames/ODBC headaches.