Difference between revisions of "MS Access"

From Lazarus wiki
Jump to navigationJump to search
(New page: '''If you want to use microsoft access?''', then do the following; <nowiki> Goto your [Data sources (ODBC)] at the control panel administrative tools. Goto [File DSN] tab menu, Click Add...)
 
m (→‎Unix/Linux: Change mdbtools link to current location)
(37 intermediate revisions by 11 users not shown)
Line 1: Line 1:
'''If you want to use microsoft access?''', then do the following;
+
{{MS Access}}
  
<nowiki>
+
{{Infobox databases}}
Goto your [Data sources (ODBC)] at the control panel administrative  tools.
+
This page explains how to use a '''Microsoft Access''' database using [[ODBCConn|ODBC]] and FPC's built-in SQLDB.
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.
 
</nowiki>
 
  
  
'''at your Lazarus form'''
+
==MS Access specifics==
<nowiki>
 
Drop:
 
TSQLQuery
 
TSQLTransaction
 
TODBCConnection
 
TDatasource
 
TDBGrid
 
</nowiki>
 
  
'''at TOBDCConnection Properties;'''
+
===UsePrimaryKeyAsKey===
<nowiki>
 
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)
+
In the '''TSQLQuery''' properties, you may need to set
</nowiki>
+
Set UsePrimaryKeyAsKey: False
  
 +
===Get just-inserted autonumber primary key===
  
'''at TSQLTransaction Properties;'''
+
After inserting data, Access 2000 and higher supports getting the just-inserted autonumber primary key using this query:
<nowiki>
 
Set;
 
Database: ODBCConnection1
 
Set;
 
Active: True
 
</nowiki>
 
  
 +
<syntaxhighlight lang="SQL">
 +
SELECT @@IDENTITY
 +
</syntaxhighlight>
  
'''at TDatasource Properties;'''
+
=== ODBC drivers ===
<nowiki>
 
Set;
 
DataSet: SQLQuery1
 
</nowiki>
 
  
 +
==== Windows ====
  
 +
There are 2 different [[ODBCConn|ODBC]] drivers for Microsoft Access:
 +
* the old driver that can only use .mdb format files, driver name is "Microsoft Access Driver (*.mdb)". Included in many Windows versions (since Windows 2000 up to and including Vista?); downloadable for older Windows versions as part of the MDAC components or Jet components. 32 bit only.
 +
* the new driver that can access both .mdb and .accdb files, driver name is "Microsoft Access Driver (*.mdb, *.accdb)". Downloadable as the "Microsoft Access Database Engine 2010 Redistributable"; available as 32 and 64 bit.
 +
As usual, when using 32 bit Lazarus/FPC, use 32 bit ODBC drivers. When using 64 bit Lazarus/FPC, use 64 bit ODBC drivers.
  
'''at TSQLQuery1 Properties;'''
+
==== Unix/Linux ====
<nowiki>
 
Set;
 
Database: ODBCConnection1
 
Set;
 
SQL: select * from TABLEyouknow
 
Set;
 
Active: True
 
</nowiki>
 
  
 +
