Board index » delphi » Access Novice Needs Help - Updating Fields in database from another database

Access Novice Needs Help - Updating Fields in database from another database

Hi,
I need to update over 50 fields in 500 or so records in an Access 2000
database, three or four times a week. The database contains about 500
records that have 70 -90 fields, of which I only need to update specific
ones 50 or so. All of the Updating information that I have seen and read so
far seems to be related to updating only one or two fields at most.

The source data is downloaded in CSV format and imported into an Access file
for the updating, I can't seem to get any further than this so far. ANY Help
is greatly appreciated.

Thank you,

Paul

 

Re:Access Novice Needs Help - Updating Fields in database from another database


Hi Paul,

You might not need to upload them into Access to do the updates, provided that the Field Names appear as the first
record in the CSV file, and that they match the Field Names exactly in your Access table.

Here's some sample code to illustrate what I'm thinking.  Basically, it loads the header record into one string list,
and then each record, one at a time, into another.  Then it uses a counter to loop through the StringList updating the
fields from those in the CSV file.

This method does require a lot of overhead, since it's opening and closing a lookup query for each record of the CSV
file.  It might be more optimized using the Lookup, Locate, or Filter methods/properties of TADODataset instead.

I hope this helps get you started in solving your problem.  Good luck!

-John

{=========================================================}

procedure TForm1.DoIt;
var
  InFile : TStringList;
  Header : TStringList;
  Values : TStringList;
  c1, c2 : Integer;
begin
  { Create the StringLists }
  InFile := TStringList.Create;
  Header := TStringList.Create;
  Values := TStringList.Create;

  try
    { Load the File }
    InFile.LoadFromFile(Edit1.Text);

    { Load the Header Record }
    Header.CommaText := InFile[0];

    for c1 := 1 to InFile.Count - 1 do begin
      { Load the Record into the "Values" TStringList }
      Values.CommaText := InFile[c1];

      with QryLookup do
        try
          { Assuming the Lookup field is the first column in each record }
          CommandText := Format('SELECT * FROM MyTable WHERE %s = ''%s''', [Header[0], Values[0]]);
          Open;

          if RecordCount > 0 then
            try
              Edit;

              { Cycle through remaining Fields in this Record and update them }
              for c2 := 1 to Header.Count - 1 do
                FindField(Header[c2]).Value := Values[c2];

              Post;
            except
              on E: Exception do begin
                Cancel;

                { Make a note of the Error in a Memo Box on the form }
                Memo1.Lines.Add(Format('Error Posting Record [%s].', [Values[0]]));
                Memo1.Lines.Add(Format('Reason: %s', [E.Message]));
                Memo1.Lines.Add('');
              end; (* on *)
            end; (* try..except *)
        finally
          Close;
        end; (* try..finally *)
    end; (* for *)
  finally
    Values.Free;
    Header.Free;
    InFile.Free;
  end;
end;

{=========================================================}

Quote
"Paul Wilson" <wil...@qwest.net> wrote in message news:3ef80133$1@newsgroups.borland.com...
> Hi,
> I need to update over 50 fields in 500 or so records in an Access 2000
> database, three or four times a week. The database contains about 500
> records that have 70 -90 fields, of which I only need to update specific
> ones 50 or so. All of the Updating information that I have seen and read so
> far seems to be related to updating only one or two fields at most.

> The source data is downloaded in CSV format and imported into an Access file
> for the updating, I can't seem to get any further than this so far. ANY Help
> is greatly appreciated.

> Thank you,

> Paul

Re:Access Novice Needs Help - Updating Fields in database from another database


Quote
>All of the Updating information that I have seen and read so
>far seems to be related to updating only one or two fields at most.

Most people don't write out examples with more than a couple of fields but it
isn't any different you just set the value for each of the 50 fields

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:Access Novice Needs Help - Updating Fields in database from another database


Hi everyone,
Thank you for the reply. I found a component called SMImport from Scalabium
and it so far seems to handle my need well. I am going to attempt the method
recommended by John Bowman also. I really appreciate the assistance from
all, and find the Borland newsgroups to be invaluable.

Good day,

Paul

Quote
"Paul Wilson" <wil...@qwest.net> wrote in message

news:3ef80133$1@newsgroups.borland.com...
Quote
> Hi,
> I need to update over 50 fields in 500 or so records in an Access 2000
> database, three or four times a week. The database contains about 500
> records that have 70 -90 fields, of which I only need to update specific
> ones 50 or so. All of the Updating information that I have seen and read
so
> far seems to be related to updating only one or two fields at most.

> The source data is downloaded in CSV format and imported into an Access
file
> for the updating, I can't seem to get any further than this so far. ANY
Help
> is greatly appreciated.

> Thank you,

> Paul

Other Threads