Solved

Database programming with MS Access

Posted on 2001-06-18
7
128 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
7 Comments
 
LVL 13

Expert Comment

by:Epsylon
Comment Utility
If CachedUpdates is false then do not call ApplyUpdates.
0
 

Author Comment

by:Tammi
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Expert Comment

by:NopparatM
Comment Utility
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
Comment Utility
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:mnasman
Comment Utility
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
Comment Utility
After trying everything, your solution worked for me.

Thanks!!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Get height of a forms top border. 3 32
PDF library for Delphi 2 89
ddeman not working in activex 3 77
How to debug For loops? 3 33
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

728 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

14 Experts available now in Live!

Get 1:1 Help Now