OLEDB vs. DSN-less connection string

Posted on 2001-09-10
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?

Question by:dtomyn
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
LVL 43

Expert Comment

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).

Accepted Solution

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
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.


Author Comment

ID: 6471243
Thanks for the quick responses.

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

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?
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 43

Expert Comment

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.

Expert Comment

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.

Expert Comment

ID: 6471522
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.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…

730 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