Copy a table from one database into another - [Solved]

[JohnLM 27]

I have two MS Access databases that I am reading through firedac in Delphi.  I have an activity that I do every day: 

 

1) I copy the source "data_Notes.mdb" and rename the copied version to "db.mdb".  
2) Then I open the "db.mdb" in a delphi app for viewing certain info. 
3) Then, when I'm finished, I delete the db file "db.mdb" and go through steps 1 through 3, thus rinse and repeat. 

 

However, I'd like to automate the process like this: 

(assuming I kept the "db.mdb" file in tact) 

 

1) I delete the table "tblMyNotes" -- I already know how to do this in delphi code
2) copy the table "tblMyNotes" from the source db "data_MyNotes.mdb" -- I don't know how to do in delphi code
3) do whatever i want as usual with the table "tblMyNotes" 
4) when I'm done, go through steps 1-3 again, re-copy it again, thus, rinse and repeat. 

 

The problem I am having is how to do the copying of the table from the source db to the "db.mdb" through delphi code.

 

I am trying to find code that is as minimal and understandable as possible. 

 

So far, I have found this link, which seems to have what I am looking for, (a one-line SQL script to copy the table) but I can not understand fully what is going on, in order to get it to work, assuming this is one example of doing it. 

 

link: https://stackoverflow.com/questions/12242772/easiest-way-to-copy-a-table-from-one-database-to-another

 

I have the following on my form: 

 

connection -> table -> ds-source 

 

I am testing various scenarios and have trying the following script, but it fails: 

 

Table.SQL.Add('CREATE TABLE tblTest SELECT * FROM "g:\data_notes.mdb".tblMyNotes'); 

Please advise your correction/suggestions, and TIA. 

 

Edited 15 hours ago by JohnLM

[Lars Fosdal 1956]

In non-technical terms, what is the purpose of this activity?

Does the schema definition of tblMyNotes change frequently or periodically?
Is the definition of the table or the processes that write to the db, outside of your control?
 

[Anders Melander 2132]

1 hour ago, JohnLM said:

Table.SQL.Add('CREATE TABLE tblTest SELECT * FROM "g:\data_notes.mdb".tblMyNotes'); 

The solution you are trying is for MySQL and will not work with an Access database.

 

Apart from that, possible solutions depends on your answers to Lars' questions.

[JohnLM 27]

Progress update. . . 

 

Okay, I finally figured it out.  

 

SELECT * INTO TheNewTableName FROM '[g:\master.mdb].TheOldTableName WHERE 0=1;

and then, 

 

INSERT INTO TheNewTablename SELECT * FROM ' + '[g:\master.mdb].TheOldTablename';

 

I created a new stand-alone test database(s) so that I would not damage the others.   Anyway, it works and now I have to do a few changes and a few more test runs before I settle on this method.  

 

PS:  To answer your questions. . . 

 

These two databases are mine.  I have control.  The first one, the main db (the source db) is a flat db, containing one table of about 14 fields.  I only use two of those fields for daily updates throughout the day.  This is a very small db of about 51MB in size and has been growing very slowly over the years, probably because some parts of the data are the same in many ways, I guess. 

 

Edited 8 hours ago by JohnLM
correction to the first 'SELECT * . . . ' statement