efg's Delphi Reference Library: ActiveX Data Objects (ADO)

These Tech Notes assume Delphi 5 and ADOExpress are installed in a Windows 98 environment.
The motivation for these notes was the lack of documentation provided by Borland on this topic.

1. UDL files -- Microsoft Data Link Files
An ADO database connection is established using a Microsoft Data Link. A Data Link is stored in a UDL file, which usually is created manually in Windows 98. When a UDL files is not directly used, the same information is stored in the properties of a TADOConnection component in a DFM (form) file. These instructions show how to manually or programmatically create UDL files.

2. DBF Files -- With and Without ADO
These instructions show how to connect to a DBF (dBase) file with or without the ADO. The ADOTable component is used to access the data via ADO. Images can only be accessed when ADO is not used.

3. Delphi 5, ADOExpress, Access and Images
Many PCs with Microsoft Office have a sample Access database called "Northwind," which is useful for database experiments. This project shows that while the "normal" data can be manipulated in Northwind using Delphi and ADOExpress, there is no easy way (without yet-another-third-party-add-on) to display the images in this Access database using only Delphi 5 and ADOExpress. However, a crude "hack" is shown that does extract a TBitmap from the Photo TBlobField. More must be understood about this "hack" before the technique can be considered reliable and used in a "real" application. An ADOQuery component and an SQL query are used to access the data via ADO.

4. SQL Query Using ADODataSet
These instructions show how to use an ADODataSet component and an SQL Query to connect with a dBase database. This Tech Note fills in many details for "A Practical ADO Primer" example in Mastering Delphi 5, pp. 562-565.

5. ADO Master/Detail and File Locking
These instructions show how to use ADO to connect to an Access database over a local area network. A Master/Detail relationship is demonstrated. Multiple applications accessing the same database are explored. Database locking (optimistic versus pessimistic) is demonstrated. Part of this example is based on Mastering Delphi 5, Figure 12.8, p. 574.

6. Running ADO Applications in Windows 95
ADO is built into Windows 98, and will be built into future versions of Windows, including Windows 2000, but there is no support for ADO in Windows 95. DCOM for Win 95 and MDAC 2.1, or later, must be installed in Windows 95 before running Delphi ADO applications.

7. Briefcase: UDL and ADTG
The Briefcase demo program, distributed as part of ADO Express, looks fairly simple, but there are some not-so-obvious steps that are needed to reproduce the program. The Briefcase program establishes an ADO connection using a Data Link File (a UDL file). When the database connection is dropped, data can be accessed locally in an Advanced Data Table Gram (ADTG) file and later written to the database.

Books

ADO 2.1 Programmer's Reference
by Dave Sussman (non-Delphi)

Delphi Developer Guide to ADO by Alex Fedorov

Mastering Delphi 5 by Marco Cantù gives a good overview, but the "Practical ADO Primer" is a bit terse for a beginner. See Chapter 12, "Using ADO," pp. 557-584. Topics include:

- ADO and OLE DB	- Indexes and Sorting
- Delphi 5 ADO Components	- Filtering
- From Paradox to Access	- Snapshot of the Data (Briefcase model)
- Copying Tables	- Finding, Summing, and Locking Records
- Master/Detail Structures	- Handling Transaction in ADO
- Cursors and Optimization

Delphi 5 Developer's Guide by Teixeira and Pacheco is a bit terse about ADO (but has a huge amount of information on a large number of Delphi topics)..
ActiveX Data Objects (ADO), pp. 1211-1217. Topics include:
- ADOExpress Components
- Connecting to an ADO Data Store (Building UDL Files)
- ADO Deployment

Building Distributed Applications with ADO by Martiner, Herion and Falino

Borland

Borland Delphi 5 Developer's Guide
Chapter 23, Working with ADO Components, pp. 23-1 - 23-28

Articles (D5)

Pictures inside a database -- Working with BLOBs. Storing pictures in Access.
http://delphi.about.com/compute/delphi/library/weekly/aa030601a.htm

A Practical Guide to ADO Extensions: Part I, Using ADOX and JRO
www.delphizine.com/features/2000/10/di200010af_f/di200010af_f.asp

A Practical Guide to ADO Extensions: Part II, ADO Multidimensional
www.delphizine.com/features/2000/12/di200012jm_f/di200012jm_f.asp

