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
LVL 43

Expert Comment

Comment Utility
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
Comment Utility
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

Comment Utility
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?
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

LVL 43

Expert Comment

Comment Utility
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

Comment Utility
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

Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

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…
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.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now