InstantADO

ActiveX Data Objects (ADO) broker for multiple database engines.

Overview

The InstantADO unit provides database persistence using Microsoft's ActiveX Data Objects (ADO), enabling access to various databases through OLE DB providers.

Supported Databases:

• Microsoft SQL Server
• Microsoft Access (Jet/ACE)
• Oracle
• MySQL
• IBM DB2
• Any database with an OLE DB provider

Key Features:

• Universal ADO interface for multiple databases
• ADOX-based or SQL-based database building
• Database compaction (MS Access)
• Navigational and SQL broker implementations
• Transaction support (provider-dependent)
• Connection string based configuration

Note: For new projects, consider InstantFireDAC which provides better performance and modern architecture.

Key Classes

TInstantADOConnectionDef

Connection definition for ADO broker.

Properties

Property	Type	Description
ConnectionString	string	ADO connection string or link file path
LinkFileName	string	Path to .udl file (Universal Data Link)

Methods

CreateConnection

function CreateConnection(AOwner: TComponent): TCustomConnection; override;

Creates TADOConnection from connection string or .udl file.

TInstantADOConnector

Main connector component for ADO.

Inheritance:

TComponent → TInstantConnector → TInstantConnectionBasedConnector → TInstantADOConnector

Properties

Property	Type	Description
Connection	TADOConnection	ADO connection component
ProviderType	TInstantADOProviderType	Detected database provider type (read-only)
CanTransaction	Boolean	Whether provider supports transactions (read-only)

Methods

BuildDatabase

procedure BuildDatabase(Scheme: TInstantScheme;
  BuildMethod: TInstantADOBuildMethod); overload;

Builds database using specified method:

• bmDefault: Choose automatically (ADOX for Access, SQL for SQL Server)
• bmADOX: Use ADOX (ActiveX Data Objects Extensions) COM objects
• bmSQL: Use SQL DDL statements

CompactDatabase

procedure CompactDatabase;

Compacts MS Access database file to reduce size and improve performance.

Only works with MS Jet/ACE providers.

GetProviderType

function GetProviderType: TInstantADOProviderType;

Detects database provider from connection string. Returns:

• ptMSJet - Microsoft Access (Jet/ACE)
• ptMSSQLServer - Microsoft SQL Server
• ptOracle - Oracle
• ptMySQL - MySQL
• ptIBMDB2 - IBM DB2
• ptUnknown - Other provider

Broker Classes

TInstantADOBroker

Base navigational broker for ADO (used for MS Access).

Inheritance:

TInstantBroker → TInstantRelationalBroker → TInstantADOBroker

Uses navigational access with TDataSet operations.

TInstantADOMSJetBroker

Specialized broker for Microsoft Access (Jet/ACE).

Inheritance:

TInstantBroker → TInstantRelationalBroker → TInstantADOBroker → TInstantADOMSJetBroker

Features:

• Navigational dataset-based access
• ADOX-based database building
• Database compaction support

TInstantADOMSSQLBroker

SQL broker for Microsoft SQL Server.

Inheritance:

TInstantBroker → TInstantSQLBroker → TInstantADOMSSQLBroker

Features:

• SQL-based parameterized queries
• Custom SQL generator for SQL Server
• BIT type for booleans

DataTypeToColumnType

function DataTypeToColumnType(DataType: TInstantDataType; Size: Integer): string; override;

SQL Server Data Type Mapping:

dtInteger   → INTEGER
dtFloat     → FLOAT
dtCurrency  → MONEY
dtBoolean   → BIT
dtString    → VARCHAR(n)
dtMemo      → TEXT
dtDateTime  → DATETIME
dtBlob      → IMAGE
dtDate      → DATETIME
dtTime      → DATETIME
dtEnum      → INTEGER

Resolver Classes

TInstantADOResolver

Base resolver for navigational access.

Inheritance:

TInstantCustomResolver → TInstantResolver → TInstantADOResolver

Uses TADODataSet for navigation-based operations.

