Adonis Component Suite FAQ

Last updated May 10, 2024 Copyright © 1998-2024 WINSOFT. All rights reserved.

ADO

How can I determine ADO version?

try
  ShowMessage('ADO version ' + GetADOVersion + ' installed');
except
  ShowMessage('ADO not installed');
end;

You can also use MDAC Detect utility supplied with Adonis.

Or use MDAC Installed Version type library (ADO 2.5 or higher required):

uses MDACVer;

ShowMessage(CoVersion.Create.String_);

What is the newest ADO version?

Windows DAC 10.0.22000.1

Where can I find ADO information?

Windows Data Access Components...

Microsoft Data Developer Center

How can I get ADO error information?

Place this code to OnPostError, OnADOError, etc.:

uses ADO;

with ADODataSet.Errors do
  for i := Count - 1 downto 0 do
    with Item[i] do
      ShowMessage(
        ' Description: ' + Description + #13 +
        ' Number: ' + IntToStr(Number) + #13 +
        ' Native: ' + IntToStr(NativeError) + #13 +
        ' Source: ' + Source + #13 +
        ' State: ' + SQLState + #13);

with ADODatabase.Errors do
  for i := Count - 1 downto 0 do
    with Item[i] do
      ShowMessage(
        ' Description: ' + Description + #13 +
        ' Number: ' + IntToStr(Number) + #13 +
        ' Native: ' + IntToStr(NativeError) + #13 +
        ' Source: ' + Source + #13 +
        ' State: ' + SQLState + #13);

C++Builder code:

_di_Errors Errors = ADODatabase->Handle->Errors;
for (int i = Errors->Count - 1; i >= 0; --i)
  ShowMessage(
    " Description: " + Errors->Item[i]->Description + "\n" +
    " Number: " + IntToStr(Errors->Item[i]->Number) + "\n" +
    " Native: " + IntToStr(Errors->Item[i]->NativeError) + "\n" +
    " Source: " + Errors->Item[i]->Source + "\n" +
    " State: " + Errors->Item[i]->SQLState + "\n");

How can I avoid showing ADO exceptions?

procedure TForm1.ADODatasetADOError(DataSet: TDataSet; E: EDatabaseError; var Action: TDataAction);
begin
  Action := daAbort;
end;

How can I create and use TADODataSet in C++Builder application?

// this line fixes an incorrectly generated ADO header file
__interface Ado::_Recordset : public Recordset15 {};

void __fastcall TFormAdonisDemo::Button1Click(TObject *Sender)
{
  TADODataSet *Table = new TADODataSet(this);

  Table->DatabaseProvider = "Microsoft.Jet.OLEDB.4.0";
  Table->DatabaseConnect = "Data Source = simple.mdb";
  Table->CommandType = ctTable;
  Table->TableName = "COUNTRY";

  Table->Active = true;

  Table->Insert();
  Table->FieldByName("NAME")->AsString = "MyName";
  Table->FieldByName("AREA")->AsInteger = 10000;
  Table->Post();

  Table->Close();

  delete Table;
}

How can I create ADO recordset in C++Builder application?

// this line fixes an incorrectly generated ADO header file
__interface Ado::_Recordset : public Recordset15 {};

void __fastcall TFormAdonisDemo::Button1Click(TObject *Sender)
{
  _di__Recordset Recordset = CoRecordset::Create(NULL);
  Recordset->Open(Variant("SELECT * FROM COUNTRY"), Variant("Provider= Microsoft.Jet.OLEDB.4.0;Data Source=simple.mdb"), adOpenKeyset, adLockOptimistic, adCmdText);

  ShowMessage("Record count = " + IntToStr(Recordset->RecordCount));
}

How can I drop a table?

ADODatabase.ExecSQL('DROP TABLE MyTable');

How can I solve 'CoInitialize has not been called' error in a multithreaded application?

Call CoInitialize and CoUninitialize in each thread:

uses ActiveX;

procedure MyThread.Execute;
begin
  CoInitialize(nil);
  try
    // your code
  finally
    CoUninitialize;
  end;
end;

How can I use multiword table names?

Enclose multiword names in [ ] brackets:

ADODataSet1.SQL.Text := 'SELECT * FROM [Financial Reports]';

Why is Adonis slow on opening large tables?

Adonis default behaviour is to use client cursor as it offers more functionality than server cursor. Client cursor loads entire recordset to memory. If your recordset is large, use server cursor - it will only load a small number of records, specified by CacheSize, at one time.

CursorLocation := clServer;

Alternatively you can use asynchronous fetching of the recordset.

How can I find a value containing ' (quote) character?

if ADODataSet.Locate('FieldName', 'er''''ik', []) then
  ShowMessage('Find');

How can I read ADO properties?

var
  i: Integer;
  Value: string;

with ADODataset.Recordset.Properties do
  for i := 0 to Count - 1 do
  begin
    try Value := Item[i].Value; except Value := '' end;
    ShowMessage(Item[i].Name + ':' + Value);
  end;

How can I retrieve parameters from stored procedure?

ADOStoredProc.RetrieveParams;

How can I retrieve parameters from stored procedure in design time?

Click Clear button in Adonis Parameters editor and confirm retrieving of parameters.

Why my parameterized SQL statement doesn't work?

ParamType and DataType parameter properties can't be retrieved from SQL statement. You need to specify it in your code:

with ADODataSet do
  begin
    SQL.Text := 'SELECT * FROM MyTable WHERE ID > :Param';

    ParamByName('Param').ParamType := ptInput;
    ParamByName('Param').DataType := ftInteger;
    ParamByName('Param').AsInteger := 1;

    Open;
  end

You can use RetrieveParams method for automatic parameter retrieving if it's supported by OLE DB provider:

with ADODataSet do
  begin
    SQL.Text := 'SELECT * FROM MyTable WHERE ID > :Param';

    RetrieveParams;
    ParamByName('Param1').AsInteger := 1;

    Open;
  end;

Names of parameters can be different from names in SQL statement.

Why OnADOError event doesn't work for ExecSQL and ExecProc?

Use this code instead:

with ADOStoredProc do
try
  ExecProc;
except
  on E: Exception do CheckADOError(E.Message);
end;

How can I directly access ADO fields in the current record?

First call UpdateCursorPos method to ensure, that physical ADO recordset position matches the logical cursor position of ADODataSet:

ADODataSet.UpdateCursorPos;
ADODataSet.Recordset.Fields.Item['ID'].Value;

How can I view MS SQL server PRINT messages?

PRINT messages are stored in Errors.Description property:

CREATE PROCEDURE MyStoredProc AS
  PRINT "Hello"

ADOStoredProc1.ExecProc;

with ADODatabase1.Handle.Errors do
  for i := Count - 1 downto 0 do
    ShowMessage(' Description: ' + Item[i].Description);

How can I use constant fields in SQL query?

SELECT 'Hello', Name FROM MyTable

Why can't I compile my application when using ADO events?

Add ADO unit to the interface unit list:

interface

uses ADO, ...

How can I use asynchronous SQL execution?

ADODataSet.ExecProc;
try
  // some statements
finally
  ADODataSet.CompleteProc;
end;

How can I use MS Access indexes?

See Direct example supplied with Adonis

OLE DB provider must support indexes
set CursorLocation property to clServer
set CommandType property to ctTableDirect
set opWithoutADOEvents in Options property to True

How do I achieve the best ADO performance?

use native OLE DB provider
use server cursors, i.e. CursorLocation := clServer, although they may have some limitation in functionality
use asynchronous record fetching and processing
use ctTableDirect CommandType for MS Access database
use SQL statements when working with multiple records instead of processing each record separately in a loop
don't use SELECT * FROM MyTable, but specify each field explicitly: SELECT Name, Address FROM MyTable

How can I retrieve Jet ISAM statistics?

const
  JET_SCHEMA_ISAMSTATS = '{8703b612-5d43-11d1-bdbf-00c04fb92675}';
var
  i: Integer;
  Line: string;

with ADOConnection.OpenSchema(adSchemaProviderSpecific, EmptyParam, JET_SCHEMA_ISAMSTATS) do
  while not EOF do
  begin
    Line := '';
    with Fields do
      for i := 0 to Count - 1 do
        Line := Line + Item[i].Name + ':' + IntToStr(Item[i].Value) + #13;
    ShowMessage(Line);

    MoveNext;
  end;

How can I delete all records in a table?

ADODatabase.ExecSQL('DELETE FROM MyTable');

How can I use TADOStream component?

with TADOStream.Create(Self) do
try
  Active := True;
  Source := 'URL=https://computer/MyFile.htm';
  Charset := 'ascii';
  State := adStateOpen;

  // show content of MyFile.htm
  ShowMessage(ReadText(adReadAll));
finally
  Free;
end;

How can I use TADORecord component?

with TADORecord.Create(Self) do
try
  Active := True;
  ActiveConnection := 'URL=https://localhost/xxx';
  State := adStateOpen;

  // show all fields
  for i := 0 to ADOFields.Count - 1 do
    ShowMessage(ADOFields[i].Name + ' : ' + string(ADOFields[i].Value));
finally
  Free;
end;

How can I use GetRows function?

var
  Rows: OleVariant;
  i, j: Integer;

// return and show first 5 rows, fields NAME and CAPITAL only
Rows := ADODataSet.RecordSet.GetRows(5, adBookmarkFirst, VarArrayOf(['NAME', 'CAPITAL']));
for i := VarArrayLowBound(Rows, 1) to VarArrayHighBound(Rows, 1) do
  for j := VarArrayLowBound(Rows, 2) to VarArrayHighBound(Rows, 2) do
    ShowMessage(Rows[i, j]);

How can I use Seek method on compound indexes?

ADODataSet.Index := 'IndexAreaPopulation';
if ADODataSet.Seek(VarArrayOf([100000, 1000000]), soAfterEQ) then
  ShowMessage('Find');

How can I retrieve users connected to MS Access database?

const JET_SCHEMA_USERROSTER = '{947bb102-5d43-11d1-bdbf-00c04fb92675}';

with ADOConnection.OpenSchema(adSchemaProviderSpecific, EmptyParam, JET_SCHEMA_USERROSTER) do
  while not EOF do
  begin
    ShowMessage('Computer: ' + Fields['COMPUTER_NAME'].Value);
    ShowMessage('Login: ' + Fields['LOGIN_NAME'].Value);
    MoveNext;
  end;

Or use this code to retrieve more information:

const
  JET_SCHEMA_USERROSTER = '{947bb102-5d43-11d1-bdbf-00c04fb92675}';
var
  i: Integer;
  Line: string;

with ADOConnection1.OpenSchema(adSchemaProviderSpecific, EmptyParam, JET_SCHEMA_USERROSTER) do
  while not EOF do
  begin
    Line := '';
    with Fields do
      for i := 0 to Count - 1 do
      begin
        Line := Line + Item[i].Name + ':';
        if VarIsNull(Item[i].Value) then
          Line := Line + '(Null)'
        else
          Line := Line + string(Item[i].Value);
        Line := Line + #13;
      end;

    // remove zero bytes
    for i := 1 to Length(Line) do
      if Line[i] = #0 then
        Line[i] := ' ';

    ShowMessage(Line);

    MoveNext;
  end;

How can I retrieve autoincrement field flag?

if ADODataSet.Recordset.Fields['MyField'].Properties['ISAUTOINCREMENT'].Value then
  ShowMessage('Autoincrement field');

How can I retrieve ADO properties of TADODataSet at runtime?

with ADODataSet.Recordset.Properties do
  for i := 0 to Count - 1 do
    ShowMessage(Item[i].Name + ': ' + WideString(Item[i].Value));

OLE DB

What OLE DB providers can I use for MS Access, MSSQL, Oracle?

ODBC: MSDASQL
MS Access: Microsoft.Jet.OLEDB.4.0
MS SQL: SQLNCLI11
Oracle: MSDAORA

Data sources

What Jet 4.0 ISAM drivers are available on my computer?

See registration database using REGEDIT.EXE program:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\ISAM Formats

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\ISAM Formats

How can I connect to dBase database?

ADODataSet.DatabaseProvider := 'Microsoft.Jet.OLEDB.4.0';
ADODataSet.DatabaseConnect := 'Data Source=C:\DbFiles;Extended Properties="dBase 5.0;"';

How can I connect to Paradox database?

ADODataSet.DatabaseProvider := 'Microsoft.Jet.OLEDB.4.0';
ADODataSet.DatabaseConnect := 'Data Source=C:\PxFiles;Extended Properties="Paradox 7.X;"';

How can I connect to an Excel file?

ADODataSet.DatabaseProvider := 'Microsoft.Jet.OLEDB.4.0';
ADODataSet.DatabaseConnect := 'Data Source=C:\MyFile.xls;Extended Properties="Excel 8.0;"';
ADODataSet.TableName := '[MySheet$]';

How can I export MS Access table to an Excel file?

ADODatabase.ExecSQL('SELECT * INTO MySheet IN "c:\MyFile.xls" "Excel 8.0;" FROM MyTable');

How can I connect to a text file?

ADODataSet.DatabaseProvider := 'Microsoft.Jet.OLEDB.4.0';
ADODataSet.DatabaseConnect := 'Data Source=C:\TxtFiles;Extended Properties="Text;"';

How can I export MS Access table to a text file?

ADODatabase1.ExecSQL('SELECT * INTO MyText#txt IN "c:\" "Text;" FROM MyTable');

Delimiter, field descriptions, etc. can be specified in SCHEMA.INI file:

[MyText.txt]
ColNameHeader=True
CharacterSet=1250
Format=Delimited(,)
Col1=NAME Char Width 24
Col2=CAPITAL Char Width 24
Col3=CONTINENT Char Width 24
Col4=AREA Float
Col5=POPULATION Float

How can I connect to a CSV (comma separated values) text file?

ADODataSet.DatabaseProvider := 'Microsoft.Jet.OLEDB.4.0';
ADODataSet.DatabaseConnect := 'Data Source=C:\TxtFiles;Extended Properties="Text;"';
ADODataSet.TableName := 'TxtFile#csv';

SCHEMA.INI file must be created and placed to the same directory as TxtFile.csv. Content of SCHEMA.INI file:

[TxtFile.csv]
Format=CSVDelimited

Field definitions can be also specified in SCHEMA.INI:

[country.txt]
ColNameHeader=True
CharacterSet=OEM
Format=Delimited(;)
Col1=NAME Char Width 24
Col2=CAPITAL Char Width 24
Col3=CONTINENT Char Width 24
Col4=AREA Float
Col5=POPULATION Float

Valid format specifier values:

TabDelimited - fields are delimited by tabs
CSVDelimited - fields are delimited by commas (comma-separated values)
Delimited(custom character) - fields are delimited by custom character
Delimited( ) - no delimiter specified
FixedLength - fields are of a fixed length

How can I connect to HTML file?

ADODataSet.DatabaseProvider := 'Microsoft.Jet.OLEDB.4.0';
ADODataSet.DatabaseConnect := 'Data Source=C:\MyPage.htm;Extended Properties="HTML Import;"';

How can I export MS Access table to HTML file?

uses ShellApi;

DeleteFile('MyPage.htm');
with ADODataSet do
begin
  DatabaseProvider := 'Microsoft.Jet.OLEDB.4.0';
  DatabaseConnect := 'Data Source=C:\simple.mdb';
  SQL.Text := 'SELECT * INTO [MyPage.htm] IN "." [HTML Export;] FROM Country';
  ExecSQL;
  ShellExecute(Handle, 'open', 'MyPage.htm', nil, '', SW_SHOW); // show page in web browser
end;

How do I save a recordset in XML format?

ADODataSet.SaveXML('c:\myfile.xml');

Output is an XML fragment. Add an XML version header for it to be a valid xml document:

<?xml version="1.0" encoding="UTF-8">

How can I open a recordset from XML file?

ADODataSet.CommandType := ctFile;
ADODataSet.TableName := 'c:\myfile.xml';
ADODataSet.Open;

Or use Load method:

ADODataSet.Load('c:\myfile.xml');

How can I save a recordset in ADTG (Advanced Data TableGram) format?

ADTG is more efficient than XML but the output is stored in Microsoft proprietary format.

ADODataSet.SaveADTG('c:\myfile.adtg');

How can I open recordset from an ADTG file?

ADODataSet.CommandType := ctFile;
ADODataSet.TableName := 'c:\myfile.adtg';
ADODataSet.Open;

Or use Load method:

ADODataSet.Load('c:\myfile.adtg');

How can I use ADO DataFactory?

Adonis directly supports DataFactory without writing any special code. Just call Adonis ApplyUpdates and CancelUpdates methods.

ADO DataFactory, when used with Adonis RDSRemoteObject, allows you to open, edit and save a recordset from a remote machine using DCOM, HTTP or HTTPS as the transport protocol.

How can I use UDL file?

UDL file allows you to store all your database connection information in an external file, so you can change it easily without having to recompile your application

ADODatabase.DatabaseConnect := 'File Name=c:\conninfo.udl';

How can I create UDL file?

uses MSDASC;

var DataInitialize: IDataInitialize;

DataInitialize := CoMSDAInitialize.Create;
if Failed(DataInitialize.WriteStringToStorage('c:\myudl.udl', 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\simple.mdb', 1)) then
  raise Exception.Create('Can''t write UDL');

How can I create UDL file in C++Builder application?

#include <msdasc.hpp>

_di_IDataInitialize DataInitialize = CoMSDAINITIALIZE::Create(NULL);
if (FAILED(DataInitialize->WriteStringToStorage(L"c:\\myudl.udl", L"Microsoft.Jet.OLEDB.4.0;Data Source=c:\\simple.mdb", 1)))
  throw Exception("Error");

How can I specify MS Access workgroup information file?

ADODataSet.DatabaseProvider := 'Microsoft.Jet.OLEDB.4.0';
ADODataSet.DatabaseConnect := 'Data Source=database.mdb;Jet OLEDB:System database=c:\mysystem.mdw';

How can I connect to password protected MS Access database?

ADODataSet.DatabaseProvider := 'Microsoft.Jet.OLEDB.4.0';
ADODataSet.DatabaseConnect := 'Data Source=database.mdb;Jet OLEDB:Database Password=mypwd';

How can I connect to MS Access database located on CD/DVD drive?

ADODatabase.DatabaseProvider := 'Microsoft.Jet.OLEDB.4.0';
ADODatabase.DatabaseConnect := 'Data Source=E:\database.mdb';
ADODatabase.ConnectionMode := cmShareExclusive; // exclusive access, so lock file isn't created

How can I instantiate a 'creatable' ADO Recordset?

Creatable ADO recordset allows you to build a recordset in memory and populate it with data without connecting to a database.

with ADODataSet1 do
begin
  CommandType = ctFile;
  FieldDefs.Clear;
  FieldDefs.Add('Name', ftString, 25, True);
  FieldDefs.Add('Age', ftInteger, 0, False);
  Open;
end;

It is also possible to create persistent fields interactively at design time using the Fields Editor:

Set CommandType property to ctFile (TableName and SQL properties must be empty)
Add persistent fields using Fields Editor
Set ADODataset.Active to true and creatable recordset will be automatically instantiated
You can now insert and edit data

How can I query heterogenous datasources (diverse data sources)?

A heterogeneous query is a query between two databases (eg. two different SQL server catalogs), two types of database (eg. an Access database and an SQL Server database) or databases on two machines.

It is possible to query two different SQL Server catalogs in SQL:

select * from cat1..table1 as t1, cat2..table2 as t2 where t1.id = t2.id

Due to the ADO limitation, that a recordset can only be opened from one connection object it is impossible to query two different types of databases or query two machines.

Intersolv produce a third-party heterogeneous query processor called ISGNavigator for ADO.

How can I disconnect a recordset?

ADODataSet.DatabaseConnect := '';

Or use this code:

ADODataSet.Database := nil;

How can I set database connection interactively?

if ADODataSet.SelectConnection then
  ADODataSet.Open;

How can I reload an saved ADTG recordset, modify it, then reconnect and update database?

Load ADTG data:

ADODataSet.CommandType := ctFile;
ADODataSet.LockType := ltBatchOptimistic;
ADODataSet.TableName := 'country.ADTG';
ADODataSet.Open;

Reconnect and update changes to database:

// ADTG file name must correspond to table name in database
ADODataSet.DatabaseConnect := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database.mdb';
ADODataSet.ApplyUpdates;

How can I reconnect an disconnected ADODataSet and apply changes?

// ADODataSet.LockType property must be ltBatchOptimistic
ADODataSet.DatabaseConnect := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database.mdb';
ADODataSet.ApplyUpdates;

How can I read connection parameters from an UDL file?

var
  DataInitialize: IDataInitialize;
  ConnectionString: PWideChar;
begin
  DataInitialize := CoMSDAINITIALIZE.Create;
  DataInitialize.LoadStringFromStorage('c:\erik.udl', ConnectionString);
  ShowMessage(ConnectionString);
end;

RDS

Why nTier sample application doesn't work over DCOM?

See the Adonis help for information on how to set the ComputerName property to select transport protocol.

If you attempt to use DCOM and the client and middle-tier applications are on the same computer RDS will use COM, not DCOM. If you want to test multi-computer deployment on a single computer you should use the HTTP transport protocol during testing.

Single computer

Set RDSRemoteObject.ComputerName to a blank string on the Client
Register AppServer.exe by running it once

Multiple computers

Client:

Before you can run an nTier client application across to machines you must add the Business object's progid to the Client machines registry. Save the following into a file called BusObj.reg and double-click on it from explorer.

REGEDIT4

[HKEY_CLASSES_ROOT\Srv.BusinessObject]
@="BusinessObjectObject"

[HKEY_CLASSES_ROOT\Srv.BusinessObject\Clsid]
@="{45CECB44-4CBB-11D2-9D8E-204C4F4F5020}"

Server:

Register your server by starting it
Run DCOMCNFG if you wish to configure your business objects security settings and launch permissions. If you want your business object to be visible on the server then set 'identity' to interactive user. The server must be logged in at all times to use this option. If you recieve the error 'Unable to create business object' on your server it usually indicates a security problem between the client and server.

Why nTier sample application doesn't work over HTTP?

Using HTTP or HTTPs as a transport protocol requires these steps:

Make sure the Business Object is marked as 'safe for scripting' and 'safe for launching'
Add the Business Object to the ADCLaunch key of IIS: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W3SVC\Parameters\ADCLaunch

Troubleshooting tips for RDS and HTTP:

if you have marked your Virtual Web site or Virtual directory as 'Run in a separate memory space' you may encounter 'Unexpected error 0x80070558'
if you don't mark your BO's as ADCLaunch you may encounter 'Unexpected error 0x80070005'
if your web server doesn't have enabled execute privelege ('Home Directory' page in Internet Service Manager's web site properties) then the client application may freeze

ADOX

How can I create a new database?

ADOXCatalog.ActiveConnection := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=new.mdb'; // select interactively
ADOXCatalog.CreateCatalog;

Or use even simpler code:

ADOXCatalog.CreateNew('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=new.mdb');

Use additional properties in connection string to specify encryption, database version, database password and locale:

ADOXCatalog.CreateNew('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=new.mdb;Jet OLEDB:Encrypt Database=True'); // encrypted database

ADOXCatalog.CreateNew('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=new.mdb;Jet OLEDB:Engine Type=4'); // Jet 3.X format (MS Access 97)

ADOXCatalog.CreateNew('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=new.mdb;Jet OLEDB:Database Password=MyPwd'); //  MyPwd password

ADOXCatalog.CreateNew('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=new.mdb;Locale Identifier=0x0405'); // Czech locale

Jet OLEDB:Engine Type codes:

1 // Jet 1.0
2 // Jet 1.1
3 // Jet 2.x
4 // Jet 3.x (MS Access 97)
5 // Jet 4.x (MS Access 2000)

Locale Identifier codes:

Chinese Punctuation		0x00000804
Chinese Stroke Count		0x00020804
Chinese Stroke Count (Taiwan)	0x00000404
Chinese Bopomofo (Taiwan)	0x00030404
Croatian			0x0000041a
Czech				0x00000405
Estonian			0x00000425
French				0x0000040c
General				0x00000409
Georgian Modern			0x00010437
German Phone Book		0x00010407
Hungarian			0x0000040e
Hungarian Technical		0x0001040e
Icelandic			0x0000040f
Japanese			0x00000411
Japanese Unicode		0x00010411
Korean				0x00000412
Korean Unicode			0x00010412
Latvian				0x00000426
Lithuanian			0x00000427
Macedonian			0x0000042f
Norwegian/Danish		0x00000414
Polish				0x00000415
Romanian			0x00000418
Slovak				0x0000041b
Slovenian			0x00000424
Spanish Traditional		0x0000040a
Spanish Modern			0x00000c0a
Swedish/Finnish			0x0000041d
Thai				0x0000041e
Turkish				0x0000041f
Ukrainian			0x00000422
Vietnamese			0x0000042a

How can I retrieve table names?

with ADOXCatalog.ADOXTables do
  for i := 0 to Count - 1 do
    ShowMessage(Item[i].Name);

To skip system tables:

with ADOXCatalog.ADOXTables do
  for i := 0 to Count - 1 do
    if Item[i].Type_ = 'TABLE' then
      ShowMessage(Item[i].Name);

How can I create a new table?

ADOXCatalog.ActiveConnection := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database.mdb';

// create table
ADOXTable.CreateNew('Contacts');

// create columns
ADOXColumn.CreateNew('Name', adVarWChar, 20);
ADOXColumn.AppendTo(ADOXTable);

ADOXColumn.CreateNew('Phone', adVarWChar, 20);
ADOXColumn.AppendTo(ADOXTable);

// create index
ADOXIndex.CreateNew('MyIndex');
ADOXIndex.PrimaryKey := True;
ADOXIndex.Unique := True;

ADOXColumn.CreateNew('Name', adVarWChar, 20);
ADOXColumn.AppendTo(ADOXIndex);

ADOXIndex.AppendTo(ADOXTable);

ADOXTable.AppendTo(ADOXCatalog);

How can I create stored query?

ADOXView.CreateNew('AllContacts', 'SELECT * FROM Contacts');
ADOXView.AppendTo(ADOXCatalog);

How can I create parameterized stored query?

ADOXView.CreateNew('Contacts by name', 'SELECT * FROM Contacts WHERE Name LIKE MyParam');
ADOXView.AppendTo(ADOXCatalog);

How can I create string field with AllowZeroLength?

ADOXCatalog.ActiveConnection := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database.mdb';
ADOXCatalog.Open;

ADOXTable.CreateNew('Country');

ADOXColumn.CreateNew('MyField', adVarWChar, 20);
ADOXColumn.ADOXColumn.ParentCatalog := ADOXCatalog.ADOXCatalog;

ADOXProperty.OpenExisting(ADOXColumn, 'Jet OLEDB:Allow Zero Length');
ADOXProperty.Value := True;

ADOXColumn.AppendTo(ADOXTable);
ADOXTable.AppendTo(ADOXCatalog);

How do I create an autoincrement field?

ADOXCatalog.ActiveConnection := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database.mdb';
ADOXCatalog.Open;

ADOXTable.CreateNew('Country');

ADOXColumn.CreateNew('Id', adInteger, 0);
ADOXColumn.ADOXColumn.ParentCatalog := ADOXCatalog.ADOXCatalog;

ADOXProperty.OpenExisting(ADOXColumn, 'AutoIncrement');
ADOXProperty.Value := True;

ADOXColumn.AppendTo(ADOXTable);
ADOXTable.AppendTo(ADOXCatalog);

How can I access field description?

Set ADOXProperty.PropertyName property to 'Description' and Active to True. Field description text appears in Value property.

Or use this code:

ADOXCatalog.ActiveConnection := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database.mdb';
ADOXTable.OpenExisting(ADOXCatalog, 'MyTable');
ADOXColumn.OpenExisting(ADOXTable, 'MyColumn');
ADOXProperty.OpenExisting(ADOXColumn, 'Description');
ShowMessage(ADOXProperty.Value);

How can I rename table?

ADOXCatalog.ActiveConnection := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database.mdb';
ADOXTable.OpenExisting(ADOXCatalog, 'MyTable');
ADOXTable.TableName := 'NewTableName';

How can I rename column?

ADOXCatalog.ActiveConnection := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database.mdb';
ADOXTable.OpenExisting(ADOXCatalog, 'MyTable');
ADOXColumn.OpenExisting(ADOXTable, 'MyColumn');
ADOXColumn.ColumnName := 'NewColumnName';

How do I use Adonis database connection in ADOX?

ADOXCatalog.OpenExisting(ADODatabase.Handle);

How do I create MS Access linked table?

ADOXCatalog.ActiveConnection := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database.mdb';
ADOXCatalog.Open;

ADOXTable.CreateNew('MyTable');

ADOXTable.ADOXTable.ParentCatalog := ADOXCatalog.ADOXCatalog;

ADOXProperty.OpenExisting(ADOXTable, 'Jet OLEDB:Create Link');
ADOXProperty.Value := True;

ADOXProperty.OpenExisting(ADOXTable, 'Jet OLEDB:Link Datasource');
ADOXProperty.Value := 'c:\mydb.mdb';

ADOXProperty.OpenExisting(ADOXTable, 'Jet OLEDB:Remote Table Name');
ADOXProperty.Value := 'Country';

// these two lines are required only if linked table is password protected
ADOXProperty.OpenExisting(ADOXTable, 'Jet OLEDB:Link Provider String');
ADOXProperty.Value := 'MS Access;PWD=mypwd;';

ADOXTable.AppendTo(ADOXCatalog);

How do I create MS Access memo field?

ADOXColumn.CreateNew('MyMemoField', adLongVarCharWChar, 0);
ADOXColumn.AppendTo(ADOXTable);

How can I create MS Access secondary index which allows duplicate keys?

ADOXIndex.CreateNew('MyIndex');
ADOXIndex.ADOXColumns.Append('Name', adVarWChar, 24);
ADOXIndex.ADOXColumns.Append('Capital', adVarWChar, 24);
ADOXIndex.Unique := False; // allow duplicate key
ADOXIndex.AppendTo(ADOXTable);

How can I create MS Access secondary index which ignore nulls in the columns?

ADOXIndex.CreateNew('MyIndex');
ADOXIndex.ADOXColumns.Append('Name', adVarWChar, 24);
ADOXIndex.ADOXColumns.Append('Capital', adVarWChar, 24);
ADOXIndex.IndexNulls := adIndexNullsIgnore; // ignore null values
ADOXIndex.AppendTo(ADOXTable);

How can I set up MS Access relationship between tables?

ADOXKey.CreateNew('MyKey');
ADOXKey.KeyType := adKeyForeign;
ADOXKey.RelatedTable := 'Master';

ADOXColumn.CreateNew('MasterId', adInteger, 0);
ADOXColumn.RelatedColumn := 'Id';
ADOXColumn.AppendTo(ADOXKey);

ADOXKey.UpdateRule := adRICascade;
ADOXKey.AppendTo(ADOXTableDetail);

How do I specify MS Access referential integrity?

Use TADOXKey DeleteRule and UpdateRule properties.

How can I retrieve a list of MS Access groups and users?

Jet 4.0 provider is required and workgroup information file (.mdw) must be specified:

ADOXCatalog.OpenExisting('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=simple.mdb;Jet OLEDB:System database=system.mdw');

// show groups
with ADOXCatalog.ADOXGroups do
  for i := 0 to Count - 1 do
    ShowMessage(Item[i].Name);

// show users
with ADOXCatalog.ADOXUsers do
  for i := 0 to Count - 1 do
    ShowMessage(Item[i].Name);

How can I retrieve or change user permissions?

var Rights: Integer;

ADOXCatalog.OpenExisting('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=simple.mdb;Jet OLEDB:System database=system.mdw');
ADOXUser.OpenExisting(ADOXCatalog, 'admin');

// retrieve permissions
Rights := ADOXUser.GetPermissions('MyTable', adPermObjTable, EmptyParam);

if Rights = adRightNone then ShowMessage('RightNone');
if Rights and adRightDrop <> 0 then ShowMessage('RightDrop');
if Rights and adRightExclusive <> 0 then ShowMessage('RightExclusive');
if Rights and adRightReadDesign <> 0 then ShowMessage('RightReadDesign');
if Rights and adRightWriteDesign <> 0 then ShowMessage('RightWriteDesign');
if Rights and adRightWithGrant <> 0 then ShowMessage('RightWithGrant');
if Rights and adRightReference <> 0 then ShowMessage('RightReference');
if Rights and adRightCreate <> 0 then ShowMessage('RightCreate');
if Rights and adRightInsert <> 0 then ShowMessage('RightInsert');
if Rights and adRightDelete <> 0 then ShowMessage('RightDelete');
if Rights and adRightReadPermissions <> 0 then ShowMessage('RightReadPermissions');
if Rights and adRightWritePermissions <> 0 then ShowMessage('RightWritePermissions');
if Rights and adRightWriteOwner <> 0 then ShowMessage('RightWriteOwner');
if Rights and adRightMaximumAllowed <> 0 then ShowMessage('RightMaximumAllowed');
if Rights and adRightFull <> 0 then ShowMessage('RightFull');
if Rights and adRightExecute <> 0 then ShowMessage('RightExecute');
if Rights and adRightUpdate <> 0 then ShowMessage('RightUpdate');
if Rights and adRightRead <> 0 then ShowMessage('RightRead');

// deny 'Insert' right
ADOXUser.SetPermissions('MyTable', adPermObjTable, adAccessDeny, adRightInsert, adInheritNone, EmptyParam);

JRO

How do I compact database?

JROJetEngine.SourceConnection := 'Data Source=c:\old.mdb'; // can be selected interactively
JROJetEngine.DestConnection := 'Data Source=c:\new.mdb'; // can be selected interactively
JROJetEngine.Compact;

How can I specify encryption, database version, database password, locale?

JROJetEngine.SourceConnection := 'Data Source=c:\old.mdb';
JROJetEngine.DestConnection := 'Data Source=c:\new.mdb;Jet OLEDB:Engine Type=4'); // Jet 3.X (MS Access 97)
JROJetEngine.Compact;

How can I change database password?

JROJetEngine.SourceConnection := 'Data Source=c:\old.mdb;Jet OLEDB:Database Password=OldPwd'; // can be selected interactively
JROJetEngine.DestConnection := 'Data Source=c:\new.mdb;Jet OLEDB:Database Password=NewPwd'; // can be selected interactively
JROJetEngine.Compact;

How can I make database replicable?

JROReplica.ActiveConnection := 'Data Source=C:\database.mdb';
JROReplica.MakeReplicable(True);

How can I make an Access object replicable?

JROReplica.OpenExisting('Data Source=C:\database.mdb');
JROReplica.SetObjectReplicability('MyTable', 'Tables', True);

How can I make an Access object local?

JROReplica.OpenExisting('Data Source=C:\database.mdb');
JROReplica.SetObjectReplicability('MyTable', 'Tables', False);

How do I create database replica?

JROReplica.OpenExisting('Data Source=C:\database.mdb');
JROReplica.CreateReplica('C:\replica.mdb', 'My replica', jrRepTypeFull, jrRepVisibilityGlobal, 90, jrRepUpdFull);

How do I create partial replica?

JROReplica.OpenExisting('Data Source=C:\database.mdb');
JROReplica.CreateReplica('C:\replica.mdb', 'My replica', jrRepTypePartial, jrRepVisibilityGlobal, 90, jrRepUpdFull);

JROReplica.OpenExisting('Data Source=C:\replica.mdb');
JROFilter.CreateNew(JROReplica, 'MyTable', jrFilterTypeTable, 'Country = ''USA''');

How can I retrieve a list of replica filters?

with JROReplica.JROFilters do
  for i := 0 to Count - 1 do
    ShowMessage(Item[i].TableName + ' : ' + Item[i].FilterCriteria);

How do I synchronize replicas?

JROReplica.OpenExisting('Data Source=C:\database.mdb');
JROReplica.Synchronize('C:\replica.mdb', jrSyncTypeImpExp, jrSyncModeDirect);

OLAP

How do I use MSOLAP provider?

with ADODataSet do
begin
  DatabaseProvider := 'MSOLAP';
  DatabaseConnect := 'DataSource=Erik;Server=Tutorial';
  SQL.Text :=
    'SELECT { [Measures].[Units Shipped], [Measures].[Units Ordered] } ON COLUMNS, ' +
    'NON EMPTY [Store].[Store Name].members ON ROWS ' +
    'FROM Warehouse ';
  Open;
end;