"Delphi Does ADO -- A New Way to Get to Data" by Bill Todd in Delphi Informant
www.delphiinformant.com/features/1999/10/di199910bt_f/di199910bt_f.asp

"Creating a Microsoft Excel Spreadsheet Viewer Using Delphi 5's ADOExpress" by Scott Strool
http://community.borland.com/article/1,1410,20066,00.html

Articles (pre-D5)

Accessing Databases Using ADO and Delphi (D4)
http://community.borland.com/soapbox/techvoyage/article/1,1795,10270,00.html

Much ADO about the Web: Using ActiveX Data Objects from Delphi Applications, Delphi Informant, Dec 1998

Microsoft

HOWTO: Get XML Representation Of an ADO Recordset in Visual Basic
http://support.microsoft.com/support/kb/articles/q252/7/67.asp

Microsoft Documentation: UDA / OLE DB / ADO / ODBC
www.microsoft.com/data/doc.htm

ActiveX Data Objects Start Page
http://msdn.microsoft.com/library/psdk/dasdk/ados4piv.htm

ADO Programming Model
www.microsoft.com/data/ado/prodinfo/progmod.htm

Installing MDAC Q&A
www.microsoft.com/data/mdac21info/MDACinstQ.htm

Release Manifest for MDAC 2.1
www.microsoft.com/data/MDAC21info/MDAC21GAmanifest.htm

Microsoft Data Access Components (MDAC) SDK
www.microsoft.com/data/doc.htm

How To Use the ADO Shape Command
http://support.microsoft.com/support/kb/articles/Q189/6/57.ASP

ADO FAQ
http://support.microsoft.com/support/kb/articles/Q183/6/06.asp

Microsoft Data Access Components End-User License Agreement
www.microsoft.com/data/eulamdac21.htm

Defining and Retrieving a Database’s Schema
http://msdn.microsoft.com/library/psdk/dasdk/migr5ue9.htm

Migrating from DAO to ADO: Using ADO with the Microsoft Jet Provider
(good tutorial, but examples are in VB))
http://msdn.microsoft.com/library/techart/daotoadoupdate.htm

Non-Delphi Resources

Introduction to ActiveX Data Objects
www.takempis.com/adointro.asp

ADO FAQs
www.able-consulting.com/ADO_Faq.htm

ADO Technology
www.able-consulting.com/tech.htm

ADO Resources
www.vb-bookmark.com/vbADO.html [Visual Basic perspective]

Vendors/Products

Diamond Tools

Diamond ADO is a Delphi component library, that provides high-speed performance when working with any OLE DB provider or ODBC data source. Diamond ADO uses ActiveX Data Objects 2.1 (ADO) to access data without requiring the BDE. Using Diamond ADO you can connect to any OLE DB provider. Currently OLE DB providers exist for SQL Server, Oracle, Access, ODBC, Active Directory Services and the Index Server. www.islamov.com/diamondado

Access

ADOX: Creating new Access Database
www.geocities.com/SiliconValley/Lakes/1636/ADOX1.htm

See ADOX_TLB before trying to compile this example.

Access JRO (Jet and Replication Objects). See Roger Morton's UseNet Post response to the question "How Do I Compact / Repair Access Databases via ADO":

"A complete ADO installation includes not only ADO itself but also ADOX, primarily for creating and modifying dbs at the schema level, and also an Access-specific bit called Jet and Replication Objects. If you import the JRO type library into Delphi then you'll have access to the CompactDatabase method of the Jet Engine."

Acronyms

ADO	ActiveX Data Objects is an automation-based inteface for accessing data. Application programming interface to MDAC. ADO applications can be run without the BDE. If installed, look in C:\Program Files\Common Files\System\ADO\ADOReadMe.TXT for supplementary information. To install, see ADO Redistributable below. ADO 2.0 -- Has OLE DB provider for Access 97 and DBF files. ADO 2.1 -- Has OLD DB provider for Access 2000.
ADOX	ADO Extensions for Data Definition and Security. ADOX exposes additional objects for creating, modifying, and deleting schema objects such as tables and procedures. It also includes security objects to maintain users and groups and to grant and revoke permissions on objects. If installed, look in C:\Program Files\Common Files\System\ADO\ADOXReadMe.TXT for supplementary information.
DAO	Data Access Objects. Microsoft technology that preceded ADO. DAO 3.51 -- Access 97 mdb file, direct use of DBF files. DAO 3.60 -- Access 2000 mdb file.
BDE	Borland Database Engine. Not needed if using an ADO database connection.
MDAC	Microsoft Data Access Components includes ADO, ODBC and OLE DB. If installed, look in C:\Program Files\Common Files\System\ADO\MDACReadMe.TXT for supplementary information. To install, see ADO Redistributable below.
ODBC	Open Database Connectivity. ODBC is included in MDAC only for backward compatibility. ODBC drivers will likely be replaced by OLE DB providers in the future.
OLE DB	System-level interface to MDAC.
UDA	Universal Data Access is part of Microsoft's strategy of providing access to data in both relational and non-relational data stores.

