oracle connection error

[juliam921]

I previously was running my visual basic program on vb6, Win 2000 with mdac 2.5 and an oracle 8.1.6 client. Now, I am running Windows XP with vb6, mdac 2.7 and an oracle 9.2 client on my machine and i get the following error at the cnnOrclConnection.Open line:

method open of object _connection failed

Public cnnOrclConnection As New ADODB.Connection, _
primaryRS As New ADODB.Recordset

Public Sub GetConnections()

cnnOrclConnection.Open "DSN=museum;UID=museum;PWD=123;SERVER=museump;"

Exit Sub

The odbc connection tests fine and so does a tnsping. So what has changed now that I need to fix? Is it Oracle 9 that is causing this? Any help is appreciated!!
-Julia

 

[nicsin]

Is the p intended in museump?

 

[juliam921]

Yes, that is the name of our dsn museump p is for production.
-Julia

Thank you! Any other ideas?

 

[zemp]

Is your UID, PWD and SERVER set up in the DSN? If so it may be redundant in your code and/or causing confusion. Try,

cnnOrclConnection.Open "DSN=museum;"

See this site for examples of connection strings.

http://www.able-consulting.com/ADO_Conn.htm

Take Care,

zemp

 

[juliam921]

I tried that and still the same. The password is not stored in the dsn, so I need that (and I tried just dsn and passowrd and same error). I am using the ODBC Connection Microsoft ODBC for Oracle. Do I need to specify the provider in the connection string? How do I find out what it is?

Thanks,
Julia

 

[zemp]

Check the link in my last post for connection string examples.

Double check your DSN. Place the password in the DSN and try to connect. Usually if the DSN connection is successsful then ADO has no trouble using it.

I would also double check that the user and password are valid and have the proper permissions.

Take Care,

zemp

 

[juliam921]

The Microsoft ODBC for Oracle doesn't allow you to store a password in the connection. I can connect to the database using sql plus using this id and password. Also, this application worked before I upgraded to XP and Oracle 9i. So its not the id or password. Could it be the version of the dlls that Oracle 9i uses?

Thanks,
Julia

 

[TomKane]

You could try either Microsoft Data Link or the ADO data control to build you a connection string for your oracle database - perhaps there are extra bits that have to be catered for in the higher version?

Best of luck

 

[juliam921]

I have the ADO data control checked under "components". What else do I have to do? I have never used a control to build a connection string.

Thanks.
Julia

 

[TomKane]

If you select (custom) from the properties window for the control, select "Use Connection String" and "Build" - then you will go through the data link properties which will build you a connection string. You can then cut and paste this connection string into your apps.

Hope this helps...