TInstantADOMSJetResolver

Specialized resolver for MS Access with enhanced error handling.

TInstantADOMSSQLResolver

SQL-based resolver for MS SQL Server.

Inheritance:

TInstantCustomResolver → TInstantSQLResolver → TInstantADOMSSQLResolver

Query Classes

TInstantADOQuery

Query class for navigational brokers.

Inheritance:

TInstantQuery → TInstantRelationalQuery → TInstantADOQuery

Uses TADOQuery internally.

TInstantADOMSSQLQuery

SQL query class for MS SQL Server.

Inheritance:

TInstantQuery → TInstantSQLQuery → TInstantADOMSSQLQuery

SQL Generator

TInstantADOMSSQLGenerator

Custom SQL generator for MS SQL Server.

Inheritance:

TInstantSQLGenerator → TInstantADOMSSQLGenerator

Custom SQL generation:

• ALTER COLUMN syntax
• DROP COLUMN syntax
• DROP INDEX with table name

Types and Constants

TInstantADOProviderType

type
  TInstantADOProviderType = (ptUnknown, ptMSJet, ptMSSQLServer, ptOracle, ptMySQL, ptIBMDB2);

TInstantADOBuildMethod

type
  TInstantADOBuildMethod = (bmDefault, bmADOX, bmSQL);

Build methods:

• bmDefault: Automatic selection
• bmADOX: Use ADOX COM objects (recommended for Access)
• bmSQL: Use SQL DDL (recommended for SQL Server)

Usage Patterns

Basic Setup - MS SQL Server

Design-time configuration:

// 1. Drop TADOConnection on form
ADOConnection1.ConnectionString :=
  'Provider=SQLOLEDB.1;' +
  'Data Source=localhost;' +
  'Initial Catalog=MyDatabase;' +
  'Integrated Security=SSPI';
ADOConnection1.LoginPrompt := False;

// 2. Drop TInstantADOConnector on form
InstantADOConnector1.Connection := ADOConnection1;
InstantADOConnector1.Connected := True;

Basic Setup - MS Access

// 1. Drop TADOConnection on form
ADOConnection1.ConnectionString :=
  'Provider=Microsoft.ACE.OLEDB.12.0;' +
  'Data Source=C:\Data\MyDatabase.accdb;' +
  'Persist Security Info=False';
ADOConnection1.LoginPrompt := False;

// 2. Drop TInstantADOConnector on form
InstantADOConnector1.Connection := ADOConnection1;
InstantADOConnector1.Connected := True;

Programmatic Connection - SQL Server

uses
  InstantADO, Data.Win.ADODB;

procedure ConnectToSQLServer(const Server, Database: string);
var
  Connection: TADOConnection;
  Connector: TInstantADOConnector;
begin
  Connection := TADOConnection.Create(nil);
  Connection.ConnectionString := Format(
    'Provider=SQLOLEDB.1;' +
    'Data Source=%s;' +
    'Initial Catalog=%s;' +
    'Integrated Security=SSPI',
    [Server, Database]);
  Connection.LoginPrompt := False;

  Connector := TInstantADOConnector.Create(nil);
  Connector.Connection := Connection;
  Connector.IsDefault := True;
  Connector.Connected := True;

  ShowMessage(Format('Connected to %s on %s', [Database, Server]));
end;

Programmatic Connection - MS Access

procedure ConnectToAccess(const DatabasePath: string);
var
  Connection: TADOConnection;
  Connector: TInstantADOConnector;
begin
  Connection := TADOConnection.Create(nil);
  Connection.ConnectionString := Format(
    'Provider=Microsoft.ACE.OLEDB.12.0;' +
    'Data Source=%s;' +
    'Persist Security Info=False',
    [DatabasePath]);
  Connection.LoginPrompt := False;

  Connector := TInstantADOConnector.Create(nil);
  Connector.Connection := Connection;
  Connector.IsDefault := True;
  Connector.Connected := True;

  ShowMessage('Connected to ' + DatabasePath);
