Board index » delphi » Create a new table using the MSACCESS native driver

Create a new table using the MSACCESS native driver

Hi,

I would like to create a new table (in an existing MSACCESS database).
What's wrong with my code ? Thanks.

procedure CreateTableThread.Execute;
begin
        // connect to the database
        m_db := TDatabase.Create( nil );
  with m_db do begin
        DriverName := 'MSACCESS';
        LoginPrompt:= False;
        DatabaseName := 'MSACCESS1';
                Params.Add(
                'DATABASE NAME=' + m_ApplicationDirectory + '\Results.mdb'
            );
        Connected := True;                                                    
  // WORKS OK !!!!
end;

        // create a new table in the database
        Query1 := TQuery.Create( nil );
        with Query1 do begin
                DatabaseName := m_db.DatabaseName;
                with SQL do begin
                        Clear;
                        Add('CREATE TABLE "MyName" ' );
                        Add('(Nr SMALLINT, ' ) ;
                        Add('Counter AUTOINC,' ) ;
                        Add('Name CHARACTER(25), ' );
                        ADD('PRIMARY KEY(Nr, Counter)) '  ) ;
                        ExecSQL;                                             // HERE IT FAILS
                        Clear;
                        Add('CREATE INDEX ByName ON "MyName" (Name, Counter) ' );
                        ExecSQL;
                end;
        end;
        Query1.Free;

    // disconnect the database
        with m_db do begin
        Connected := False;
        Free;
        end;
end;

 

Re:Create a new table using the MSACCESS native driver


Quote
>I would like to create a new table (in an existing MSACCESS database).
>What's wrong with my code ?

Did you test your SQL in Access?  Access SQL is fairly non standard.
If you can get it to work there it should work from Delphi

--
Brian Bushay (TeamB)
Bbus...@DataGuidance.com

Re:Create a new table using the MSACCESS native driver


The syntax is incorrect. First, do not use quotes around the table name. If
your table name is using whitespace characters use square brackets (i.e.
 and ]). Also, definition of indexes is not done this way, you have to use
CONSTRAINT statements to do this. Bring up MS Access' help file, go to the
index and bring up the information on CREATE TABLE.

Remember that MS' implementation of SQL in the Jet engine and DAO is
non-standard (iow, a PITA if you're used to normal SQL).

Juan

Quote
Pierre Planche wrote in message <01bd22c0$894afc60$5fc3f7cc@castor>...
>Hi,

>I would like to create a new table (in an existing MSACCESS database).
>What's wrong with my code ? Thanks.
>  // create a new table in the database
>      Query1 := TQuery.Create( nil );
>    with Query1 do begin
>    DatabaseName := m_db.DatabaseName;
>   with SQL do begin
>   Clear;
>   Add('CREATE TABLE "MyName" ' );
>   Add('(Nr SMALLINT, ' ) ;
>   Add('Counter AUTOINC,' ) ;
>   Add('Name CHARACTER(25), ' );
>   ADD('PRIMARY KEY(Nr, Counter)) '  ) ;
> ExecSQL;                                             // HERE IT FAILS
>   Clear;
>   Add('CREATE INDEX ByName ON "MyName" (Name, Counter) ' );
> ExecSQL;

Other Threads