INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Oracle 11g issue with VBA connection

Oracle 11g issue with VBA connection

(OP)
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!!

RE: Oracle 11g issue with VBA connection

Have you attempted to set up an ODBC/DSN to the Oracle database and test?

Duane
Hook'D on Access
MS Access MVP

RE: Oracle 11g issue with VBA connection

(OP)
Hi dhookom

Thanks for the reply.
If you are asking if I went into Administrative toos & ODBC administrator then yes.

I set both ODBC links to both the 10g and the 11g environment using the same userid and they both worked. This suggested to me that :

a) There were no rights issues on the 11g
b) The issue could be with the syntax of the connection string but looking into this I could not find anything.

Any ideas that I may try would be greatfully appreciated.

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close