end;

Using Connection Definition

procedure ConnectViaConnectionDef;
var
  ConnDef: TInstantADOConnectionDef;
  Connector: TInstantADOConnector;
begin
  ConnDef := TInstantADOConnectionDef.Create(nil);
  try
    ConnDef.ConnectionString :=
      'Provider=SQLOLEDB.1;' +
      'Data Source=localhost;' +
      'Initial Catalog=MyDatabase;' +
      'Integrated Security=SSPI';

    Connector := TInstantADOConnector.Create(nil);
    try
      Connector.ConnectionDef := ConnDef;
      Connector.IsDefault := True;
      Connector.Connected := True;

      // Use connector...
    finally
      Connector.Free;
    end;
  finally
    ConnDef.Free;
  end;
end;

Using UDL File

// 1. Create .udl file using Windows Data Link Properties dialog
// 2. Use FILE NAME= prefix in connection string

procedure ConnectViaUDL(const UDLPath: string);
var
  Connection: TADOConnection;
  Connector: TInstantADOConnector;
begin
  Connection := TADOConnection.Create(nil);
  Connection.ConnectionString := 'FILE NAME=' + UDLPath;
  Connection.LoginPrompt := False;

  Connector := TInstantADOConnector.Create(nil);
  Connector.Connection := Connection;
  Connector.Connected := True;
end;

Building Database with ADOX (Access)

procedure BuildAccessDatabase(const DatabasePath: string;
  Model: TInstantModel);
var
  Connection: TADOConnection;
  Connector: TInstantADOConnector;
  Scheme: TInstantScheme;
begin
  Connection := TADOConnection.Create(nil);
  try
    Connection.ConnectionString := Format(
      'Provider=Microsoft.ACE.OLEDB.12.0;' +
      'Data Source=%s',
      [DatabasePath]);

    Connector := TInstantADOConnector.Create(nil);
    try
      Connector.Connection := Connection;

      Scheme := Connector.CreateScheme(Model);
      try
        // Use ADOX method for Access
        Connector.BuildDatabase(Scheme, bmADOX);
        ShowMessage('Database created: ' + DatabasePath);
      finally
        Scheme.Free;
      end;
    finally
      Connector.Free;
    end;
  finally
    Connection.Free;
  end;
end;

Building Database with SQL (SQL Server)

procedure BuildSQLServerDatabase(Connector: TInstantADOConnector;
  Model: TInstantModel);
var
  Scheme: TInstantScheme;
begin
  Connector.Connect;

  Scheme := Connector.CreateScheme(Model);
  try
    // Use SQL method for SQL Server
    Connector.BuildDatabase(Scheme, bmSQL);
    ShowMessage('Database created successfully');
  finally
    Scheme.Free;
  end;
end;

Compacting MS Access Database

procedure CompactAccessDatabase(Connector: TInstantADOConnector);
var
  SizeBefore, SizeAfter: Int64;
  DatabaseFile: string;
begin
  // Only works with MS Access
  if Connector.ProviderType <> ptMSJet then
    raise Exception.Create('Compact only supported for MS Access');

  DatabaseFile := Connector.DatabaseName;

  Connector.Disconnect;
  try
    SizeBefore := GetFileSize(DatabaseFile);

    // Compact database
    Connector.CompactDatabase;

    SizeAfter := GetFileSize(DatabaseFile);
    ShowMessage(Format('Database compacted: %d KB → %d KB',
      [SizeBefore div 1024, SizeAfter div 1024]));
  finally
    Connector.Connect;
  end;
end;

Detecting Provider Type

procedure ShowProviderInfo(Connector: TInstantADOConnector);
var
  ProviderName: string;
