[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Database programming with MS Access

Posted on 2001-06-18
7
Medium Priority
?
136 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 800 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

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…
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…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

649 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