Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Switching ADO Connection from SQL SERVER to Oracle

Posted on 2006-05-11
10
Medium Priority
?
372 Views
Last Modified: 2008-03-17
Hi,

I am having trouble getting data returned from my ADO connection.

My ADO connection in VB 6 looks like this:
  conn.Open 'OracleDC', 'test', 'test'

Note: 'OracleDC' is an ODBC connection I've set up using the Oracle 10g driver.

The connection actually opens and sql statements can be executed on the connection however, no data is being returned when it should.  

I noticed some things in my connection object which look odd to me but that could be because I'm not that familiiar with setting up ADO connections.

Provider=MSDASQL.1;Password=test;User ID=test;Data Source=OracleDC;Extended Properties="DSN=OracleDC;UID=test;PWD=test;DBQ=TESTDB;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;"

Is it correct that it's using the Provider=MSDASQL provider?  If not, I don't know how to change this since i'm not setting it

Please let me know if I need to provide more information.

Thanks.
0
Comment
Question by:Able22
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 16660538
MSDASQL.1 is the driver to connect to SQLServer.  Try changing it to: MSDAORA.1

That is the Microsoft data provider for Oracle.  I would recommend using an Oracle driver.  I can't suggest one without knowing more about the app.

Check out:
http://www.orafaq.com/faqmsvb.htm#CONNECT
0
 

Author Comment

by:Able22
ID: 16660597
The user has to set up an ODBC then that is used to make the connection.

For whatever reason, MSDASQL.1 is being set as the provider and I don't know how to get the provider to be MSDAORA.1

The ODBC is defined using the Oracle 10g driver
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 16660885
this should answer you concerns about the MSDAORA Data Provider for ORACLE:

.NET Framework Data Provider for Oracle For Oracle data sources.

The .NET Framework Data Provider for Oracle supports Oracle client software version 8.1.7 and later.
Note   The .NET Framework Data Provider for Oracle is not included in the .NET Framework version 1.0. If you require the .NET Framework Data Provider for Oracle and are using the .NET Framework version 1.0, you can download the .NET Framework Data Provider for Oracle at http://msdn.microsoft.com/downloads.

AW
 
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 16660911
by the way, this statement:

"The user has to set up an ODBC then that is used to make the connection.

For whatever reason, MSDASQL.1 is being set as the provider..."

is nonsense.  You should NOT be using the SQL Server Data Provider when attempting to get data from an ORACLE Database.

AW
0
 

Author Comment

by:Able22
ID: 16661101
I'm not sure why you wrote the last comment because that is my question.  This is new to me so I am not sure how to make this any clearer

conn.Open "OracleDC, test, test"  - this line is what is set in VB 6.0 code to establish the ADO connection.  Where 'OracleDC' is the defined ODBC

This is what I see the above Connection string resolved to when I interrogate the ADO connection object at runtime:

Provider=MSDASQL.1;Password=test;User ID=test;Data Source=OracleDC;Extended Properties="DSN=OracleDC;UID=test;PWD=test;DBQ=TESTDB;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;"

So, I am trying to get help understanding why this is happening when I've used an Oracle driver to establish my ODBC

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 16661443
I think I'm starting to get it.  The DSN you created says that it's using the Oracle ODBC driver but it is somehow resolving to the SQDASQL driver?  If that isn't it, I'm still confused.

I suggest you delete and recreate the DSN from scratch and test the connection outside the app.  Please post back results.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16662136
Why not confirm your connection string with this website, pretty useful site, got for lots of DBs

http://www.connectionstrings.com/

0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 1000 total points
ID: 16663318
the DSN named ORACLEDC should be changed to use the MSDAORA Data Provider.  Open the DSN in the Control Panel>Administrative Tools>Data Sources (ODBC).  Select the OracleDC DSN, and then choose Configure.  There you can change the DataProvider that is currently assigned, to the Oracel DataProvider - it is currently assigned the SQL Server DataProvider - that is where your problem lies.

AW
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question