MS Access

From Lazarus wiki
Revision as of 10:50, 21 May 2011 by Rocarobin (talk | contribs) (Instructions)

This page explains how to use a Microsoft Access database.

Databases portal

References:

Tutorials/practical articles:

Databases

Advantage - MySQL - MSSQL - Postgres - Interbase - Firebird - Oracle - ODBC - Paradox - SQLite - dBASE - MS Access - Zeos

Instructions

Goto your [Data sources (ODBC)] at the control panel administrative tools. Goto [File DSN] tab menu, Click Add, then Select <microsoft access driver>, Next, Browse a path TO save your .DSN file, because that dsn file will contain the configuration where you store your database file(.mdb). Click Next, then Finish(you have created a new .dsn file) of which will be use in your TODBCConnection [FileDSN]. Save the .dsn in your current project path.


at your Lazarus form Drop: TSQLQuery TSQLTransaction TODBCConnection TDatasource TDBGrid

at TOBDCConnection Properties; Set; FileDSN : thepathofyour.dns_saved\the.dsn_filesaved eq., c:\mylazarus\project1\myFile.dsn then Set; Transaction: SQLTransaction1 Set; Username: admin Set; Connected: True; Do not put any other entries in the properties. (just that)


at TSQLTransaction Properties; Set; Database: ODBCConnection1 Set; Active: True


at TDatasource Properties; Set; DataSet: SQLQuery1


at TSQLQuery1 Properties; Set; Database: ODBCConnection1 Set; SQL: select * from TABLEyouknow Set; Active: True


at TDBGrid Properties; Set; Datasource: Datasource1

You will noticed an active data inside DBGrid.


Your '''FILE.dsn''' may contain like this.......... [ODBC] DRIVER=Microsoft Access Driver (*.mdb) UID=admin UserCommitSync=Yes Threads=3 SafeTransactions=0 PageTimeout=5 MaxScanRows=8 MaxBufferSize=2048 FIL=MS Access DriverId=25 DefaultDir= DBQ=YOUR_msaccess.mdb


Important: .dsn file is inside your current project application path. Unlike [System DSN] or [User DSN], they are configured by [ODBC Data Source Administrator] inside your system and '''cannot fly''',


BUT using FILE dsn? You can manage to edit each line as long as you understand it, and is a portable file in which you can fly anywhere.


File DSN


A file DSN is simply where the connection settings are written to a file. The reason for having a file DSN is if you want to distribute a data source connection to multiple users on different systems without having to configure a DSN for each system. For instance, I can create a file DSN to a reporting database on my desktop. I can then send the file to my users. My users can save the file DSN to their hard drives and then point their reporting applications at the file DSN.


Go and tell others about Lazarus.


But if FILE DSN won't work on you, try using SYSTEM DSN, and type at TODBCConnection Set; DatabaseName: yourSYSTEMDNS_ADDED

if USER dsn, SYSTEM dsn, FILE dsn wont work?


at SQLQuery1 Properties Set; UsePrimaryKeyAsKey: False



Working Source code sample:(Updated2_with_add_delete_update) http://www.mediafire.com/file/ne1jx3zpnwzefq3/msaccesstest2.zip


Enjoy Lazarus



<delphi> unit Unit1;

{$mode objfpc}{$H+}

interface

uses

 Classes, SysUtils, odbcconn, sqldb, db, FileUtil, LResources, Forms, Controls,
 Graphics, Dialogs, DBGrids, DbCtrls, StdCtrls, Printers, PrintersDlgs;

type

 { TForm1 }
 TForm1 = class(TForm)
   Button1: TButton;
   Button2: TButton;
   Button3: TButton;
   Button4: TButton;
   Datasource1: TDatasource;
   DBEdit1: TDBEdit;
   DBEdit2: TDBEdit;
   DBGrid1: TDBGrid;
   ODBCConnection1: TODBCConnection;
   PrintDialog1: TPrintDialog;
   SQLQuery1: TSQLQuery;
   SQLTransaction1: TSQLTransaction;
   procedure Button1Click(Sender: TObject);
   procedure Button2Click(Sender: TObject);
   procedure Button3Click(Sender: TObject);
   procedure Button4Click(Sender: TObject);
   procedure FormCloseQuery(Sender: TObject; var CanClose: boolean);
   procedure FormShow(Sender: TObject);
 private
   { private declarations }
   procedure PrintDbGrid(dbGrid:TdbGrid);
 public
   { public declarations }
 end; 

var

 Form1: TForm1; 

implementation

{ TForm1 }

