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

Database programming with MS Access

I haven't done much development with databases so I need some help.

I have two tables, I need to search one table for records with a certain string in the Type field and if I find a record with that string I need to delete the record.  The code I have for this is as follows:

with tblContact do
  begin
    SearchOptions := [loCaseinsensitive];
    if Locate ('Type', 'Bradford', SearchOptions) then
      Delete;
      ApplyUpdates;
  end;

I keep getting a message "tblContact:  Cannot Perform this Operation on a Closed Dataset".

If I put a tblContact.Open statement in, I get the a  message saying the table is already open.

Once I get this working, I then need to look in the registry for a certain value and then search my second table for records with this value in the Area field and add those records to the first table.  I was going to try using the BatchMove component with the batAppend, but I believe that would append the entire second table.  
Any help would be appreciated :)
0
Tammi
Asked:
Tammi
1 Solution
 
EpsylonCommented:
If CachedUpdates is false then do not call ApplyUpdates.
0
 
TammiAuthor Commented:
CachedUpdates was false so I removed ApplyUpdates, still get the error ""tblContact:  Cannot Perform this Operation on a Closed Dataset".
0
 
Phoenix_sCommented:
make the table active before doing all that

you can't search an inactive table so that'll be where the error is originating, not even getting to the delete or applyupdates
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
NopparatMCommented:
You can check state of the table before do that like this

with tblContact do
 begin
   If State = dsInactive Then
      Open;
   If (State = dsInsert) or (State = dsEdit) then
      Post;
   SearchOptions := [loCaseinsensitive];
   if Locate ('Type', 'Bradford', SearchOptions) then
     Delete;
     ApplyUpdates;
 end;
0
 
ITugayCommented:
Hi Tammi,
why do not use TQuery?

var
  Q: TQuery;
begin
  Q := TQuery.Create(nil);
  Q.DatabaseName := 'your alias';
  Q.SQL.Text := 'delete from Table_Name where Type = "Bradford"';
  Q.ExecSQL;
  Q.Free;
end;

-----
Igor
0
 
Mohammed NasmanSoftware DeveloperCommented:
Hello

  It's seems that ur dataset not open, so try to open it before you u call the locate method, this code will work fine with you

// add these two lines before ur code
if tblContact.Active = false then
    tblContact.Active := true;

with tblContact do
 begin
   SearchOptions := [loCaseinsensitive];
   if Locate ('Type', 'Bradford', SearchOptions) then
     Delete;
  end;

Best regards
Mohammed Nasman
0
 
TammiAuthor Commented:
After trying everything, your solution worked for me.

Thanks!!
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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