SELECT @@IDENTITY

[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

//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...

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;

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.

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.

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;