SQL add statement for 2 tables ms access Delphi

I have this code to empty a local ms access and add records from a online database (this works).

But now I have 1 ms access file with 2 tables inside, I want to empty and add records for both tables, but when I try this I get the message that I need to select a table to empty.

Can someone help me with this?

Here is the code that works for 1 table (added after the FROM statement the email, songs. Normally it's only songs).
procedure TfrmSplash.LoadExternalDB(var Msg: TMessage);
var ConTypes : Integer;
    Source, Target : TAdQuery;
    teller : integer;
begin
  case Msg.WParam of
    0:
    if not fRunning then
    begin
      fRunning := True;
      try
        ConTypes := INTERNET_CONNECTION_MODEM + INTERNET_CONNECTION_LAN + INTERNET_CONNECTION_PROXY;
        if InternetGetConnectedState(@ConTypes, 0) = True then
        begin
          AD_MySQL.Connected := True;
          AccessConnect;
          Source := TADQuery.create(Self);
          try
            Target := TADQuery.create(self);
            try
              Source.connection := AD_MySQL;
              Target.connection := AD_Access;
              Source.Close();
              Target.Close();
              Target.SQL.Add('DELETE * FROM email, songs');
              Target.ExecSQL;
              Source.SQL.Text := 'SELECT * FROM email, songs';
              Target.SQL.Text := Source.SQL.Text;
              Source.Active := True;
              Target.Active := True;
              while not Source.eof do
              begin
                Target.Append;
                for teller := 0 to Source.fieldcount -1 do
                  Target.Fields[teller].Value := Source.Fields[teller].value;
                Target.Post;
                progressCopy.Position :=  progressCopy.Position + 1;
                Source.next;
              end;
              Source.active := false;
              Target.active := false;
            finally
              Target.Free;
            end;
          finally
            Source.free;
          end;
        end;
      finally
        fRunning := False;
      end;
      PostMessage(Handle, WM_USER, 1, 0);
    end;
    1: Close;
  end;
end;

Open in new window

delphiuser2009Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

8080_DiverCommented:
I seriously doubt that SQL will know what to do with the code you have written. ;-)
For instance, 'SELECT * FROM email, songs' is going to result in a set of rows that have every column from both tables in each row and you will have every row of each table matched with every row of the other table.  If you have 100 rows in the emails table and 100 rows in the songs table, your results will have 10,000 rows!
You need to have separate DELETE and INSERT statements for the 2 tables.
 You should also get in the habit of actually listing the columns you are selecting and the ones you are inserting into.  That will prevent problems such as trying to insert a value into an AutoIncrement column.
0
delphiuser2009Author Commented:
Thanks for the reply, I changed it but got a syntax error:

Component FROM has a syntax error.

Here is the code I changed:
procedure TfrmSplash.LoadExternalDB(var Msg: TMessage);
var ConTypes : Integer;
    Source, Target : TAdQuery;
    teller : integer;
begin
  case Msg.WParam of
    0:
    if not fRunning then
    begin
      fRunning := True;
      try
        ConTypes := INTERNET_CONNECTION_MODEM + INTERNET_CONNECTION_LAN + INTERNET_CONNECTION_PROXY;
        if InternetGetConnectedState(@ConTypes, 0) = True then
        begin
          AD_MySQL.Connected := True;
          AccessConnect;
          Source := TADQuery.create(Self);
          try
            Target := TADQuery.create(self);
            try
              Source.connection := AD_MySQL;
              Target.connection := AD_Access;
              Source.Close();
              Target.Close();
              Target.SQL.Add('DELETE * FROM songs');
              Target.SQL.Add('DELETE * FROM email');
              Target.ExecSQL;
              Source.SQL.Text := 'SELECT * FROM songs';
              Source.SQL.Text := 'SELECT * FROM email';
              Target.SQL.Text := Source.SQL.Text;
              Source.Active := True;
              Target.Active := True;
              while not Source.eof do
              begin
                Target.Append;
                for teller := 0 to Source.fieldcount -1 do
                  Target.Fields[teller].Value := Source.Fields[teller].value;
                Target.Post;
                progressCopy.Position :=  progressCopy.Position + 1;
                Source.next;
              end;
              Source.active := false;
              Target.active := false;
            finally
              Target.Free;
            end;
          finally
            Source.free;
          end;
        end;
      finally
        fRunning := False;
      end;
      PostMessage(Handle, WM_USER, 1, 0);
    end;
    1: Close;
  end;
