Copy an entire table from one Access DB to another
lazarus / 16-Databases

----------------------------------------------------------------------
biazarr · 2016-07-13 09:48

Hey folks,

I've got the following problem: I need to copy an entire table from
one mdb to another. My current idea would be to temporarely load the
table from mdb A into a ListView using an ODBCConnection
(TODBCConnection, TSQLTransaction, TSQLQuery) then put the whole data
from the Listview and insert it into mdb B using another ODBC
Connection.

But that solution seems so unnecessarily complicated, so i wondered if
there is a way to do this in a more elegant way.

EDIT: Just wanted to make clear: I already googled this, but haven't
found anything that is helpful.

----------------------------------------------------------------------
tintinux · 2016-07-13 09:58

Hi

Can't you use a Sql instruction like   Code: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
newtable SELECT * FROM oldtable  on your Access database with ODBC.

If this is supported, you just have to run it with a TSQLScript.

If not, the best is to export in a SDF file, or even in a TStringList
if the table is not big, and then import.

In all cases, , no need of visual component.

Regards

----------------------------------------------------------------------
biazarr · 2016-07-13 10:05

Thanks for your reply.

Well will the SQL instruction work if i use two seperate databases?

----------------------------------------------------------------------
tintinux · 2016-07-13 11:06

With Access and ODBC, I don't know, I have not used them for years,
sorry !

You could try :

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
onedb.newtable SELECT * FROM anotherdb.oldtable

----------------------------------------------------------------------
Zath · 2016-07-13 11:56

A real Heath Robinson way would be to create the empty table then
simply select all the records from table A, (Copy / Ctrl+C), open up
the new empty table B, (Paste / CTRL+V) into the new table.

----------------------------------------------------------------------
biazarr · 2016-07-14 09:58

Ah well sorry, it seems i might not have given enough information.
I'm programming this in Lazarus (1.6, FPC 3.0.0).

The tool has to copy several tables from one mdb-file to another.
Since this has to be done at least every month it is not very
practical to do this by simply copy&pasting the tables, a tool where
you simply hit the "Start" Button is far more useful on the long run.

----------------------------------------------------------------------
biazarr · 2016-07-14 10:19

Okay, before I get another reply like that: I have good reasons to
create a new tool for this. I really appreciate your effort put in it,
and i thank you for it, but it's not helping.

I have good reasons to code a tool in Lazarus (e.g. it's only a part
of a bigger process), so i need a solution to do it in Lazarus.

----------------------------------------------------------------------
wp · 2016-07-14 10:44

Here is a Lazarus solution usint an ODBCConnection:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button2Click(Sender: TObject);const  NEW_DB = 'copy.mdb';begin 
if FileExists(NEW_DB) then    DeleteFile(NEW_DB); 
CreateAccessDatabase(NEW_DB);   ODBCConnection1.ExecuteDirect('SELECT
* INTO Invoices IN ' + NEW_DB + ' FROM Invoices');end;Use the
CreateAccessDatabase from the wiki http://wiki.freepascal.org/MS_Acces
s#Create_a_database_programmatically

----------------------------------------------------------------------
biazarr · 2016-07-14 11:14

Now this is something I can work with. Thank you!

----------------------------------------------------------------------
Zath · 2016-07-15 16:20

Quote from: biazarr on July 14, 2016, 01:14:49 pmNow this is something
I can work with. Thank you!

Sorry, I assumed you wanted a quick, one off, easy solution.

wp's simple 10 lines is the sort of solution I like too.