ADO redistributable

ADO is built into Windows 98 and will be built into future versions of Windows, including Windows 2000.

To find out if ADO is installed, look for
C:\Program Files\Common Files\System\ADO\MDACReadMe.TXT.

To install ADO support inWindows 95, see the Tech Note "Running ADO Applications in Windows 95."

ADOX_TLB

Before you can have a "USES ADOX_TLB" clause, do the following:

Start Delphi 5
Project
Import Type Library
Choose "Microsoft ADO Ext 2.1 for DDL and Security (Version 2.1)"
Change "TTable" to "TADOXTable"
Change "TColumn" to "TADOXColumn"
Change "TIndex" to "TADOXIndex"
Press Install button
Press OK once and Yes twice
File | Close All | Yes

CreateUDLFile

Procedure in ADODB.PAS for creating UDL file programmatically. See Tech Note.

Data Cache

Shannon Broskie's E-mail with suggestion about using ADO to 'cache' data only while the program is running, which then allows one to use ADO's capabilties of filtering and sorting along with the added bonus of ADO's ability to save data directly out to XML.

DataLinkDir

Function in ADODB.PAS that returns the string stored in the registry of the default location for data link files, usually,
C:\Program Files\Common Files\System\OLE DB\Data Links

Mark Edington's (Borland) UseNet Post about DataLinkDir function

GetDataLinkFiles

Function in ADODB.PAS. <need better definition here>

GetProviderNames

Procedure in ADODB.PAS. <need better definition here>

PromptDataLinkFile

Function in ADODB.PAS. <need better definition here>

PromptDataSource

Function in ADODB.PAS. <need better definition here>

TADOCommand examples

Create Access 97 Database	See ADOX_TLB before trying to compile this example. USES ADOX_TLB, ComObj; ... CONST BlankDatabase = 'C:\Lab\CreateDB\blank.mdb'; ... // Adapted from "ADOX: Creating a new Access database" // http://www.geocities.com/SiliconValley/Lakes/1636/ADOX1.htm // Also see Roger Morton's 23 Jan 2000 UseNet Post to // borland.public.delphi.database.ado procedure TFormCreateDB.ButtonCreateDatabaseClick(Sender: TObject); VAR Catalog : _Catalog; DataSource: STRING; begin Catalog := CreateCOMObject(StringToGUID('ADOX.Catalog')) AS _Catalog; DataSource := 'Provider=Microsoft.Jet.OLEDB.4.0;' + 'Data Source=' + BlankDatabase + ';Jet OLEDB:Engine Type=4'; // Needed for Access 97 DB IF FileExists(BlankDatabase) THEN DeleteFile(BlankDatabase); // Create new Access database Catalog.Create(DataSource) end;
SQL Create Table	ADOCommand.CommandText := 'CREATE TABLE NameList ' + ' ( ' + ' IDKey TEXT(10) PRIMARY KEY, ' + ' NameLast TEXT(24) NOT NULL, ' + ' NameFirst TEXT(20) NOT NULL, ' + ' NameMiddleInitial TEXT(1), ' + ' Notes MEMO, ' + ' Picture LongBinary, ' + ' BirthDate DATE ' + ' )'; ADOCommand.Execute;
SQL Drop Table	ADOCommand.CommandText := 'DROP TABLE NameList'; ADOCommand.Execute
SQL Create Index	ADOCommand.CommandText := 'CREATE UNIQUE INDEX NameIndex ON NameList ' + '( ' + ' NameLast ASC, ' + ' NameFirst ASC ' + ')'; ADOCommand.Execute;
SQL Drop Table	ADOCommand.CommandText := 'DROP INDEX NameIndex ON NameList'; ADOCommand.Execute;

Version

The ADOConnection.Version string returns '2.1' in Delphi 5.