end;

Open in new window

0
cyberkiwiCommented:
DELETE * FROM songs >> DELETE FROM songs
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

delphiuser2009Author Commented:
How do you mean?
0
delphiuser2009Author Commented:
sorry misread, but didn't work
0
cyberkiwiCommented:
And DELETE FROM email as well?
0
delphiuser2009Author Commented:
yes both didn't work.
0
cyberkiwiCommented:
I see, you need to execute them separately

              Target.SQL.Text := 'DELETE * FROM songs';
              Target.ExecSQL;
              Target.SQL.Text := 'DELETE * FROM email';
              Target.ExecSQL;
0
cyberkiwiCommented:
I meant to remove the * of course!

              Target.SQL.Text := 'DELETE FROM songs';
              Target.ExecSQL;
              Target.SQL.Text := 'DELETE FROM email';
              Target.ExecSQL;
0
delphiuser2009Author Commented:
Thanks but that still didn't work, still got the same error message.
0
cyberkiwiCommented:
What is the exact error message please?
0
delphiuser2009Author Commented:
The error message is in dutch, but here is the translation:

The Component FROM contains a syntax error. (the component I use is anydac to connect to the mysql database).
0
delphiuser2009Author Commented:
btw behind the syntax error I forgot .'.
0
cyberkiwiCommented:
AnyDac is probably parsing the sql, so try this

              Target.SQL.Text := 'DELETE * FROM songs';
              Target.ExecSQL;
              Target.SQL.Text := 'DELETE * FROM email';
              Target.ExecSQL;
0
delphiuser2009Author Commented:
Nope not working.
0
FactorBCommented:
Target.Close;
Target.SQL.Clear;
Target.SQL.Add('DELETE FROM songs');
Target.ExecSQL;
Target.Close;
Target.SQL.Clear;
Target.SQL.Add('DELETE FROM email');
Target.ExecSQL;

The above is completely correct statement, if you still get an error search for it in other code, for example, Source.SQL.Text := 'SELECT * FROM songs';
               Source.SQL.Text := 'SELECT * FROM email';
the final result will be only 'SELECT * FROM email' so try to modify your code a little and  try to copy paste this code

procedure TfrmSplash.LoadExternalDB(var Msg: TMessage);
var ConTypes : Integer;
    Source, Target : TAdQuery;
    teller, iloop : integer;
begin
  case Msg.WParam of
    0:
    if not fRunning then
    begin
      fRunning := True;
      try
        ConTypes := INTERNET_CONNECTION_MODEM + INTERNET_CONNECTION_LAN + INTERNET_CONNECTION_PROXY;
        if InternetGetConnectedState(@ConTypes, 0) = True then
        begin
          AD_MySQL.Connected := True;
          AccessConnect;
          Source := TADQuery.create(Self);
          try
            Target := TADQuery.create(self);
            try
              Source.connection := AD_MySQL;
              Target.connection := AD_Access;
              Target.Close;
              Target.SQL.Clear;
              Target.SQL.Add('DELETE FROM songs');
              Target.ExecSQL;
              Target.Close;
              Target.SQL.Clear;
              Target.SQL.Add('DELETE FROM email');
              Target.ExecSQL;
              for iloop:=1 to 2 do begin
               Source.Close;
               Source.SQL.Clear;
               if iloop=1 then Source.SQL.Text := 'SELECT * FROM songs' else Source.SQL.Text := 'SELECT * FROM email';
               Source.Open;
               Target.Close;
               Target.SQL.Clear;
               Target.SQL.Text := Source.SQL.Text;
               Target.Open;
               while not Source.eof do
               begin
                 Target.Append;
                 for teller := 0 to Source.fieldcount -1 do begin
                   Target.Fields[teller].Value := Source.Fields[teller].value;
                 end;
                 Target.Post;
                 progressCopy.Position :=  progressCopy.Position + 1;
                 Source.next;
               end;
              end;// iloop              
              Target.Close;
              Source.Close;
            finally
              Target.Free;
            end;
          finally
            Source.free;
          end;
        end;
      finally
        fRunning := False;
      end;
      PostMessage(Handle, WM_USER, 1, 0);
    end;
    1: Close;
  end;
