Solved

Update MS Access table through SQL

Posted on 2004-08-11
8
399 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
  • 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

930 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now