Create a Microsoft Access (.mdb) and tables

Hi,

1. Creating an mdb file:

uses ComObj;

// Version 3=95, 4=97, 5=2000
procedure CreateAccessDatabase(MDBName: string; Version: integer);
var
  Catalog: Variant;
begin
  Catalog := CreateOleObject('ADOX.Catalog');
  Catalog.Create('Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type='+IntToStr(Version)+';Data Source='+MDBName+';');
end;

// usage
procedure TForm1.Button1Click(Sender: TObject);
begin
  CreateAccessDatabase('D:\new.mdb',4);
end;

2. Creating tables:

Use (ADO)Query component with 'CREATE TABLE' sql statement and use ExecSQL instead of Open.

Example:

procedure TForm1.Button2Click(Sender: TObject);
begin
with ADOQuery1 do begin
  Close;
  ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\new.mdb;';
  SQL.Clear;
  SQL.Add('CREATE TABLE Table1 (Field1 char(20), Field2 integer)');
  ExecSQL;
end;
end;

Regards, Geo

geo, you become faster :-))

I'm currently writing few things using access and that code above (similar) is opened in my Delphi window right now :-)

geobul,
how can i determine fields property of table in this maner.
It is very important for me.

Hi,

What property? Using CREATE TABLE statement you define:

- table name
- fields names
- fields types
- fields lengths (for string type fields)

You may also define:

- NOT NULL property
- PRIMARY KEY field/s
- multiply

Examples (All words in capital letters are reserved words. The other words are names of tables, fields and indexes):

// create Table1: NOT NULL example
CREATE TABLE Table1 (Field1 INTEGER NOT NULL, Field2 CHAR(20), Field3 DATETIME)

// create Table2: Primary Key example
CREATE TABLE Table2 (id INTEGER CONSTRAINT Table2id PRIMARY KEY, Name CHAR(50), DateOfBirth DATETIME)

// create Table3: complex unique index
// if you may also use PRIMARY KEY instead of UNIQUE for complex primary key
CREATE TABLE Table3 (FirstName CHAR(20), LastName CHAR(20), DateOfBirth DATETIME, CONSTRAINT Table3Constraint UNIQUE (FirstName, LastName, DateOfBirth))

Regards, Geo

- multiply

should be:

- multiple-field index

GeoBul,
I need determine property "Allow zero length" of Field

Hi Geobul.
I test what you say, but NOU NULL define propert "Requier" not "Allow Zero Length"
I think this way of creating is not so good.
Do you know better way(for example with delphi components in server tab in delphi 6)

My mistake, sorry. You are right. I can't find a way to set that property using SQL :-(

I have no experience using these components.