Lazarus and MS Access 2010
lazarus / 16-Databases

----------------------------------------------------------------------
asdf · 2012-10-31 06:00

In my previous projects, I wrote the codes like below for Firebird in
order to not dragging 'controls' into 'forms', but now I have a limit
to use only MS Access2010 as database. I applied from my previous
project and ran but error:
Code: [Select]uses
  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs,
Buttons,
  Grids, odbcconn, sqldb ;
                                  

Code: [Select]implementation

{$R *.lfm}

{ TForm1 }

var
cna : TODBCConnection;
sqa : TSQLQuery;
tra : TSQLTransaction;



procedure TForm1.BitBtn2Click(Sender: TObject);
begin
application.Terminate;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
bitbtn2.Caption:='exit';

// DataModule like
cna := TODBCConnection.Create(nil);
tra := TSQLTransaction.Create(nil);
tra.DataBase := cna;
tra.Action   := caCommit;

cna.Transaction := tra;

sqa := TSQLQuery.Create(nil);
sqa.DataBase:= cna;
sqa.Transaction := tra;

  if not fileexists('ta.mdb') then   // if not exist, create new

  begin

  // create .fdb

  cna.DatabaseName:= 'ta'+'.mdb';
  cna.UserName:= 'sysdba';
  cna.Password:= 'masterkey';
  cna.CreateDB;
  tra.Active := True;

  //create 'Table'

  sqa.SQL.Clear;
  sqa.sql.text:= 'create table engthagen'
  +'('
              + 'esearch varchar(200) not null,'
              + 'eentry  varchar(200),'
              + 'tentry  varchar(700),'
              + 'ecat    varchar(100),'
              + 'ethai   varchar(700),'
              + 'eid     varchar(100),'
              + 'esyn    varchar(300),'
              + 'eant    varchar(100)'
  +')';

  sqa.ExecSQL;
  tra.CommitRetaining;
 
  //EFlowMemo.Append(' .fdb created');

  cna.Close; // -> cn.CreateDB

  end;

end;

The first error is at row 73, what should I do in row number 73 and
how about the remained codes after row number 73 ?

Thank you in advance

----------------------------------------------------------------------
BigChimp · 2012-10-31 10:24

Apparently the sqldb ODBC code has no way to create a database.

I strongly suspect the ISO SQL standard has no way to specify CREATE
DATABASE because every db server/product has its own implementation,
so it makes sense that the ODBC driver does not support this.

Some (more or less random) ideas:
Create a template .accdb/.mdb file and include it as a resource in
your program; copy it out when needed and use itCreate the database
via DAO/ADOX, you will probably need to run the .tlb through the
ActiveX tools in newer releases of Lazarus

----------------------------------------------------------------------
asdf · 2012-10-31 10:37

Dear BigChimp

Thank you.

What is the difference that Firebird can create but MS Access cannot ?

----------------------------------------------------------------------
BigChimp · 2012-10-31 12:56

Access can create a db just fine. Look at the many examples for
creating databases with VBA code and the DAO or ADO(X) object models.

You are going through the ODBC driver though. ODBC supports many
different databases via drivers.

In this situation, FreePascal/SQLDB has no way of knowing what exact
statement to issue for CreateDB. E.g. you could have access a Firebird
or MS SQL Server or MS Access database through ODBC; this requires
that FPC SQLDB should send different commands to implement .Createdb
(e.g. mysql doesn't know anything about Firebird's pagesize option;
you can minutely fine tune physical storage locations on DB/2 and
Oracle dbs etc).

If you're sure you're always dealing with Access, you could try to
pass an SQL statement through the code (e.g.
connection1.executedirect('CREATE DATABASE C:\test.mdb' or something
similar), though I don't know whether the Access ODBC driver will
accept this.

A more general solution would be to detect ODBC driver type and let
SQLDB generate CreateDB statements on the fly, but that looks like a
maintenance nightmare, to be honest.