I have an SQL Server database, and I need to push data into it through vbscript, as well as pull data into Excel. I have found multiple connection strings, but no repository for the benefits of performance and functionality comparing them. The driver options (Provider=
) I have found so far are:
Different things I read say that ODBC is better because it has been around longer. And that OLE DB has been around long enough to have the same advantages. And OLE DB was made to work with a certain company's applications. And ODBC was made by the same company. And OLE DB can connect to and from different kinds of applications better. And ODBC works better with databases. And Native is...Native, so must be better... because of the name?
I find multiple questions here on SO floating around with no or partial answers, or having multiple comments claiming the answers are out of date. So, as of now, what the specific differences between these different drivers? Do they have different performance in different circumstances? Do they have different features? Do I need to do profiling to determine the best performance and reliability for my particular use case, or is there a standard "best practice" recommended by Microsoft or some recognized expert? Or are they all basically doing the same thing and as long as it's installed on the target system it doesn't really matter?
SqlConnection is designed to access SQL Server, while OleDbConnection is designed to access any database.
ODBC is constrained to relational data stores; OLE DB supports all forms of data stores (relational, hierarchical, etc) In general, OLE DB provides a richer and more flexible interface for data access because it is not tightly bound to a command syntax (like SQL in the case of ODBC).
ODBC provides data types and functions that help applications to interact with the database. SQL is used to create queries to manipulate the data stored in a database.
2- OLE DB is more faster than ODBC ... Microsoft ADO, OL DB, and ODBC MDAC Components. Developers can use any of MDAC's components (ODBC, OLE DB, and ADO) to connect to several relational and non-relational data stores.
ODBC-it is designed for connecting to relational databases. However, OLE DB can access relational databases as well as nonrelational databases.
There is data in your mail servers, directory services, spreadsheets, and text files. OLE DB allows SQL Server to link to these nonrelational database systems. For instance, if you want to query, through SQL Server, the Active Directory on the domain controller, you couldn't do this with ODBC, because it's not a relational database. However, you could use an OLE DB provider to accomplish that.
http://www.sqlservercentral.com/Forums/Topic537592-338-1.aspx
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With