Oracle Multi-Org DSN Connection

[CT25]

I have tried several approaches in other threads but have not been successful.
I am using pass-thru querys to connect to 4 different oracle worlds. The
connection strings in the pass-thrus are setup without Uid & Pwd.

I will like to "ping" each world when I open a form so that all required
uids & pwds are entered up front. Do not want to save uid & pwd.(Other
users). 2 of the connections are multi-org (same server but use different
Uid). All my connections are in pass-thru (no tables) so not listed in link
manager.

 

[Stefan Hoffmann]

hi,

I have tried several approaches in other threads but have not been successful.
I am using pass-thru querys to connect to 4 different oracle worlds. The
connection strings in the pass-thrus are setup without Uid & Pwd.

World? What do you mean?

What have you done exactly and what error messages do you get?



mfG
--> stefan <--

 

[CT25]

Some of my trys - don't know if even if ballpark:

strUSERID and strPASSWORD are string variables.

1. No error message but did not work:
Dim conn As New ADODB.Connection
conn.Open "Driver={Microsoft ODBC for Oracle};" & _
"Server=PROD;" & _
"Uid=" & strUSERID & ";Pwd=" & strPASSWORD & ";"

2. Error messge: TNS:Could not resolve sercie name.
Dim conn As New ADODB.Connection
conn.Open "ODBC;Driver={Microsoft ODBC for Oracle};" & _
"Server=PROD;" & _
"Uid=" & strUSERID & ";Pwd=" & strPASSWORD & ";"

3: Error message: Data source name not found and no default driver specified.
Dim conn As New ADODB.Connection
conn.ConnectionString = "ODBC;DSN=PROD;SERVER=PROD;" & _
"Uid=" & strUSERID & ";Pwd=" & strPASSWORD & ";"
conn.Open

4: Error message: Data source name not found and no default driver specified.
Dim conn As New ADODB.Connection
conn.Open "ODBC;DSN=PROD;SERVER=PROD;" & _
"Uid=" & strUSERID & ";Pwd=" & strPASSWORD & ";"

 

[James]

Some of my trys - don't know if even if ballpark:

strUSERID and strPASSWORD are string variables.

1. No error message but did not work:
Dim conn As New ADODB.Connection
conn.Open "Driver={Microsoft ODBC for Oracle};" & _
           "Server=PROD;" & _
           "Uid=" & strUSERID & ";Pwd=" & strPASSWORD & ";"

2.  Error messge: TNS:Could not resolve sercie name.
Dim conn As New ADODB.Connection
conn.Open "ODBC;Driver={Microsoft ODBC for Oracle};" & _
           "Server=PROD;" & _
           "Uid=" & strUSERID & ";Pwd=" & strPASSWORD & ";"

3: Error message: Data source name not found and no default driver specified.
Dim conn As New ADODB.Connection
conn.ConnectionString = "ODBC;DSN=PROD;SERVER=PROD;" & _
                     "Uid=" & strUSERID & ";Pwd=" & strPASSWORD & ";"
conn.Open

4: Error message: Data source name not found and no default driver specified.
Dim conn As New ADODB.Connection
conn.Open "ODBC;DSN=PROD;SERVER=PROD;" & _
                     "Uid=" & strUSERID & ";Pwd=" & strPASSWORD & ";"








- Show quoted text -

Hi

How about creating a DSN using the following first and then trying
your connection?
DBEngine.RegisterDatabase dbname, driver, silent, attributes

Regards
James

 

[CT25]

James, I would prefer to stay away from modifying the registy - any other
options? The DNS are already setup thru the control panel.

 

[Stefan Hoffmann]

hi,

Some of my trys - don't know if even if ballpark:

Try using the Oracle driver. btw, is your TNS setup correct? You should
test it (tnsping, somewhere in Oracle bin path).


mfG
--> stefan <--

 

[CT25]

I verified that my TNS setup is working.
Due to time constraints, this will have to be a future refinement - so I
will be checking back.
On a temporary basis, I have linked to a small table in all four Oracle
worlds and am using a tabledefs.refreshlink to accomplish what I wanted.
Thank you for all your help.
CT