begin
  case Connector.ProviderType of
    ptMSJet:        ProviderName := 'Microsoft Access';
    ptMSSQLServer:  ProviderName := 'Microsoft SQL Server';
    ptOracle:       ProviderName := 'Oracle';
    ptMySQL:        ProviderName := 'MySQL';
    ptIBMDB2:       ProviderName := 'IBM DB2';
  else
    ProviderName := 'Unknown';
  end;

  ShowMessage(Format('Connected to: %s' + sLineBreak +
                    'Transactions: %s',
    [ProviderName, BoolToStr(Connector.CanTransaction, True)]));
end;

Transaction Management

procedure PerformTransactionalOperation;
begin
  // Check if provider supports transactions
  if not InstantADOConnector1.CanTransaction then
    raise Exception.Create('Provider does not support transactions');

  InstantADOConnector1.StartTransaction;
  try
    // Perform operations
    Obj1.Store;
    Obj2.Store;
    Obj3.Delete;

    InstantADOConnector1.CommitTransaction;
  except
    InstantADOConnector1.RollbackTransaction;
    raise;
  end;
end;

Connection Strings

MS SQL Server

Windows Authentication:

Provider=SQLOLEDB.1;Data Source=localhost;Initial Catalog=MyDB;Integrated Security=SSPI

SQL Server Authentication:

Provider=SQLOLEDB.1;Data Source=localhost;Initial Catalog=MyDB;User ID=sa;Password=mypass

SQL Server Native Client:

Provider=SQLNCLI11;Server=localhost;Database=MyDB;Trusted_Connection=yes

MS Access

Access 2007-2019 (.accdb):

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\MyDB.accdb;Persist Security Info=False

Access 97-2003 (.mdb):

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\MyDB.mdb;Persist Security Info=False

With Password:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\MyDB.accdb;Jet OLEDB:Database Password=mypass

Oracle

Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword

MySQL

Provider=MySQLProv;Data Source=localhost;User Id=root;Password=mypass;Database=mydb

IBM DB2

Provider=IBMDADB2;Data Source=MyDB2;User ID=myuser;Password=mypass

Best Practices

1. Use Appropriate Build Method

// MS Access: Use ADOX
Connector.BuildDatabase(Scheme, bmADOX);

// SQL Server: Use SQL
Connector.BuildDatabase(Scheme, bmSQL);

// Unknown: Use Default
Connector.BuildDatabase(Scheme, bmDefault);

2. Compact Access Databases Regularly

// Schedule compaction weekly/monthly
if Connector.ProviderType = ptMSJet then
begin
  Connector.Disconnect;
  try
    Connector.CompactDatabase;
  finally
    Connector.Connect;
  end;
end;

3. Check Transaction Support

if Connector.CanTransaction then
begin
  // Use transactions
  Connector.StartTransaction;
  try
    // Operations
    Connector.CommitTransaction;
  except
    Connector.RollbackTransaction;
    raise;
  end;
end
else
begin
  // Provider doesn't support transactions
  // Operations execute immediately
end;

4. Use Connection Pooling

// Enable connection pooling in connection string
Connection.ConnectionString :=
  'Provider=SQLOLEDB.1;Data Source=localhost;Initial Catalog=MyDB;' +
  'Integrated Security=SSPI;OLE DB Services=-2';  // Enable pooling

5. Set LoginPrompt to False

Connection.LoginPrompt := False;
// Prevents modal dialog on connection failure

6. Use Integrated Security for SQL Server

// Windows Authentication (recommended)
Connection.ConnectionString :=
  'Provider=SQLOLEDB.1;Data Source=localhost;Initial Catalog=MyDB;Integrated Security=SSPI';

7. Close Connections Properly

try
  // Use connector
finally
  Connector.Disconnect;
  Connection.Free;
end;

Troubleshooting

"Provider cannot be found"

Problem: ADO error -2147467259.

Solutions:

• Install Microsoft Access Database Engine (for ACE provider)
• Use correct provider name in connection string
• Check 32-bit vs 64-bit provider availability

"Cannot open database"

Problem: Access denied or file not found.

Solutions:

// Check file exists
if not FileExists(DatabasePath) then
  ShowMessage('Database file not found: ' + DatabasePath);

