OLEDB vs. DSN-less connection string

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?

Who is Participating?
MCummings111400Connect With a Mentor Commented:
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.

TimCotteeHead of Software ServicesCommented:
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).
dtomynAuthor Commented:
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?
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

TimCotteeHead of Software ServicesCommented:
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.
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.
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.