Oracle 11G connection strings

[rekrabnai]

Hi all,

We have recently moved to Oracle 11g and now when I try to use some current code I have created to connect to the tables on an ORACLE Server, it fails with an error 3000 ??????

1. The names of the tables I wish to connect to are stored in an MSAccess Table (OracleTables) the field which holds the table name is 'TableName'

2. The Oracle Environments I wish to connect to are strored in an MSAccess Table (OracleEnvironments) which holds the DB Host Name (DbHost), The DB Port Number (DbPort) and the DBSID (DbSID).

3. [Forms]![Connect to database]![cboDatabase] is the name of the DB I wish to connect to.

4. 'txtUsername' and 'txtPassword' are just Text Boxes on a form.

The code I have been using which has always worked fine in the past is :

Private Sub cmdOK_Click()
Dim rs As Recordset
Dim tdf As TableDef
Dim strConnect As String
Dim strSQL As String
Dim x As Boolean

On Error GoTo err_routine

strSQL = ""
strSQL = strSQL & "SELECT * "
strSQL = strSQL & "FROM tblOracleEnvironments "
strSQL = strSQL & "WHERE (((tblOracleEnvironments.DBName)='OracleDBName')); "
strSQL = strSQL & "WHERE (((tblNorthgateDatabaseNames.DBName)='" & [Forms]![Connect to database]![cboDatabase] & "')); "

strConnect = "ODBC;Driver={Microsoft ODBC for Oracle};"
strConnect = strConnect & "CONNECTSTRING=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST="

Set rs = CurrentDb().OpenRecordset(strSQL)
Do While Not rs.EOF
strConnect = strConnect & rs!DbHost
strConnect = strConnect & ")(PORT=" & rs!DbPort
strConnect = strConnect & "))) (CONNECT_DATA = (SID =" & rs!DbSID
strConnect = strConnect & ")));Uid=" & txtUsername
strConnect = strConnect & ";Pwd=" & txtPassword & ";"
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

DoCmd.Hourglass True

Set rs = CurrentDb().OpenRecordset("select * from OracleTables order by 1 asc")
Do While Not rs.EOF
LnkTable = rs!TableName
'create link
Set tdf = CurrentDb().CreateTableDef(rs!TableName)
tdf.Connect = strConnect
tdf.SourceTableName = rs!User & "." & rs!TableName
CurrentDb().TableDefs.Append tdf
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set tdf = Nothing
End If
DoCmd.Hourglass False

Exit Sub

err_routine:
DoCmd.Hourglass False
MsgBox "CONNECTION Issue!!! - Unable to link to specified database. (Error=" & Err & ")."
rs.Close
Set rs = Nothing
Set tdf = Nothing

End Sub

The PC I am using has Windows 7 (32-Bit) and MSAccess 2010 (also 32-bit).

Any light that can be shed on this would be of great help.

Thank you in anticipation..... Rekrabnai

Sometimes if you want to put a nail in a piece of wood, you just gotta hit it with a hammer!!

 

[JoeAtWork]

>>> We have recently moved to Oracle 11g <<<
Have you upgraded the Client software to your computer for 11g?

My first step would be to independently verify you can connect to your database, through SQLPlus or try setting up a DSN in ODBC Administrator and see if you can connect from there. If that doesn't work, then you first need to fix that before looking at your code.

 

[rekrabnai]

Okay, apologies for the delay but I found a 'temporary' solution on technet.

MSAccess needs to be ALWAYS 'Run as Administrator'. (Although i have 'administrator' rights on my pc, this is not enough)

The link for instructions was found here:

http://technet.microsoft.com/en-us/magazine/ff431742.aspx

The downside to this is that I would ALWAYS have to open MSACCESS first and then navigate to my database (which is a pain)

Will be looking for a permanent solution and if/when found I will post it here.

Sometimes if you want to put a nail in a piece of wood, you just gotta hit it with a hammer!!