Solved

Update MS Access table through SQL

Posted on 2004-08-11
8
405 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
Technology Partners: 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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

690 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