Difference between revisions of "MS Access"
Line 137: | Line 137: | ||
− | |||
− | |||
− | |||
− | |||
Revision as of 10:50, 21 May 2011
This page explains how to use a Microsoft Access database.
References:
Tutorials/practical articles:
Databases |
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>