MDAC
TAGS: Computers, Databases, Microsoft, TECH
Intro
The Microsoft UDA (Universal Data Access) is their current name for their general strategy to easily and powerfully access any data, any where.
The Windows DNA (Distributed interNet Application) Architecture is Microsoft's current name for their view of where Windows is going and how it is going to participate with everyone.
MDAC (Microsoft Data Access Components) is Microsoft's current name for how UDA and DNA are manifested through miscellaneous technologies that Microsoft hopes will be easy and powerful, while both backwards compatible and forwards extensible.
There are three groups of technologies that compromise MDAC: ADO, OLE DB, and ODBC.
Here is a diagram of the UDA/MDAC Architecture:
ADO
ADO (ActiveX Data Objects) provides objects that developers can use in their consumer applications to interface with OLE DB which in turn connects to the data providers themselves. ADO is geared for hi-level, RAD (Rapid Application Development) environments; the lower end details of the data access process are left for OLE DB.
ADO utilizes COM (Component Object Model) objects. This makes ADO language independent. It also makes ADO good for client/server, n-tier, or Web database applications.
ADO is meant to replace two previous technologies: DAO (Data Access Objects) and RDO (Remote Data Objects).
- DAO are COM objects that access databases via Microsoft JET (Joint Engine Technology) which is used especially for Microsoft Access. JET can also access ISAM (Indexed Sequential Access Method) databases such as dBase, FoxPro, BTrieve, Paradox, and ODBC databases.
- RDO are COM objects that access database with ODBC. ODBC drivers by Microsoft can connect to the majority of SQL (Structured Query Language) databases.
Because ADO interfaces with OLE DB providers, ADO can interface many sources:
- JET
- ODBC
- Relational databases
- Non-relational data
- Text files
- Spreadsheets
- HTML pages
- Sources over networks and the Web
ADO has two newer components that extend ADO's abilities: ADOX (ADO eXtensions) and ADO MD (ADO MultiDimensional).
- ADOX provides objects that deal with security as well as schema creation and modification. Whereas ADO deals with database data, ADOX deals with database security objects (like users and groups) and database structure objects (like tables and procedures).
- ADO MD provides objects that deal with multidimensional data such as cubes. ADO MD objects include CubeDef and Cellset. ADO MD provides multidimensional views of data as opposed to tabular views of data. Multidimensional data is used in data mining. ADO MD would be used with OLE DB for OLAP.
OLE DB
OLE DB (Object Linking and Embedding DataBase) is a collection of COM objects that encapsulate database management system services for a number of services and data providers. OLE DB is to many kinds of data providers, as ODBC is to SQL data providers.
The four main OLE DB objects are Data Source, Session, Command, and Rowset.
- Data Source encapsulates functions and data about the particular data source, connection, and permissions.
- Session defines transactions within a connection.
- Command encapsulates functions that define data or acquire data, like queries.
- Rowset provides representation of data.
OLE DB can provide a bridge to many kinds of data providers including: SQL databases; indexed-sequential files; mainframe ISAM/VSAM and hierarchial databases; e-mail and file systems; text, graphical, and geographical data; spreadsheets; and custom business objects.
One screwy thing is that Microsoft calls its OLE DB drivers "OLE DB providers", which can easily be confused with the data sources, which Microsoft also calls "OLE DB providers". In order to avoid confusion, I will call the two "OLE DB drivers" and "data providers".
By default OLE DB ships with OLE DB drivers for the following data providers: ODBC drivers, Oracle, and Microsoft Jet. OLE DB also ships with the OSP (OLE DB Simple Provider) Toolkit which can make OLE DB drivers for other data providers.
OLE DB can provide a bridge to many kinds of services that can provide interfaces not natively supported by the data provider.
- Remoting allows client-side caching and thus "stateless" environments. That is the server does not have to be concerned about the data's state: whether the data is being changed, etc. Remoting reduces the number of roundtrips between user and server. [Remoting is supposed to supplant the RDS (Remote Data Service) component, which in turn supplanted the ADC (Advanced Data Connector).]
Remoting also allows remote invocation of object through HTTP and DCOM, so the user can be either a "thin" or "fat" client. - Persistence allows data to be saved as a local file.
- Data Shaping allows hierarchial views of data based on relation, parameter, and/or grouping.
- Cursor also provides client-side caching. It also typically translates sequential, forward only data to scrollable data.
- Relational typically makes it possible to query data providers that don't normally allow queries.
- Syncrhronization allows changes to the provider and refreshes data for the client.
- Trace intercepts messages between clients and providers for logging purposes.
OLE DB has a newer component that extends OLE DB's abilities: OLE DB for OLAP (On Line Analytical Processing). OLAP is multidimensional viewing of data as opposed to tabular viewing of data. EG: OLAP might look at a cube where one axis is for products, the second is for time, and the third sales. This is used in data mining.
Here are some OLE DB connection strings for common data sources (Note that OLE DB can also used ODBC connection strings):
| Data Source | OLE DB Connection String |
|---|---|
| Microsoft Access | Provider=Microsoft.Jet.OLEDB.4.0;Data Source=physical path to .mdb file |
| Microsoft SQL Server | Provider=SQLOLEDB.1;Data Source=path to database on server |
| Oracle | Provider=MSDAORA.1;Data Source=path to database on server |
| Microsoft Indexing Service | Provider=MSIDXS.1;Data Source |
ODBC
ODBC (Open DataBase Connectivity) is a C-level API (Application Programming Interface) that provides a common interface to a variety of SQL databases. Although the ODBC API is consistent for many types of SQL databases, an ODBC driver must be made for each kind of database used.
Developers can write code that uses the ODBC API directly but developers typically interface with ODBC drivers with tools such as DAO, RDO, and ADO.
Windows uses a ODBC Administrator to manage the various ODBC drivers and data sources available to the system. This administrator is usually found in the Control Panel as Data Sources, ODBC Data Sources, 32-bit ODBC, ODBC, or some variation of those names.
Windows typically ships with ODBC drivers for SQL Server, Oracle, Visual FoxPro, and Microsoft ODBC Desktop Database Drivers. The last driver provides access to Access, Excel, Paradox, dBASE, and text. Windows can also install an ISAM (Indexed Sequential Access Method) driver which support Lotus 123, Microsoft Exchange, HTML, and text.
Here are some ODBC connection strings for common data sources:
| Data Source Driver | ODBC Connection String |
|---|---|
| Microsoft Access | Driver={Microsoft Access Driver (*.mdb)};DBQ=physical path to .mdb file |
| SQL Server | Driver={SQL Server};Server=path to server |
| Oracle | Driver={Microsoft ODBC for Oracle};Server=path to server |
| Microsoft Excel | Driver={Microsoft Excel Driver (*.xls)};DBQ=physical path to .xls file;DriverID=278 |
| Microsoft Excel 97 | Driver={Microsoft Excel Driver (*.xls)};DBQ=physical path to .xls file;DriverID=790 |
| Paradox | Driver={Microsoft Paradox Driver (*.db)};DBQ=physical path to .db file;DriverID=26 |
| Text | Driver={Microsoft Text Driver (*.txt;*.csv)};DefaultDir=physical path to .txt file |
| Microsoft Visual FoxPro (with a database container) | Driver={Microsoft Visual FoxPro Driver};SourceType=DBC;SourceDb=physical path to .dbc file |
| Microsoft Visual FoxPro (without a database container) | Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDb=physical path to .dbf file |
Page Modified: (Hand noted: 2007-10-06 06:47:26Z) (Auto noted: 2007-11-17 06:47:52Z)