Solved

OLEDB vs. DSN-less connection string

Posted on 2001-09-10
6
590 Views
Last Modified: 2007-12-19
I got a question today from somewhere I used to work and was unable to answer it.

The project over there is a VB front-end, SQL Server back-end.  When reports were needed, the company decided to call into Access 97 and using pass-through queries to call stored procedures as the source, open up reports as needed via a controlling VB form.

The VB front-end uses ADO with OLEDB.  Access uses DSN-less connections for the connection string in the reports (for the pass-through query).

They are wondering (and now me) if there would be any harm in changing the connection string in VB to be the DSN-less version.  They are looking at doing this so that they only have to maintain one version of the connection string instead of 2.

So, really I have 2 questions:
1) Is it possible to use OLEDB for the connection string in Access 97? (So far it looks like it is not possible)
2) What are the issues with moving from an OLEDB connection string to a DSN-less (ODBC) connection string?

Thanks!
0
Comment
Question by:dtomyn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 6471200
1) "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=D:\Program Files\Microsoft Visual Studio\VB98\Biblio.mdb" for example is a DSN-less connection string to an access database.

2) As long as the databases are the same then there should be no problems with the conversion. Providing that all machines can see the database in the same way (drive mapping or UNC path to the database).
0
 
LVL 3

Accepted Solution

by:
MCummings111400 earned 50 total points
ID: 6471209
So, really I have 2 questions:
1) Is it possible to use OLEDB for the connection string in Access 97? (So far it looks like it is not
possible)
A: No, access will only use ODBC.

2) What are the issues with moving from an OLEDB connection string to a DSN-less (ODBC) connection string?
A: Not Many. Simpy make sure you can attach to the datasource using your new string. ADO won't care what the provider is mostly. A few things that can happen. Loss of Bookmark capability, Loss of .recordcount functionality.

0
 
LVL 5

Author Comment

by:dtomyn
ID: 6471243
Thanks for the quick responses.

TimCottee,
I think perhaps you misunderstood my question.  Both the VB and Access projects are pointing to a SQL Server database

MCummings,
Thanks for the comments.  I will mention to them to verify that code that uses Bookmark and .Recordcount continue to work the same.

Any others?
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 43

Expert Comment

by:TimCottee
ID: 6471266
Ok, perhaps it is my misunderstanding. If you are talking about using the same connection string in VB as in Access with ADO then there is absolutely no problem. To all intents and purposes both the styles of connection string have the same results. A DSN is only a means of storing the same sort of information as is present in a connection string. The ADO layer parses the DSN to extract the appropriate parameters to be used in the connection. There should be no issues changing in this way.
0
 
LVL 1

Expert Comment

by:CArnold
ID: 6471374
I've been following along with this question.
dtomyn, I'm sorry that I don't a comment or answer that is directly related to your question.  In fact, your question sparked one that I had myself not too long ago.  Perhaps we could both gain some insight into this subject, together.

I am offering 75 points for any expert out there who can tell me the pros and cons of OLE DB vs. ODBC and the differences between the two.

My company currently uses ODBC, and I have always been an advocate of using OLE DB when connecting to Oracle, Access, or SQL.  I have found OLE DB to be the simplest of the bunch.  However, before I begin pressing for OLE DB in lieu of ODBC in future projects, I'd like to make a knowledgeable decision (without reading a 1000+ page book on each of the connection methods).  Hence my "info in a nutshell" question.  If you would like to comment on this question, please look for "OLEDB vs ODBC".
Sometimes, I receive such great responses that I wish I could split the points and divide them between two (or more) people.  If I run into such a case with this question (I realize and appreciate the time and effort some of the EE experts put into each question), I will create a similar posting and award 75 additional points to the runner up.

Thanks, again, to everyone.
0
 

Expert Comment

by:ratantata
ID: 6471522
CArnold,
Microsoft introduced OLE DB to overcome the 'relational' problem with the ODBC standard. Basically, they wanted to allow people to query data sources that are not relational. Together with OLE DB, Microsoft promoted ADO (ActiveX? Data Objects), a set of easy-to-use objects and methods to access and manipulate data in a database server through an OLE DB provider.
As of today, if you develop new database applications, you should use ADO. ADO (together with its variations - ADOMD, ADOX and ADOCE) is THE toolkit to use because with ADO you can query different relational data sources while sharing the same source code. However with ADO, you can also share the code to access non-relational databases, such as VSAM, LDAP, etc.

If you're writing an application using Visual Basic, VBScript or JScript (for web applications) or Powerbuilder, for example, Microsoft's ADO provides a simplified interface to OLE DB calls. You can also use ADO to access ODBC, but ADO first makes OLE DB calls that in turn call the ODBC API. OLE DB provides a clear advantage in this situation because OLE DB is called directly.

If you need to access data from a third-party product such as a query or reporting tool, you typically need an ODBC driver. For these applications, no database connectivity programming is required. You can just install the driver then access it from the third-party application's menu-driven interface. Currently, few third party applications support OLE DB in the same way that SQL Server 7.0 does now. SQL 7.0 allows you to access a DB2 database using an OLE DB Provider.

There are a number of reasons why OLE DB drivers are faster than ODBC drivers:


Data type conversions for OLE DB compliant apps are faster and more precise than using ODBC drivers by means of the 'OLEDB-to-ODBC' OLE DB driver.
Recordset data caching is a lot more efficient with OLE DB drivers. The reason for better performance is the OLE DB recordset data caching approach that delegates the individual OLE DB driver the task of storing its rowset data.
With ADO and OLE DB, you can change the isolation level (or a number of other connection properties" at any time very easily associating a connection property to your query. With ODBC this is more difficult.

I hope this helps.
Cheers!
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

728 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