procedure TForm1.FormShow(Sender: TObject); begin

 //connection
 ODBCConnection1.FileDSN := ExtractFilePath(Application.ExeName) + 'file.dsn';
 ODBCConnection1.Connected := True;
 ODBCConnection1.KeepConnection := True;
 //transaction
 SQLTransaction1.DataBase := ODBCConnection1;
 SQLTransaction1.Action := caCommit;
 SQLTransaction1.Active := True; //not applied to ms access(false)
 //SQLTransaction1.StartTransaction; //removed ONLY for lazarus-0.9.30.0-fcl-2.4.2
 SQLQuery1.DataBase := ODBCConnection1;
 SQLQuery1.UsePrimaryKeyAsKey := False;
 SQLQuery1.SQL.Text := 'select * from table1';


 //T.H.I.S  SQL String implementation is (ONLY) for MS_ACCESS
 //:id is the fieldname id
 SQLQuery1.deleteSQL.Text := 'delete from table1 where id=:id';
 //:name is the fieldname name
 SQLQuery1.updateSQL.Text := 'update table1 set name=:name where id=:id';


 DataSource1.DataSet := SQLQuery1;
 DBGrid1.DataSource := DataSource1;
 DBGrid1.ReadOnly := true;
 DBEdit1.DataField := 'id';
 DBEdit1.DataSource := DataSource1;
 DBEdit2.DataField := 'name';
 DBEdit2.DataSource := DataSource1;
 SQLQuery1.Open;

end;

function MulDiv(nNumber, nNumerator, nDenominator: Integer): Integer; begin

 Result:=Round(int64(nNumber)*int64(nNumerator)/nDenominator);

end; procedure TForm1.PrintDbGrid(dbGrid:TdbGrid); const

 LeftMargin = 0.05;
 TopMargin = 0.05;
 BottomMargin = 0.05;

var

 i: integer;
 x,y: integer;

begin

 if PrintDialog1.Execute then
   begin
   Printer.BeginDoc;
   Printer.Canvas.Font.Size := 12;
   y := Round(TopMargin*Printer.PageHeight);
   dbGrid.DataSource.DataSet.First;
   while not dbGrid.DataSource.DataSet.Eof do
     begin
     x := Round(LeftMargin*Printer.PageWidth);
     for i := 0 to dbGrid.DataSource.DataSet.FieldCount-1 do
       begin
       printer.Canvas.TextOut(x,y,dbGrid.DataSource.DataSet.Fields[i].AsString);
       x := x + MulDiv(dbGrid.Columns[i].Width,72, dbGrid.Width);
       end;
     dbGrid.DataSource.DataSet.Next;
     y := y + printer.Canvas.TextHeight('A');
     if y > (1-TopMargin-BottomMargin)* Printer.PageHeight then
       begin
       y := Round(TopMargin*Printer.PageHeight);
       Printer.NewPage;
       end;
     end;
   Printer.EndDoc;
   end
   else
   Form1.caption := 'NO PRINTER INSTALLED';

end;

procedure TForm1.Button1Click(Sender: TObject); begin PrintDbGrid(DBGrid1); end;

procedure TForm1.Button2Click(Sender: TObject); begin if button2.Caption = 'new' then begin

 SQLQuery1.Insert;
 button2.Caption := 'save';
 exit
 end
 else
 begin
   if (dbedit1.Text = ) or (dbedit2.Text = )
   then
   begin
   SQLQuery1.Cancel;
   end
   else
   begin
   if SQLQuery1.State = dsInsert then
      begin
      SQLQuery1.Post;
      SQLQuery1.ApplyUpdates;
      Form1.caption := 'ADDED';
      end;
   end;
 end;

button2.Caption := 'new'; end;

procedure TForm1.Button3Click(Sender: TObject); begin if SQLQuery1.RecordCount>0 then

 begin
 SQLQuery1.Delete;
 SQLQuery1.ApplyUpdates;
 '//REMOVED or ADD this 2 lines if there is a problem in your SQLdB
 '//THIS 2 lines are =**BUG**= of SQLdB for MS_Access dataBase'
 //SQLQuery1.Close;
 //SQLQuery1.Open;
 '//We don't need this 2 line's in other data models, like mySQL, sqLiTE, etc.,...'


 Form1.caption := 'DELETED';
 end;

end;

procedure TForm1.Button4Click(Sender: TObject); begin

 if SQLQuery1.RecordCount>0 then
 begin
  SQLQuery1.Edit;
  SQLQuery1.Post;
  Sqlquery1.ApplyUpdates;
  Form1.caption := 'UPDATED';
  end;

end;

procedure TForm1.FormCloseQuery(Sender: TObject; var CanClose: boolean); begin

   SQLQuery1.Close;
   CanClose := True;

end;

initialization

 {$I unit1.lrs}

end.

</delphi>