The [https://github.com/mdbtools/mdbtools mdbtools project] offers limited support for MS Accesss. It includes an ODBC driver which might be used. Try installing packages like mdbtools mdbtools-dev mdbtools-gmdb
  
'''at TDBGrid Properties;'''
+
At least on Debian, the ODBC driver name is "MDBTools".
<nowiki>
 
Set;
 
Datasource: Datasource1
 
</nowiki>
 
  
You will noticed an active data inside DBGrid.
+
==File-based DSN Instructions==
  
 +
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.
  
 +
If you want to use a file-based DSN with the SQLDB ODBC driver:
  
<nowiki>
+
=== Set up the File DSN ===
Your '''FILE.dsn''' may contain like this..........
 
  
 +
* Go to your [Data sources (ODBC)] at the control panel administrative  tools.
 +
* Go to [File DSN] tab menu, Click Add, then Select <microsoft access driver>.
 +
* Next, browse to your current Lazarus project path to save your .DSN file, because that dsn file will contain the configuration needed to access your database file (.mdb).
 +
* Click Next, then Finish (you now have created a new .dsn file) of which will be use in your TODBCConnection [FileDSN].
 +
 +
==== File DSN contents ====
 +
 +
For reference, a file DSN that refers to an MS Access database '''may''' look something '''like''' this:
 +
 +
<syntaxhighlight lang="DOS">
 
[ODBC]
 
[ODBC]
 
DRIVER=Microsoft Access Driver (*.mdb)
 
DRIVER=Microsoft Access Driver (*.mdb)
Line 89: Line 65:
 
DriverId=25
 
DriverId=25
 
DefaultDir=
 
DefaultDir=
DBQ=msaccess.mdb
+
DBQ=YOUR_msaccess.mdb
</nowiki>
+
</syntaxhighlight>
 +
 
 +
=== Configure your project to use file-based DSN ===
 +
 
 +
 
 +
Usage of grids and other data bound controls is the same as for any other SQLDB connector, so that is not covered here.
 +
 
 +
As you're using an ODBC connection, you should have a '''TODBCConnection''' object.
 +
 
 +
TOBDCConnection Properties:
 +
 
 +
FileDSN: the path+filename to the DSN file saved earlier, e.g. c:\mylazarus\project1\myFile.dsn
 +
Username: admin (or whatever username you need if you are using Access security) ''this parameter may not be needed if no MS Access security is used''
 +
Do not put any other entries in the properties.
 +
 
 +
 
 +
== System/User DSN ==
 +
 
 +
As explained in the [[ODBCConn|ODBC]] article, you can also use system or user DSNs, where connection settings are defined in the ODBC control panel instead of stored in a file.
 +
 
 +
The TODBCConnection should have
 +
DatabaseName: <name_of_your_DSN>
 +
 
 +
== DSN-less connection ==
 +
 
 +
As explained in the [[ODBCConn|ODBC]] article, you can also create a DSN-less connection to your Access database where you can specify all connection parameters in code, something like:
 +
 
 +
<syntaxhighlight lang=pascal>
 +
  //Notice we're using the new MS Access driver, we could probably use the old one too:
 +
  conn.Driver:='Microsoft Access Driver (*.mdb, *.accdb)';
 +
  conn.Params.Add('DBQ=c:\somedirectory\test.mdb');
 +
  ... add whatever parameters you want/need...
 +
</syntaxhighlight>
 +
 
 +
Here is an example how to query a table in an Access mdb file using TODBCConnection, TSQLTransaction and TSQLQuery (the mdb file exists in the same folder as the project binary):
 +
 
 +
<syntaxhighlight lang=pascal>
 +
procedure TForm1.Button1Click(Sender: TObject);
 +
begin
 +
  //connection
 +
  ODBCConnection1.Driver := 'Microsoft Access Driver (*.mdb, *.accdb)';
 +
  ODBCConnection1.Params.Add('DBQ=.\test.mdb');      // or specify full path to mdb file
 +
  ODBCConnection1.Params.Add('Locale Identifier=1031');
 +
  ODBCConnection1.Params.Add('ExtendedAnsiSQL=1');
 +
  ODBCConnection1.Params.Add('CHARSET=ansi');
 +
  ODBCConnection1.Connected := True;
 +
  ODBCConnection1.KeepConnection := True;
 +
   
 +
  //transaction
 +
  SQLTransaction1.DataBase := ODBCConnection1;
 +
  SQLTransaction1.Action := caCommit;
 +
  SQLTransaction1.Active := True;
 +
   
 +
  SQLQuery1.DataBase := ODBCConnection1;
 +
  SQLQuery1.UsePrimaryKeyAsKey := False;
 +
  SQLQuery1.SQL.Text := 'select * from Customers';
 +
  SQLQuery1.Open;
 +
end;
 +
</syntaxhighlight>
 +
 
 +
== Example code ==
 +
 
 +
=== Lazarus: data bound controls on forms ===
 +
 
 +
Working Source code sample:(Updated2_with_add_delete_update)
 +
http://www.mediafire.com/file/ne1jx3zpnwzefq3/msaccesstest2.zip ''[Dead link - November 2020]''
 +
 
 +
<syntaxhighlight lang=pascal>
 +
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)
 +
 
 +
  SQLQuery1.DataBase := ODBCConnection1;
 +
  SQLQuery1.UsePrimaryKeyAsKey := False;
 +
 
 +
  SQLQuery1.SQL.Text := 'select * from table1';
 +
 
 +
  // :id is the parameter for the field name id
 +
  SQLQuery1.deleteSQL.Text := 'delete from table1 where id=:id';
 +
  //:name is the parameter for the field name 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
 +
  //THESE 2 lines apparently work around a bug in SQLdB for MS_Access dataBase'
 +
  //**** missing reference to bugtracker item; please create a bugtracker item
 +
  //SQLQuery1.Close;
 +
  //SQLQuery1.Open;
 +
 
 +
 
 +
  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.
 +
</syntaxhighlight>
 +
 
 +
=== Create a database programmatically ===
 +
 
 +
Using either ODBC driver, you can programmatically create Microsoft Access databases.
 +
(Note: error handling has not been tested; please update page if you tested it)
 +
 
 +
Example program:
 +
 
 +
<syntaxhighlight lang=pascal>
 +
program CreateAccessDb;
 +
 
 +
{$mode objfpc}{$H+}
 +
 
 +
uses
 +
  {$IFDEF UNIX}{$IFDEF UseCThreads}
 +
  cthreads,
 +
  {$ENDIF}{$ENDIF}
 +
  Classes, sysutils,
 +
  LCLType;
  
 +
Const
 +
  ODBC_ADD_DSN=1;
 +
  ODBC_CONFIG_DSN=2;
 +
  ODBC_REMOVE_DSN=3;
 +
  ODBC_ADD_SYS_DSN=4;
 +
  ODBC_CONFIG_SYS_DSN=5;
 +
  ODBC_REMOVE_SYS_DSN=6;
 +
  ODBC_REMOVE_DEFAULT_DSN=7;
 +
function SQLConfigDataSource(hwndParent: Integer; fRequest: Integer;
 +
  lpszDriverString: PChar; lpszAttributes: PChar): Integer; stdcall; external 'odbccp32.dll';
 +
function SQLInstallerError(iError: integer; pfErrorCode: PInteger; lpszErrorMsg: string; cbErrorMsgMax: integer; pcbErrorMsg: PInteger): integer; stdcall; external 'odbccp32.dll';
  
'''Important:'''
+
function CreateAccessDatabase(DatabaseFile: string): boolean;
<nowiki>
+
var
.dsn file is inside your current project application path.
+
  DBPChar: PChar;
 +
  Driver: PChar;
 +
  ErrorCode, ResizeErrorMessage: integer;
 +
  ErrorMessage: PChar;
 +
  retCode: integer;
 +
begin
 +
driver := 'Microsoft Access Driver (*.mdb, *.accdb)';
 +
  { With this driver,
 +
  CREATE_DB/CREATE_DBV12 will create an .accdb format database;
 +
  CREATE_DBV4 will create an mdb
 +
  http://stackoverflow.com/questions/9205633/how-do-i-specify-the-odbc-access-driver-format-when-creating-the-database
 +
  }
 +
  DBPChar:=PChar('CREATE_DBV4="'+DatabaseFile+'"');
 +
  retCode := SQLConfigDataSource(Hwnd(nil), ODBC_ADD_DSN, Driver, DBPChar);
 +
  if retCode<>0 then
 +
  begin
 +
    //try alternate driver
 +
    Driver := 'Microsoft Access Driver (*.mdb)';
 +
    DBPChar:=PChar('CREATE_DB="'+DatabaseFile+'"');
 +
    retCode := SQLConfigDataSource(Hwnd(nil), ODBC_ADD_DSN, Driver, DBPChar);
 +
  end;
 +
  if retCode=0 then
 +
  begin
 +
    result:=true;
 +
  end
 +
  else
 +
  begin
 +
    result:=false;
 +
    ErrorCode:=0;
 +
    ResizeErrorMessage:=0;
 +
    // todo: verify how the DLL is called - use pointers?; has not been tested.
 +
    GetMem(ErrorMessage,512);
 +
    try
 +
      SQLInstallerError(1, @ErrorCode, ErrorMessage, SizeOf(ErrorMessage), @ResizeErrorMessage);
 +
    finally
 +
      FreeMem(ErrorMessage);
 +
    end;
 +
    raise Exception.CreateFmt('Error creating Access database: %s', [ErrorMessage]);
 +
  end;
 +
end;
  
 +
begin
 +
  writeln('Result: ');
 +
  write(CreateAccessDatabase('d:\cop\t\bla.mdb'));
 +
end.
 +
</syntaxhighlight>
  
Unlike [System DSN] or [User DSN],  they are configured by [ODBC Data Source Administrator] inside your system and '''cannot fly''',
+
=== Character set issues ===
</nowiki>
 
  
 +
Old Access databases are encoded using an ANSI character set (I don't know about new ones - they probably use wide strings). Therefore, non-ASCII characters are not displayed correctly in a DBGrid or other data-aware controls. This can be fixed by attaching handlers to the OnGetText and OnSetText events of the dataset after opening:
  
'''BUT using FILE dsn?'''
+
<syntaxhighlight lang=pascal>
<nowiki>
+
uses
You can manage to edit each line as long as you understand it, and is a portable file in which you can fly anywhere.
+
  lconvencoding;
 +
 +
procedure TForm1.SQLQuery1AfterOpen(DataSet: TDataSet);
 +
var
 +
  i: Integer; 
 +
begin
 +
  for i:=0 to DataSet.Fields.Count-1 do
 +
  begin
 +
    if DataSet.Fields[i].DataType=ftString then
 +
    begin
 +
      DataSet.Fields[i].OnGetText := @ConvertFromDB;
 +
      DataSet.Fields[i].OnSetText := @ConvertToDB;
 +
    end;
 +
  end;
 +
end;
 +
 +
procedure TForm1.ConvertFromDB(Sender: TField; var aText: string; DisplayText: Boolean);
 +
begin
 +
  if not Sender.IsNull then
 +
    aText := WinCPToUTF8(Sender.AsString);  // if encoded in windows default code page
 +
end;
 +
 +
procedure TForm1.ConvertToDB(Sender: TField; const aText: string);
 +
begin
 +
  if aText <> '' then
 +
    Sender.Value := UTF8ToWinCP(aText);
 +
end;
 +
</syntaxhighlight>
  
 +
== Example database ==
 +
Microsoft Access ships with the Northwind example database.
  
Go and tell others about Lazarus.
+
If you only have the Access Runtime installed, you can use this alternative database from the Mondial project as a test database:
 +
[http://databases.about.com/od/sampleaccessdatabases/a/Microsoft-Access-Sample-Database-Countries-Cities-And-Provinces.htm] ''[Dead link - November 2020]''
  
 +
== See also ==
  
Regards,
+
* [[ODBCConn]] The ODBC connector this article uses
Roca Robin
 
</nowiki>
 

Revision as of 07:28, 15 November 2020

English (en) español (es) français (fr) русский (ru)

Databases portal

References:

Tutorials/practical articles:

Databases

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

This page explains how to use a Microsoft Access database using ODBC and FPC's built-in SQLDB.


MS Access specifics

UsePrimaryKeyAsKey

In the TSQLQuery properties, you may need to set Set UsePrimaryKeyAsKey: False

Get just-inserted autonumber primary key

After inserting data, Access 2000 and higher supports getting the just-inserted autonumber primary key using this query:

SELECT @@IDENTITY

ODBC drivers

Windows

There are 2 different ODBC drivers for Microsoft Access:

  • the old driver that can only use .mdb format files, driver name is "Microsoft Access Driver (*.mdb)". Included in many Windows versions (since Windows 2000 up to and including Vista?); downloadable for older Windows versions as part of the MDAC components or Jet components. 32 bit only.
  • the new driver that can access both .mdb and .accdb files, driver name is "Microsoft Access Driver (*.mdb, *.accdb)". Downloadable as the "Microsoft Access Database Engine 2010 Redistributable"; available as 32 and 64 bit.

As usual, when using 32 bit Lazarus/FPC, use 32 bit ODBC drivers. When using 64 bit Lazarus/FPC, use 64 bit ODBC drivers.

Unix/Linux

The mdbtools project offers limited support for MS Accesss. It includes an ODBC driver which might be used. Try installing packages like mdbtools mdbtools-dev mdbtools-gmdb

At least on Debian, the ODBC driver name is "MDBTools".

File-based DSN Instructions

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.

If you want to use a file-based DSN with the SQLDB ODBC driver:

Set up the File DSN

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

File DSN contents

For reference, a file DSN that refers to an MS Access database may look something 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

Configure your project to use file-based DSN

Usage of grids and other data bound controls is the same as for any other SQLDB connector, so that is not covered here.

As you're using an ODBC connection, you should have a TODBCConnection object.

TOBDCConnection Properties:

FileDSN: the path+filename to the DSN file saved earlier, e.g. c:\mylazarus\project1\myFile.dsn Username: admin (or whatever username you need if you are using Access security) this parameter may not be needed if no MS Access security is used Do not put any other entries in the properties.


System/User DSN

As explained in the ODBC article, you can also use system or user DSNs, where connection settings are defined in the ODBC control panel instead of stored in a file.

The TODBCConnection should have DatabaseName: <name_of_your_DSN>

DSN-less connection

As explained in the ODBC article, you can also create a DSN-less connection to your Access database where you can specify all connection parameters in code, something like:

  //Notice we're using the new MS Access driver, we could probably use the old one too:
  conn.Driver:='Microsoft Access Driver (*.mdb, *.accdb)';
  conn.Params.Add('DBQ=c:\somedirectory\test.mdb');
  ... add whatever parameters you want/need...

Here is an example how to query a table in an Access mdb file using TODBCConnection, TSQLTransaction and TSQLQuery (the mdb file exists in the same folder as the project binary):

procedure TForm1.Button1Click(Sender: TObject);
begin
  //connection
  ODBCConnection1.Driver := 'Microsoft Access Driver (*.mdb, *.accdb)';
  ODBCConnection1.Params.Add('DBQ=.\test.mdb');      // or specify full path to mdb file
  ODBCConnection1.Params.Add('Locale Identifier=1031');
  ODBCConnection1.Params.Add('ExtendedAnsiSQL=1');
  ODBCConnection1.Params.Add('CHARSET=ansi');
  ODBCConnection1.Connected := True;
  ODBCConnection1.KeepConnection := True;
     
  //transaction
  SQLTransaction1.DataBase := ODBCConnection1;
  SQLTransaction1.Action := caCommit;
  SQLTransaction1.Active := True;
     
  SQLQuery1.DataBase := ODBCConnection1;
  SQLQuery1.UsePrimaryKeyAsKey := False;
  SQLQuery1.SQL.Text := 'select * from Customers';
  SQLQuery1.Open;
end;

Example code

Lazarus: data bound controls on forms

Working Source code sample:(Updated2_with_add_delete_update) http://www.mediafire.com/file/ne1jx3zpnwzefq3/msaccesstest2.zip [Dead link - November 2020]

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)

  SQLQuery1.DataBase := ODBCConnection1;
  SQLQuery1.UsePrimaryKeyAsKey := False;

  SQLQuery1.SQL.Text := 'select * from table1';

  // :id is the parameter for the field name id
  SQLQuery1.deleteSQL.Text := 'delete from table1 where id=:id';
  //:name is the parameter for the field name 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
  //THESE 2 lines apparently work around a bug in SQLdB for MS_Access dataBase'
  //**** missing reference to bugtracker item; please create a bugtracker item
  //SQLQuery1.Close;
  //SQLQuery1.Open;


  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.

Create a database programmatically

Using either ODBC driver, you can programmatically create Microsoft Access databases. (Note: error handling has not been tested; please update page if you tested it)

Example program:

program CreateAccessDb;

{$mode objfpc}{$H+}

uses
  {$IFDEF UNIX}{$IFDEF UseCThreads}
  cthreads,
  {$ENDIF}{$ENDIF}
  Classes, sysutils,
  LCLType;

Const
   ODBC_ADD_DSN=1;
   ODBC_CONFIG_DSN=2;
   ODBC_REMOVE_DSN=3;
   ODBC_ADD_SYS_DSN=4;
   ODBC_CONFIG_SYS_DSN=5;
   ODBC_REMOVE_SYS_DSN=6;
   ODBC_REMOVE_DEFAULT_DSN=7;
function SQLConfigDataSource(hwndParent: Integer; fRequest: Integer;
  lpszDriverString: PChar; lpszAttributes: PChar): Integer; stdcall; external 'odbccp32.dll';
function SQLInstallerError(iError: integer; pfErrorCode: PInteger; lpszErrorMsg: string; cbErrorMsgMax: integer; pcbErrorMsg: PInteger): integer; stdcall; external 'odbccp32.dll';

function CreateAccessDatabase(DatabaseFile: string): boolean;
var
  DBPChar: PChar;
  Driver: PChar;
  ErrorCode, ResizeErrorMessage: integer;
  ErrorMessage: PChar;
  retCode: integer;
begin
	driver := 'Microsoft Access Driver (*.mdb, *.accdb)';
  { With this driver,
  CREATE_DB/CREATE_DBV12 will create an .accdb format database;
  CREATE_DBV4 will create an mdb
  http://stackoverflow.com/questions/9205633/how-do-i-specify-the-odbc-access-driver-format-when-creating-the-database
  }
  DBPChar:=PChar('CREATE_DBV4="'+DatabaseFile+'"');
  retCode := SQLConfigDataSource(Hwnd(nil), ODBC_ADD_DSN, Driver, DBPChar);
  if retCode<>0 then
  begin
    //try alternate driver
    Driver := 'Microsoft Access Driver (*.mdb)';
    DBPChar:=PChar('CREATE_DB="'+DatabaseFile+'"');
    retCode := SQLConfigDataSource(Hwnd(nil), ODBC_ADD_DSN, Driver, DBPChar);
  end;
  if retCode=0 then
  begin
    result:=true;
  end
  else
  begin
    result:=false;
    ErrorCode:=0;
    ResizeErrorMessage:=0;
    // todo: verify how the DLL is called - use pointers?; has not been tested.
    GetMem(ErrorMessage,512);
    try
      SQLInstallerError(1, @ErrorCode, ErrorMessage, SizeOf(ErrorMessage), @ResizeErrorMessage);
    finally
      FreeMem(ErrorMessage);
    end;
    raise Exception.CreateFmt('Error creating Access database: %s', [ErrorMessage]);
  end;
end;

begin
  writeln('Result: ');
  write(CreateAccessDatabase('d:\cop\t\bla.mdb'));
end.

Character set issues

Old Access databases are encoded using an ANSI character set (I don't know about new ones - they probably use wide strings). Therefore, non-ASCII characters are not displayed correctly in a DBGrid or other data-aware controls. This can be fixed by attaching handlers to the OnGetText and OnSetText events of the dataset after opening:

uses
  lconvencoding;
 
procedure TForm1.SQLQuery1AfterOpen(DataSet: TDataSet);
var
  i: Integer;  
begin
  for i:=0 to DataSet.Fields.Count-1 do
  begin
    if DataSet.Fields[i].DataType=ftString then
    begin
      DataSet.Fields[i].OnGetText := @ConvertFromDB;
      DataSet.Fields[i].OnSetText := @ConvertToDB;
    end;
  end;
end;
 
procedure TForm1.ConvertFromDB(Sender: TField; var aText: string; DisplayText: Boolean);
begin
  if not Sender.IsNull then
    aText := WinCPToUTF8(Sender.AsString);  // if encoded in windows default code page
end;
 
procedure TForm1.ConvertToDB(Sender: TField; const aText: string);
begin
  if aText <> '' then 
    Sender.Value := UTF8ToWinCP(aText);
end;

Example database

Microsoft Access ships with the Northwind example database.

If you only have the Access Runtime installed, you can use this alternative database from the Mondial project as a test database: [1] [Dead link - November 2020]

See also

  • ODBCConn The ODBC connector this article uses