Welcome to Babylon of Microsoft database access technologies, APIs and tools! Why Babylon? Because all these technologies are designed to provide unified standardized language and communication way between data provider (of any kind) and consumer (application, developer, user).
But over years some technologies were more successful in their missions then others. In this article I would like to introduce you to world of Microsoft database technologies with target to help you to talk, develop, and use these technologies.
You may be wondered about form of table but a) I like tables and b) I think it’s great for gather overview and imagination how huge and extensive topic database technologies are.
Unfortunately is a result of my today and limited knowledge. I am still learning yet ADO.NET, and developer technologies LINQ, Entity Frameworks. Later, I will extend table for these buzz words.
Also, don’t miss general link section bellow!
Table of Contents
Encyclopedia
ODBC
Status: retirement
Open Database Connectivity. Low-level interface in C.
Old industry standard for manipulating relational data using SQL query syntax across disparate data sources. ODBC is written in non-objective C language.
Should be replaced by OLE DB where possible.
Manageable through “Data Sources (ODBC)” applet in Administrative Tools.
ODBC Data Source Administrator:
http://msdn.microsoft.com/en-us/library/ms714024(VS.85).aspx
OLE DB
Status: vital
Low-level interface. COM-based.
OLE DB is build on the success of ODBC by providing an open standard for accessing all kinds of data. Unlike ODBC but not imposes specific limitation on either the query syntax, or the structure of the data exposed.
In actual OLE DB haven’t any relation to OLE (Object Linking and Embedding) and it’s a marketing name.
Is today’s industry standard of database access. If database haven’t OLE DB provider, use OLE DB Provider for ODBC Drivers, enables you to expose any ODBC Data Source to an OLE DB consumer (= to ADO, ADO is definitive consumer of OLE DB)
Haven’t any management GUI in Windows.
ADO
Status: retirement
ActiveX Data Objects. High-level, easy-to-use and language-neutral API for accessing OLE DB databases.
ADO is the single data interface you need to know for all database development in all kind of applications like n-tier client/server or Web-based data-driven solution development.
ADO consist of these several top-level objects like Connection, Recordset, Command, Error, Field.
Evolution of ADO with coming of .NET Framework is ADO.NET.
ADOX
Status: retirement
ADO eXtension for Data Definition Language and Security
Some but in plain English: with basic ADO you can’t create and modify schemas. Of course, same can be achieved by „CREATE/ALTER TABLE“ etc. SQL commands, but thanks to object-oriented approach you write one and same code for various data sources regardless of their native syntax differences. Not speaking that with ADO (and underlying OLE DB) you can access to non-relation (with no SQL language) sources.
ADOMD
Status: retirement
ADO Multidimensional. Easy access to multidimensional data, schema, query a cube, and retrieve the results.
Thanks to difficulty of multidimensional databases access cannot be unified. Refer to the documentation for your OLAP OLE DB provider for more detailed information on the specific syntax and behaviors supported by your provider.
ADO.NET
Status: vital
Evolution of ADO in .NET platform.
ADO.NET is a set of classes that expose data access services to the .NET programmer. ADO.NET provides functionality to developers writing managed code similar to the functionality provided to native COM developers by ADO. ADO.NET provides consistent access to data sources such as SQL Server, as well as data sources exposed through OLE DB and XML.
Elementary providers provides .NET application:
- SQL .NET Data Provider: access to SQL Server database directly.
- OLE DB .NET Data Provider: databases by using their native OLE DB Providers.
- ODBC .NET Data Provider: databases by using their ODBC drivers.
Currently, I am a beginner in ADO.NET so better explanation will be here later.
ADO.NET Overview:
http://msdn.microsoft.com/en-us/library/h43ks021.aspx
JET
Status: dead
Joint Engine Technology. Desktop database system.
Default database engine for Visual Basic (prior VB.NET) as well as other Microsoft applications such as Microsoft Access, Microsoft Excel, Project (all prior < Office 2007), and Microsoft Foundation Classes (MFC).
Last version was 4.0.
Access
Status: vital
Database application designer. Not database engine!
Access is GUI for creating DB-oriented application, not a database engine. Default database engine used in Access prior Access 2007 is JET. In other word Access <> JET. Default database engine of Access 2007 is ACE.
ISAM
Status: N/A
Indexed Sequential Access Method. General algorithm for fast retrieval of sequential data, not specific implementation.
For example MySQL implements and extends ISAM as MyISAM.
Installable ISAM (IISAM)
Status: dead
Driver for JET engine based on ISAM algorithm.
Best known for error message „Could not find installable ISAM“ when you import/export/link in Access or Excel.
List of my drivers under C:\Windows\System32 (Jet 4.0):
- Msexcl40.dll (Microsoft Excel)
- Mstext40.dll (Text)
- Msltus40.dll (Lotus 1-2-3)
- Mspdox40.dll (Paradox)
- Msxbse40.dll (Microsoft FoxPro)
- Msxbse40.dll (dBase)
MDAC
vital
Microsoft Data Access Components. Distribution unit of Microsoft database technologies.
MDAC or MDAC stack is name for distribution software package containing installation of Microsoft database technologies. Contains ADO, various ODBC Drivers and OLE DB Providers.
Current version is 2.8 SP1. Every computer with Windows XP SP2 or later has it.
From Windows Vista renamed to Windows Data Access Components (WDAC)
MDAC homepage:
http://msdn.microsoft.com/en-us/data/aa937729.aspx
MDAC roadmap (past, present, future):
http://msdn.microsoft.com/en-us/library/ms810810.aspx
What is in what version of MDAC:
http://support.microsoft.com/kb/190463
Developer documentation:
http://msdn2.microsoft.com/en-us/library/ms811910.aspx
WDAC
Status: vital
Windows Data Access Components. Formerly MDAC. New name in Vista and further Windows.
SQLNCLI / SQL Native Client
Status: vital
New stand-alone for SQL Server 2005 and later. If you need all SQL Server 2005+ features not accessible through standard OLE DB/ODBC.
It combines the SQL OLE DB Provider and the SQL ODBC Driver into one native dynamic-link library (DLL). Not necessary in .NET. Use SQL Server .NET Data Provider instead.
Not a part of MDAC package. Distributed separately.
SQL Native Client Programming:
http://msdn.microsoft.com/cs-cz/library/ms130892(en-us,SQL.90).aspx.
DAO
Status: dead
Data Access Object. Designed for Visual Basic (VB) and Visual Basic for Application (VBA).
Able connect only to JET. Successor is ADO.
DSP
Status: dead
Data Shape Provider. Deprecated. Forgot it completely.
RDS
Status: dead
Remote Data Service. Deprecated. Forgot it completely.
LINQ
Status: vital
Language Integrated Query. What originally SQL should be – standardized query language against various data sources.
Currently, I am beginner in LINQ. Explanation will be here later.
DSN
Status: retirement
Data Source Name. Definition of ODBC data source.
Is simply a name accessible from client application that define all necessary information to access data source thought ODBC.
- file DSN – definition stored in text file (.dsn extension) in (usually) “c:\Program Files\Common Files\ODBC\Data Sources”. Shared among all users who has permissions to a file. Also passwords are save in plain text!
- system DSN – same as file DSN but stored in Windows registry. Visible to all users on this machine, including Windows (formerly NT) Services.
- user DSN – same as system DSN but visible only to you and on your machine.
UDL file/ Data Link File
Status: vital
Same like file file DSN (ODBC) but for ADO (it implies OLE DB).
Because OLE DB haven’t visual GUI for managing you must create new file with .udl extension, then double-click to see configuration dialog. Or create content of UDL by hand.
How To Use Data Link Files with ADO:
http://support.microsoft.com/kb/q189680/
Data Link API Overview:
http://msdn.microsoft.com/en-us/library/ms718102(VS.85).aspx
ODC file
Status: I don’t know
Office Data Connection. Connection information in stored in text file for Office 2003. Similar to file DSN and UDL file.
I don’t understand the sense and future of ODC.
ACE
Status: vital
Microsoft Access Engine (ACE Engine). Default database engine in Access 2007. Successor of Jet engine.
The ACE engine is fully backward-compatible with previous versions of the Jet engine, so it reads and writes (.mdb) files from earlier Access versions. The new engine introduces a new default file format, (.accdb), that brings several improvements to Access, including complex data types such as multivalue fields, the attachment data type and history tracking in memo fields. It also brings security and encryption improvements.
UDA
Status: dead
Universal Data Access. Microsoft marketing name.
UDA is Microsoft’s strategy for providing high-performance access to all types of information (including relational and non-relational data) across an organization from the desktop to enterprise systems.
Some useful Microsoft database links
Microsoft database technologies “guidepost”
http://msdn.microsoft.com/en-us/data/default(en-us).aspx
Internet’s greatest colletion fo connection strings
http://www.carlprothman.net/Default.aspx?tabid=81
How To Use ADO with Excel Data from Visual Basic or VBA
http://support.microsoft.com/kb/257819
Excel connection strings
http://www.connectionstrings.com/excel#20
2007 Office System Driver: Data Connectivity Components
http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en
ExcelADO(.exe) demonstrates how to use ADO to read and write data in Excel workbooks
http://support.microsoft.com/default.aspx?scid=kb;en-us;278973
jobs at home says: (November 22, 2011 at 5:07 am)
How come you dont have your website viewable in mobile format? cant view anything in my netbook.