• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • Last Modified:

Update MS Access table through SQL

Hi,

Database    :     MS Access
Tablename :     ChangeDate

Fieldname  Status       Date
                 New         23-12-2004
                 New         04-03-2004
                 New         13-11-2004
                 New         11-06-2004

I need to update one field (Status) in the dateset whenever the date in that record is in the past.

Result:
Fieldname  Status       Date
                 New         23-12-2004
                 Old           04-03-2004
                 New         13-11-2004
                 Old          11-06-2004

Can anybody give me the correct statement?

Thanks, Stef
0
Stef Merlijn
Asked:
Stef Merlijn
  • 6
  • 2
1 Solution
 
esoftbgCommented:
procedure TForm1.btnUpdateClick(Sender: TObject);
var
  S:      string;
begin
  S := FormatDateTime('yyyymmdd', Date);
  AdoQuery1.Connection := AdoConnection;
  AdoQuery1.Sql.Text := ''
                      + ' update CHANGEDATE '
                      + ' set STATUS='
                      + '''' + 'Old' + ''''
                      + ' where DATES<=:TODAY'
                      + '';
  AdoQuery1.Parameters.ParamByName('TODAY').Value := Date;
  AdoQuery1.ExecSQL;
end;

procedure TForm1.ADOQueryCHANGESNewRecord(DataSet: TDataSet);
begin
  ADOQueryCHANGES.FieldByName('STATUS').AsString := 'New';
end;

emil
0
 
esoftbgCommented:
unit Unit1_Q_21090692;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics,
  Controls, Forms, Dialogs, DB, DBTables, Grids, DBGrids,
  Buttons, StdCtrls, ExtCtrls, DBCtrls, ComObj, ADODB, ToolWin, ComCtrls;

type
  TForm1 = class(TForm)
    dsaLIST: TDataSource;
    ADOQueryCHANGES: TADOQuery;
    ADOCommand: TADOCommand;
    ToolBar: TToolBar;
    DBNavigator: TDBNavigator;
    ADOConnection: TADOConnection;
    btnUpdateNew: TButton;
    DBGrid1: TDBGrid;
    ADOQueryUpdate: TADOQuery;
    btnUpdateOld: TButton;
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure btnUpdateNewClick(Sender: TObject);
    procedure ADOQueryCHANGESNewRecord(DataSet: TDataSet);
    procedure btnUpdateOldClick(Sender: TObject);
  private{ Private declarations }
  public { Public declarations }
    ConnStr:   string;
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
var
  FN:     string;
  R:      string;
begin
  R := '';
  FN := ExtractFilePath(Application.ExeName) + 'CHANGES.MDB';
  ConnStr := 'Data Source=' + FN + ';Provider=Microsoft.Jet.OLEDB.4.0';
  if FileExists(FN) then
  try
    ADOConnection.ConnectionString := ConnStr;
    ADOConnection.Open;
  finally
    ADOQueryCHANGES.Connection := AdoConnection;
    ADOQueryCHANGES.Active := True;
    ADOQueryCHANGES.FieldByName('ID').Visible := False;
  end;
end;

procedure TForm1.FormDestroy(Sender: TObject);
var
  I:      Integer;
begin
  for I := 0 to ADOConnection.DataSetCount-1 do
    ADOConnection.DataSets[I].Active := False;
end;

procedure TForm1.btnUpdateNewClick(Sender: TObject);
begin
  ADOQueryUpdate.Connection := AdoConnection;
  ADOQueryUpdate.Sql.Text := ''
                           + ' update CHANGEDATE '
                           + ' set STATUS='
                           + '''' + 'New' + ''''
                           + ' where DATES>=:TODAY'
                           + '';
  ADOQueryUpdate.Parameters.ParamByName('TODAY').Value := Date;
  ADOQueryUpdate.ExecSQL;
  ADOQueryCHANGES.Requery;
end;