end;

             
0
cyberkiwiCommented:
Is the error still on the same line of code (delete from) and is the error text still "The Component FROM contains a syntax error." ?

If you change the line back to

Target.SQL.Text := 'DELETE * FROM email, songs';

Does it go back to the original error of "need to select a table to empty"?

FactorB,
.Clear->.Add is exactly the same as .Text
The extra .Close between the 2 is not necessary since .ExecSQL never Opens a query. Unless AnyDAC does something non-standard?
0
FactorBCommented:
Agree on the above, that was how I standardly use the command, btw I am writing in the morning on the time between wake up and work. Need to go, catch you all later.
B.

DELETE * FROM  is normally DELETE FROM, if that does not work try 'TRUNCATE TABLE tablename', again may not work for all dbs and providers.
0
delphiuser2009Author Commented:
Thanks FactorB, I tried it and got an error (see image).

The text is mostly dutch, so here the translation:
ODBC Microsoft Access-Driver, syntaxerror (operator missing) in query-expression Pa_RaM001 name.

Any idea what it means or is.
printscreen.jpg
0
FactorBCommented:
Have you tried

'DELETE * FROM songs'
'DELETE * FROM email'

instead of

'DELETE FROM songs'
'DELETE FROM email'

Anyway seems that some of the queries is erroneous, can you try to debug it and see which one?
0
8080_DiverCommented:
Let me see if I can summarize the experiments so far and then see if the OP can provide an insight or two:
  1. The original code (with only one table) worked perfectly . . . for the one table.
  2. Adding the second table name, separated from the first by only a comma, resulted in the initial error message re: selecting a table to empty.
  3. Suggestions have been made to execute two (2) separate delete commands in two (2) separate formats:
    1. DELETE FROM songs and then DELETE FROM email;
    2. DELETE FROM songs and then DELETE * FROM email;
Now, this is where it gets fuzzy (for me, at least).  
  • Were the two separate DELETE queries executed separately with a reulsting failure?  
  • Were both suntax versions separately executed with a resulting failure?
So, delphiuser2009,
  • Can you provide the exact statement that was executed that resulted in the error message you provided?
  • Can you try each of the snippets (appropriately placed in your code) and let us know what happens?
TRIAL_1:

              Target.SQL.Add('DELETE FROM songs');
              Target.ExecSQL;
              Target.Close;
              Target.SQL.Clear;
              Target.SQL.Add('DELETE FROM email');
              Target.ExecSQL;

TRIAL_2:
              Target.SQL.Add('DELETE * FROM songs');
              Target.ExecSQL;
              Target.Close;
              Target.SQL.Clear;
              Target.SQL.Add('DELETE * FROM email');
              Target.ExecSQL;

Open in new window

0
delphiuser2009Author Commented:
I know that the problem is in the code with source.next (if I set a breakpoint at the target.close the on after the source.next, I get the error message). When I set a breakpoint at source.next no error but the program stops because of teh breakpoint.

So my thought is that the problem lies with the source.next.

Any idea?
0
8080_DiverCommented:
Let me get this straight:
  • First you delete everything in the Songs table;
  • Then you delete everything in the Emails table
  • Then you open a query that is designed to SELECT from the Songs table;
  • Then you try to READNEXT on that query;
  • Now you are trying to figure out why you get an error?
Well, if there are no rows in the Songs table, when you open the query, you probably won't have an EOF condition because you haven't exactly accessed a row in the table.  However, when you do a READNEXT, there won't be anything to read and you WILL get an error.
What is it that you think you are going to be able to append to the Target table by reading from a table you have just emptied?
I'm betting that adding an AND (Source.RecordCount  > 0) to your WHILE loop control condition will preclude you're attempt to read from an empty table.
0
delphiuser2009Author Commented:
I found out the I had a mistypo of the mysql table.

So my old code still works (tested it).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.