Hi all
I created some code (which has been used without issue time and again) to create linked tables to an Oracle 10g Database.
Recently one of our servers has been upgraded ro Oracle 11g and now my code will not work.
The code refers to a table 'OracleTables' to get the tablenames I wish to connect to
The code :
Dim rs As Recordset
Dim tdf As TableDef
Dim strConnect As String
strconnect="ODBC;Driver={Microsoft ODBC for Oracle};Server=MyDatabase;Uid=USERID;Pwd=PASSWORD"
DoCmd.Hourglass True
On Error GoTo err_routine
Set rs = CurrentDb().OpenRecordset("select * from OracleTables") 'This contains the Table Name (Tablename) and Owner (User)
Do While Not rs.EOF
If tableexists(rs!Tablename) Then
CurrentDb().TableDefs.Delete (rs!Tablename)
End If
'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
When I execute the code on the 11g server I get the message :
'Unable to link to specified database'
for info, I am using Windows 7, MSAccess 2010 and the Microsoft ODBC for Oracle Driver is version 6.01.760117514
Any help is gratefully appreciated.
Sometimes if you want to put a nail in a piece of wood, you just gotta hit it with a hammer!!
I created some code (which has been used without issue time and again) to create linked tables to an Oracle 10g Database.
Recently one of our servers has been upgraded ro Oracle 11g and now my code will not work.
The code refers to a table 'OracleTables' to get the tablenames I wish to connect to
The code :
Dim rs As Recordset
Dim tdf As TableDef
Dim strConnect As String
strconnect="ODBC;Driver={Microsoft ODBC for Oracle};Server=MyDatabase;Uid=USERID;Pwd=PASSWORD"
DoCmd.Hourglass True
On Error GoTo err_routine
Set rs = CurrentDb().OpenRecordset("select * from OracleTables") 'This contains the Table Name (Tablename) and Owner (User)
Do While Not rs.EOF
If tableexists(rs!Tablename) Then
CurrentDb().TableDefs.Delete (rs!Tablename)
End If
'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
When I execute the code on the 11g server I get the message :
'Unable to link to specified database'
for info, I am using Windows 7, MSAccess 2010 and the Microsoft ODBC for Oracle Driver is version 6.01.760117514
Any help is gratefully appreciated.
Sometimes if you want to put a nail in a piece of wood, you just gotta hit it with a hammer!!