Solved

OLEDB vs. DSN-less connection string

Posted on 2001-09-10
6
572 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
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…

777 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