Solved

Database programming with MS Access

Posted on 2001-06-18
7
129 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Syntax Check Delphi Seattle IOS app without MAC ? 1 98
Delphi - replicating a form 8 74
creating threads in delphi 1 105
Firemonkey webbrowser scrollbars ? 1 41
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 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…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

810 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