// Check file permissions
// Ensure write access for .accdb/.mdb files

Transaction Not Supported

Problem: Provider doesn't support transactions.

Solution:

if not Connector.CanTransaction then
  ShowMessage('Warning: Provider does not support transactions');

Compact Database Fails

Problem: CompactDatabase raises exception.

Cause: Only works with MS Jet/ACE providers.

Solution:

if Connector.ProviderType <> ptMSJet then
  raise Exception.Create('Compact only available for MS Access');

Connection String Format Error

Problem: Invalid connection string.

Solution:

// Use connection string builder or .udl file
// Test connection string with ADO Test Tool

32-bit vs 64-bit Provider Mismatch

Problem: Provider not found in 64-bit application.

Solutions:

• Install 64-bit Access Database Engine
• Or compile application as 32-bit
• Or use different provider (SQL Server Native Client)

Access Database Locked

Problem: "Could not use ''; file already in use".

Solution:

// Close all connections
Connector.Disconnect;

// Delete .ldb lock file
DeleteFile(ChangeFileExt(DatabasePath, '.ldb'));

Performance Optimization

1. Use SQL Broker for SQL Server

// TInstantADOMSSQLBroker is faster than navigational TInstantADOMSJetBroker
// Automatically selected for SQL Server

2. Enable Connection Pooling

Connection.ConnectionString := ConnectionString + ';OLE DB Services=-2';

3. Use Prepared Statements

// Queries automatically use prepared statements via TADOQuery

4. Set Cursor Location

Connection.CursorLocation := clUseClient;  // For read-only queries
// OR
Connection.CursorLocation := clUseServer;  // For updates

5. Compact Access Databases

// Regular compaction improves performance
Connector.CompactDatabase;

Limitations

MS Access Limitations

1. File size limit: 2 GB
2. Concurrent users: ~10-15 users max (performance degrades)
3. No stored procedures: Limited server-side logic
4. Database bloat: Requires regular compaction

Provider Limitations

1. Transaction support: Provider-dependent
2. Data type support: Varies by provider
3. Performance: Slower than native APIs

ADO Limitations

1. Windows only: ADO not available on Linux/macOS
2. 32/64-bit mismatch: Provider bitness must match application
3. COM dependencies: Requires Windows COM infrastructure

Migration Path

From ADO to FireDAC

// ADO (old)
uses InstantADO;
Connector := TInstantADOConnector.Create(nil);
Connector.Connection.ConnectionString := '...';

// FireDAC (new, recommended)
uses InstantFireDAC;
Connector := TInstantFireDACConnector.Create(nil);
Connector.Connection.Params.Text := ConvertADOConnectionString(...);

Benefits of migrating to FireDAC:

• Better performance
• Cross-platform support (Windows, Linux, macOS)
• More databases supported
• Modern architecture

See Also

• InstantFireDAC - Recommended for new projects
• InstantBrokers - Broker architecture
• InstantPersistence - Persistence infrastructure
• InstantDBBuild - Database building
• InstantDBEvolution - Schema evolution

Source Code

File: InstantADO.pasLocation: Source/Brokers/ADO/

Summary

The InstantADO broker provides database persistence using Microsoft's ADO technology, supporting multiple databases through OLE DB providers.

Key Features:

• Multiple database support (SQL Server, Access, Oracle, MySQL, DB2)
• ADOX or SQL-based database building
• Database compaction for MS Access
• Navigational and SQL broker implementations

Best for:

• Legacy Windows applications using ADO
• Microsoft Access databases
• SQL Server applications (with limitations)

Limitations:

• Windows only
• Slower than native providers
• 32/64-bit provider dependencies
• Limited transaction support (provider-dependent)

Recommendation: For new projects, use InstantFireDAC which provides better performance, cross-platform support, and modern architecture.

Data Types (SQL Server):

• Booleans → BIT
• Strings → VARCHAR(n)
• Memo → TEXT
• Currency → MONEY
• Blob → IMAGE
• DateTime → DATETIME