Understanding Universal Data Access, OLE DB, and …

1 30 CHAPTERIn this chapterUnderstanding Universal DataAccess, OLE DB, and ADOIn this chapterGaining a Perspective on Microsoft data access Components1258 Creating the Object Browser to Display ADO Properties, Methods, and Events1271 Working with the the the Advantage of Disconnected Recordsets1312 Programming StreamObjects1323 Exploring the Sample Project1327Tr oubleshooting1330In the Real World Why Learn ADO Programming?133038 0789729520 CH30 8/22/03 5:08 PM Page 12571258 Chapter 30 Understanding Universal data access , OLE DB, and ADOG aining a Perspective on Microsoft data AccessComponentsIntegrated data management is the key to access s success in the desktop RDBMS andclient/server front-end market. access and its wizards let you create basic data -bound forms,reports, and pages with minimal effort and little or no VBA programming. Linked tablesprovide dynamic access to a wide range of data sources. As your access applications growlarger and more complex, automation with VBA code in class and public modules becomesessential.

2 When networked access applications gain more users, performance may suffer as aresult of Jet record-locking issues or multiple connections to client/server back performance with increasing user load is a symptom of lack of scalability requires VBA code to manage your application s database advanced chapter shows you how to write the VBA code that s required to improve thescalability of access front ends. You also learn how to use the Streamobject to generateXML data documents from SQL Server 2000 s FOR XML 2003 continues Microsoft s emphasis on Universal data access for VBA and VisualBasic programmers. Microsoft wants access developers to abandon Jet s data AccessObjects (DAO), access 97 s ODBCD irect, and the venerable Open Database Connectivity(ODBC) Application Programming Interface (API) in favor of a collection of ComponentObject Model (COM) interfaces called OLE DB and ActiveX data Objects (ADO). Toencourage access power users and developers to adopt OLE DB and ADO, all traditionalMicrosoft database technologies (referred to by Microsoft as downlevelor legacy, synonymsfor obsolete ) are destined for maintenance mode.

3 Maintenance mode is a technologicalpurgatory in which Microsoft fixes only the worst bugs and upgrades occur infrequently, ifever. In 1999, OLE DB, ADO, and, for Jet programmers, ActiveX data Object Extensions(ADOX), became Microsoft s mainstream data access s primary goals for Universal data access were to Provide the capability to accommodate less common data types unsuited to SQLqueries, such as directory services (specifically Active Directory), spreadsheets, emailmessages, and file systems Minimize the size and memory consumption of the dynamic link libraries (DLLs)required to support data access on Internet and intranet clients Reduce development and support costs for the multiplicity of Windows-based dataaccess architectures in common use today Extend the influence of COM in competition with other object models, primarilyCommon Object Request Broker Architecture (CORBA) and its derivativesThis chapter introduces you to the fundamentals of Universal data access and MicrosoftData access Components (MDAC).

4 MDAC makes connecting to databases with OLE DBpractical for access users and developers. MDAC includes ADO and ADOX for conven-tional relational data , plus ADOMD for multidimensional expressions (MDX) to create andmanipulate data 0789729520 CH30 8/22/03 5:08 PM Page 12581259 Gaining a Perspective on Microsoft data access ComponentsRedesigning from the Bottom Up with OLE DBToaccommodate the widest variety of data sources, as well as to spread the gospel of COMand Windows XP/2000+ s COM+, Microsoft s data architects came up with a new approachto data connectivity OLE DB. OLE DB consists of three basic elements: data providersthat abstract information contained in data sources into a tabular (row-column) format called a rowset. Microsoft currently offers native OLE DB dataproviders for Jet, SQL Server, IBM DB2, IBM AS/400 and ISAM, and Oracle data -bases, plus ODBC data sources. (Only Microsoft SNA Server installs the providers forIBM data sources.)

5 Other Microsoft OLE DB providers include an OLE DB SimpleProvider for delimited text files, the MSPersist provider for saving and openingRecordsets to files (called persisted Recordsets), and the MSDataShape provider for creat-ing hierarchical data sets. The MSDataShape provider also plays an important role inADP and when using VBA to manipulate the Recordset of access forms and SQL Server Analysis Services (formerly OLAP Services) generates data cubesfrom online sources, such as transactional databases. Office 2003 installs other supporting files for MDX and data cubes. Microsoft provides OLE DB for OLAPand the PivotTable Service to enable Excel 2003 PivotTables to manipulate data and PivotTable services are beyond the scope of this the list of OLE DB data sources installed on your computer, open project, and choose File, Get External data , Link Tables to start the LinkTable Wizard. With the Linked Server option selected in the first dialog, click Next toopen the Select data Source dialog, and double-click the +Connect to New file to open the second Wizard dialog.

