Solved

Database programming with MS Access

Posted on 2001-06-18
7
132 Views
Last Modified: 2010-04-06
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
Comment
Question by:Tammi
[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
7 Comments
 
LVL 13

Expert Comment

by:Epsylon
ID: 6204160
If CachedUpdates is false then do not call ApplyUpdates.
0
 

Author Comment

by:Tammi
ID: 6204202
CachedUpdates was false so I removed ApplyUpdates, still get the error ""tblContact:  Cannot Perform this Operation on a Closed Dataset".
0
 
LVL 1

Expert Comment

by:Phoenix_s
ID: 6204334
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
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!

 

Expert Comment

by:NopparatM
ID: 6204737
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
 
LVL 9

Accepted Solution

by:
ITugay earned 200 total points
ID: 6205545
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
 
LVL 22

Expert Comment

by:Mohammed Nasman
ID: 6205755
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
 

Author Comment

by:Tammi
ID: 6215196
After trying everything, your solution worked for me.

Thanks!!
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

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
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…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

724 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