Oracle Database Connection via Access 2010

[dirk1965]

All,

I have a VB routine that worked prior on Windows XP SP3, Oracle 9, & Excel 2003&2007. I have recently upgraded to Windows 7 64-bit, Oracle 11gR2 64-bit, and Office 2010 32-bit (64-bit is having to many problems that my company is holding off on it).

Since upgrading, the routine is failing to connect to an Oracle database even though I've setup a 32-bit ODBC. I'm suspicious if the set statement formats have changed for Oracle 11g, but the routine still compiles without error.

I would appriciate help on figuring out what may be the problem.

Thx!


----------------------------------------------------------------------------------
Sub Connect_DB()
connect_string = ActiveWorkbook.Worksheets("config").Range("B2").value
user = ActiveWorkbook.Worksheets("config").Range("B3").value
user_pass = user & "/" & ActiveWorkbook.Worksheets("config").Range("B4").value
MsgBox ("Connecting as " & user)
On Error GoTo SetConnectError
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase(connect_string, user_pass, 0&)
connected_flag = True
MsgBox ("Connected to Database as User " & user)
Exit Sub
SetConnectError:
On Error GoTo 0
connected_flag = False
MsgBox ("Database connection failed !")
Exit Sub
End Sub

 

[xenou]

You are using OO40 which is an Oracle product:
http://www.orafaq.com/wiki/Oracle_Objects_for_OLE

You'd probably want to contact Oracle support on this if you can. It's unclear from some web searching whether this product even has a 64 bit port yet, or if the 32 bit version will work on your 64 bit machine.

There are other ways to connect to an oracle database, but the purpose of OO40 is to provide a native interface that performs better than generic solutions such as ODBC connections.

ξ