6 With the Other/Advanced itemselected in the data source list, click Next to open the data Link Properties dialog. TheProviders page lists all currently installed OLE DB data providers. Click Cancel three timesto return to the Database window. data consumersthat display and/or manipulate rowsets, such as access applicationobjects or OLE DB service providers. Rowsetis the OLE DB object that ADO convertsto a Recordsetobject. data services(usually called OLE DB service providers) that consume data from providersand, in turn, provide data to consumers. Examples of data services are SQL queryprocessors and cursor engines, which can create scrollable rowsets from forward-onlyrowsets. A scrollable cursor lets you move the record pointer forward and backward inthe Datasheet view of a Jet or SQL Server 0789729520 CH30 8/22/03 5:08 PM Page 12591260 Chapter 30 Understanding Universal data access , OLE DB, and ADOF igure illustrates the relationship between OLE DB data providers, data consumers,and data services within Microsoft s Universal data access architecture.

7 You should under-stand the relationships between these objects, because Microsoft commonly refers to themin ADO documentation, help files, and Knowledge Base articles. Database front ends writ-ten in C++ can connect directly to the OLE DB interfaces. High-level languages, such asVBA, use ADO as an intermediary to connect to OLE DB s COM interfaces. ,which implements ADO , has a memory footprint of about 327KB, about 60% s ++ DatabaseFront-EndAccess 2000+Application ObjectsActiveX data Objects (ADO)ADO Extensions (ADOX)OLE DBSQL QueryProcessorDistributedQueryEngineCurs orEngineOLE DBSQLRDBMSFileSystemJet (ISAM)DatabasesSpread-sheetE-mailSystemD irectoryServiceData ProvidersData ConsumersServiceProvidersOLE DBAutomationWrapperFigure diagram showsthe relationshipsbetween front-endapplications, ADO andADOX, and OLE DBservice and support files install in your \Program Files\System\Ado folder. If you re runningWindows XP/2000+, the ADO support files are subject to Windows File Protection (WFP),which places a copy of the file in the DLL cache and prevents you from permanently delet-ing or moving the ADO support files.

8 WFP also prevents unruly installation programs fromoverwriting the ADO support files with an earlier or corrupt (hacked) ADO support file names have a version number, as in ; thestrange versioning of these files is required for backward compatibility with applicationsthat used very early versions of 0789729520 CH30 8/22/03 5:08 PM Page 12601261 Gaining a Perspective on Microsoft data access Components For more information on the use of RDS with DAP, see Enabling Private or Public Internet access , p. OLE DB Interfaces to ADOY ouneed to know the names and relationships of OLE DB interfaces to ADO objects,because Microsoft includes references to these interfaces in its technical and white papers onOLE DB and ADO. Figure illustrates the correspondence between OLE DB interfacesand the highest levels of the ADO also supports Remote data Services (RDS, formerly Advanced DatabaseConnector, or ADC). RDS handles lightweight browser-basedapplications; RDS, which commonly is used for three-tier, Web-based applications, isrequired to make data access Pages (DAP) accessible safely over the ( )CreateCommand( )Execute( )Open Rowset ( ) DB (C++)ActiveX data Objects (ADO)Figure diagram illus-trates the correspon-dence between OLEDB interfaces andADO OLE DB specification defines a set of interfaces to the following objects: DataSourceobjects provide a set of functions to identify a particular OLE DB dataprovider, such as the Jet or SQL Server provider, and determine whether the caller hasthe required security permissions for the provider.

9 If the provider is found and authen-tication succeeds, a connection to the data source results. Sessionobjects provide an environment for creating rowsets and isolating transactions,especially with Microsoft Transaction Server (MTS), which runs under Windows COM+ components of Windows 2000+ provide MTS services. Commandobjects include sets of functions to handle queries, usually (but not necessarily)in the form of SQL statements or names of stored 0789729520 CH30 8/22/03 5:08 PM Page 12611262 Chapter 30 Understanding Universal data access , OLE DB, and ADO Rowsetobjects can be created directly from Sessionobjects or as the result of executionof Commandobjects. Rowsetobjects deliver data to the consumer through the maps the four OLE DB objects to the following three top-level Automation objectsthat are familiar to access programmers who ve used ODBCD irect: Connectionobjects combine OLE DB s DataSourceand Sessionobjects to specify theOLE DB data provider, establish a connection to the data source, and isolate transac-tions to a specific connection.

10 The Executemethod of the canreturn a forward-only Commandobjects are directly analogous to OLE DB s Commandobject. accept an SQL statement, the name of a table, or the name of a stored proce-dure. Commandobjects are used primarily for executing SQL UPDATE, INSERT, DELETE, andSQL data Definition Language (DDL) queries that don t return records. You also canreturn an executing an Recordsetobjects correspond to OLE DB s Rowsetobjects and have properties andmethods similar to access 97 s ODBCD irect Recordset. A Recordsetis an in-memoryimage of a table or a query result ADODB prefix, the short name of the ADO type library, explicitly identifies ADO objectsthat share object names with DAO (Recordset) and DAO s ODBCD irect (ConnectionandRecordset). For clarity, all ADO code examples in this book use the ADOX accessible to VBA, you must add a reference to Microsoft ADO for DDL and Security to your application.