Solved

Update MS Access table through SQL

Posted on 2004-08-11
8
403 Views
Last Modified: 2010-04-05
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
Comment
Question by:Delphiwizard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
8 Comments
 
LVL 12

Expert Comment

by:esoftbg
ID: 11779281
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
 
LVL 12

Accepted Solution

by:
esoftbg earned 500 total points
ID: 11779334
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
 
LVL 12

Expert Comment

by:esoftbg
ID: 11779340
The above example expected the database is in the same directory with .exe
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Delphiwizard
ID: 11779345
Thanks.
Why do you this?

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

I don't see you use it futheron.

Stef
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 11779388
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
 
LVL 12

Expert Comment

by:esoftbg
ID: 11779679
> 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
 

Author Comment

by:Delphiwizard
ID: 11780865
Thanks Emil for your help and for the example on your site it works great.

Stef
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 11781741
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question