procedure TForm1.btnUpdateOldClick(Sender: TObject);
begin
  ADOQueryUpdate.Connection := AdoConnection;
  ADOQueryUpdate.Sql.Text := ''
                           + ' update CHANGEDATE '
                           + ' set STATUS='
                           + '''' + 'Old' + ''''
                           + ' where DATES<:TODAY'
                           + '';
  ADOQueryUpdate.Parameters.ParamByName('TODAY').Value := Date;
  ADOQueryUpdate.ExecSQL;
  ADOQueryCHANGES.Requery;
end;

procedure TForm1.ADOQueryCHANGESNewRecord(DataSet: TDataSet);
begin
  ADOQueryCHANGES.FieldByName('STATUS').AsString := 'New';
end;

end.

//........

object Form1: TForm1
  Left = 210
  Top = 113
  BorderIcons = [biSystemMenu, biMinimize]
  BorderStyle = bsSingle
  Caption = 'Insert / Edit Form'
  ClientHeight = 382
  ClientWidth = 376
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  OnDestroy = FormDestroy
  PixelsPerInch = 96
  TextHeight = 13
  object ToolBar: TToolBar
    Left = 0
    Top = 0
    Width = 376
    Height = 29
    ButtonHeight = 24
    Caption = 'ToolBar'
    TabOrder = 0
    object DBNavigator: TDBNavigator
      Left = 0
      Top = 2
      Width = 180
      Height = 24
      DataSource = dsaLIST
      VisibleButtons = [nbFirst, nbPrior, nbNext, nbLast, nbInsert, nbEdit, nbPost, nbCancel, nbRefresh]
      TabOrder = 0
    end
  end
  object btnUpdateNew: TButton
    Left = 4
    Top = 34
    Width = 72
    Height = 25
    Caption = 'Update New'
    TabOrder = 1
    OnClick = btnUpdateNewClick
  end
  object DBGrid1: TDBGrid
    Left = 80
    Top = 32
    Width = 256
    Height = 344
    DataSource = dsaLIST
    TabOrder = 2
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'MS Sans Serif'
    TitleFont.Style = []
  end
  object btnUpdateOld: TButton
    Left = 4
    Top = 64
    Width = 72
    Height = 25
    Caption = 'Update Old'
    TabOrder = 3
    OnClick = btnUpdateOldClick
  end
  object dsaLIST: TDataSource
    DataSet = ADOQueryCHANGES
    Left = 20
    Top = 196
  end
  object ADOQueryCHANGES: TADOQuery
    OnNewRecord = ADOQueryCHANGESNewRecord
    Parameters = <>
    SQL.Strings = (
      'SELECT * FROM CHANGEDATE')
    Left = 18
    Top = 168
  end
  object ADOCommand: TADOCommand
    CommandText =
      'CREATE TABLE MY_DATA (FIRST_NAME VARCHAR(16), LAST_NAME VARCHAR(' +
      '16), INFO TEXT, COUNTRY TEXT)'
    Parameters = <>
    Left = 54
    Top = 170
  end
  object ADOConnection: TADOConnection
    LoginPrompt = False
    Left = 20
    Top = 128
  end
  object ADOQueryUpdate: TADOQuery
    Connection = ADOConnection
    Parameters = <>
    Left = 24
    Top = 248
  end
end
0
 
esoftbgCommented:
The above example expected the database is in the same directory with .exe
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Stef MerlijnDeveloperAuthor Commented:
Thanks.
Why do you this?

var
  S:      string;
begin
  S := FormatDateTime('yyyymmdd', Date);

I don't see you use it futheron.

Stef
0
 
esoftbgCommented:
this is an unsuccessful attemt to assign TODAY as string, please ignore it....

download working example from :
page:        http://www.geocities.com/esoftbg/
  link:        Q_21090692.zip
0
 
esoftbgCommented:
> this is an unsuccessful attemt to assign TODAY as string, please ignore it....

mean that:

var
  S:      string;

and

  S := FormatDateTime('yyyymmdd', Date);

to be ignored....
0
 
Stef MerlijnDeveloperAuthor Commented:
Thanks Emil for your help and for the example on your site it works great.

Stef
0
 
esoftbgCommented:
Hi Delphiwizard,
You are welcome !
I did improve the example on my site. If you are interested, download it again ....
Best Regards,
Emil
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now