SQL add statement for 2 tables ms access Delphi

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.

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;

Select allOpen in new window

DELETE * FROM songs >> DELETE FROM songs

How do you mean?

sorry misread, but didn't work

And DELETE FROM email as well?

yes both didn't work.

I see, you need to execute them separately

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

I meant to remove the * of course!

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

Thanks but that still didn't work, still got the same error message.

What is the exact error message please?

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).

btw behind the syntax error I forgot .'.

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;

Nope not working.

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;

             

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?

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.

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

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?

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:
4. 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;

Select allOpen in new window

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?

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.