New...Access Database from Delphi

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. Add a TADOXCatalog component (ActiveX page). The TADOXCatalog will do the trick of creating a new database. 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.
In this chapter we'll link those components from code. Therefore, you do not need to set up a ConnectionString for the ADOConnection component and the Connection property for the ADOCOmmand component now.

New...Database
Before we move on to creating tables and linking them we have to create a new (empty) database. This is the code:

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;

Would you belive - simple as that. Obviously the ADOXCatalog has a method called Create1 that creates a new database. Pretty unusual since we've accustomed that Create methods are used to create an object from a class. The ADOXCatalog really has a Create method which has nothing in common to 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. Engine type 4 is used to create an MS Access 97 database, type 5 is for MS Access 2000.

Note that the above code does not check for the existance of the c:\aboutdelphi.mdb database. If you run this code twice it will complain that the databae already exists.

Add table, create index, set referential integrity
The next step is to create all tables (three of them), add indexes, and create referential integrity. Even though we could use ADOX, that is, TADOXTable, TADOXKey, etc. I'm somehow more familiar with the (standard) DDL language and the TADOCommand component. 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

That's it. Now run the project, click the btnNewDatabase button, click the btnAddTables button and you have a new (empty) aboutdelphi.mdb database in the root of the C disk. If you have MS Access installed on your system you can open this database with it and check that all thje tables are here and in the Relationships window all the tables are linked.