用代码创建Access数据库

文章原地址http://delphi.about.com/od/database/l/aa072401b.htm，加上一些翻译和对数据库创建加入了一种新的方法。

The Delphi Project
Our task is to have Delphi do all the work. We want to create a new database from code, add all three tables from code, add indexes from code and even set up a referential integrity between those tables - again from code.

As usual, have an empty Delphi form. Add two button component. Add a TADOConnection, TADOCommand. We'll use TADOCommand with a DDL language to create and link tables. 添加TADOXCatalog 组件(在ActiveX组件页上). TADOXCatalog 用来创建数据库. Let the name of the first button be btnNewDatabase (caption: 'Create database'), the second one should be called btnAddTables (caption: 'Create and link tables'). All the other components should have the default name.
在这一节里，我们将用代码建立所有组件和数据的链接，所以不需要设置ConnectionString 和 ADOConnection 和ADOCOmmand 的Connection 属性。

New...Database
在创建表和关联之前，我们先建立一个空的数据库，代码如下：

 

//注：TADOXCatalog 默认是没有的，需要将C:\Program Files\Common Files\System\ado\msadox.dll导入

procedure TForm1.btnNewDatabaseClick(Sender: TObject);
var DataSource : string; 
dbName     : string;
begin 
    dbName:='c:\aboutdelphi.mdb'; 
    DataSource :=    'Provider=Microsoft.Jet.OLEDB.4.0' +
    ';Data Source=' + dbName +    ';Jet OLEDB:Engine Type=4';
     ADOXCatalog1.Create1(DataSource);
end;

 

//另一种更简洁的方法,直接使用OLE对象创建

 

//Delphi Code

procedure TForm1.btnNewDatabaseClick(Sender: TObject);
  var cat : OLEVariant;
  var Filename, result : string;
begin
  Filename := 'c:\test.mdb';
  result := '';
  try
    cat := CreateOleObject('ADOX.Catalog');
    cat.create ('Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+Filename+';');
    cat := NULL;
  except
    on e : Exception do result := e.message;
  end;
end;

 

//BCB Code

OleVariant DataSource = String("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=")
    + path + ";Jet OLEDB:Engine Type=5"; //Type=4=access97, Type=5=Access2000
try
{
  Variant cat = CreateOleObject("ADOX.Catalog");
  cat.OleFunction("Create", DataSource);
  cat = NULL; 
}
catch(Exception& e)
{
  String info = e.Message;
  Application->MessageBoxW(info.c_str(), "数据库创建出错");
}

 

你相信如此简单吗。显然ADOXCatalog 有一个Create1的方法来创建数据库。这种方式在我们习惯用Create创建一个对象后显得有些不寻常，但是ADOXCatalog 真的有一个Create方法但和Create1无关。

The variable DataSource looks pretty much like a standard connection string for the TADOConnection component. There is only one addition, the Jet OLEDB:Engine Type=X part. 类型 4 用来创建MS Access 97 数据库, 类型 5 创建 MS Access 2000.

注意上面的代码不能检测是否存在c:\aboutdelphi.mdb数据库，如果这段代码运行两次则会报告数据库已经存在。

Add table, create index, set referential integrity

下一步就是创建所有的表，索引，和创建表之间的关联。尽管我能使用ADOX，如TADOXTable，TADOXKey等等。但我更熟练使用标准的DDL语言和TADOCommand 组件。Back in the chapter 11 of this course we discussed database tables porting issues. This time we'll create tables from nothing.
The following peaces of code are to be placed inside the button's btnAddTables OnClick even handler, I'll slice the code and add some explanations in between.

First, we need to connect to the newly created database with the TADOConnection component. Since we've left the ADOCommand unattached to ADOConnection - we'll link them from code (this should be obvious by now):

 

procedure TForm1.btnAddTablesClick(Sender: TObject);
var
  DataSource : string;
  cs         : string;
begin
DataSource :=
     'Provider=Microsoft.Jet.OLEDB.4.0'+
     ';Data Source=c:\aboutdelphi.mdb'+
     ';Persist Security Info=False';

ADOConnection1.ConnectionString := DataSource;
ADOConnection1.LoginPrompt := False;
ADOCommand1.Connection := ADOConnection1;

 

Second, we create both Types and Authors tables, the structures are given in the first chapter. To build a new table with DDL by using the Jet SQL, we use the CREATE TABLE statement by providing it the name the table, name the fields, and fiedl type definitions. Then, the Execute method of the ADOCommand component is used.

 

cs:='CREATE TABLE Types (typename TEXT(50))';
ADOCommand1.CommandText := cs;
ADOCommand1.Execute;

cs:='CREATE TABLE Authors (' +
       'authorname TEXT(50),' +
       'email TEXT(50),' +
       'web TEXT(50))';
ADOCommand1.CommandText := cs;
ADOCommand1.Execute;

 

Next, we add indexes to those two tables. When you apply an index to a table, you are specifying a certain arrangement of the data so that it can be accessed more quickly. To build an index on a table, you must name the index, name the table to build the index on, name the field or fields within the table to use, and name the options you want to use. You use the CREATE INDEX statement to build the index. There are four main options that you can use with an index: PRIMARY, DISALLOW NULL, IGNORE NULL, and UNIQUE. The PRIMARY option designates the index as the primary key for the table.

 

cs:='CREATE INDEX idxPrimary '+
     'ON Types (typename) WITH PRIMARY';
ADOCommand1.CommandText := cs;
ADOCommand1.Execute;

cs:='CREATE INDEX idxPrimary '+
     'ON Authors (authorname) WITH PRIMARY';
ADOCommand1.CommandText := cs;
ADOCommand1.Execute;

 

Finally, we add the last table. Applications table is linked with both Types and Authors in a master detail relationship. Back in the last chapter we were discussing one-to-many relationships that define the following: for every record in the master table, there are one or more related records in the child table. In our case, one Author (Authors table) can post more Applications; and the Application can be of some type.
When defining the relationships between tables, we use the CONSTRAINT declarations at the field level. This means that the constraints are defined within a CREATE TABLE statement.

 

cs:='CREATE TABLE Applications ('+
     '  Name TEXT(50),'+
     '  Description TEXT(50),'+
     '  Author TEXT(50) CONSTRAINT idxauthor '+
        'REFERENCES Authors (authorname),'+
     '  Type TEXT(50) CONSTRAINT idxtype '+
        'REFERENCES Types (typename),'+
     '  [Size] FLOAT,'+
     '  Cost CURRENCY,'+
     '  DateUpl DATETIME,'+
     '  Picture LONGBINARY)';

ADOCommand1.CommandText := cs;
ADOCommand1.Execute;

end;//btnAddTablesClick

 

到此为止。现在运行工程，点btnNewDatabase按钮，点btnAddTables 按钮就会在C盘创建一个空的aboutdelphi.mdb数据库。如果你的电脑你安装了Access，你可以打开来检查数据库的结构和表之间的关系。

All that using ADOX
我曾讲过我们可以使用ADOX，如，TADOXTable，TADOXKey等等。在经过几天的ADOX研究之后，我决定让使用ADOX的代码能正常工作。但是，你已经使用DDL创建了这个数据库，所以我不打算在这里在转一圈了。这里有一个链接是使用Delphi Pascal 代码调用ADOX创建数据库的例子（包括表关联，索引等等）。这些代码演示这些例子的同时也指出了一些问题和陷阱。Create an Access database with ADOX

To the next chapter
If you need any kind of help so far, please post to the Delphi Programming Forum where all the questions are answered and beginners are treated as experts.