MS Access

From Lazarus wiki
Jump to: navigation, search

This page explains how to use a Microsoft Access database.

Database Programming Articles

References:

Tutorials/practical articles:

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 



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.
Retrieved from "http://wiki.lazarus.freepascal.org/index.php?title=MS_Access&oldid=56660"
Personal tools