Macro that copy/paste table from one .odb file into another

[Balam]

Looking for a way to automate copying a table from one .odb (linking to a Calc spreadsheet) to another .odb (with embedded tables). I need this because the SQL in the linking .odb file is limited (cfr. a previous question I asked), while an .odb file with embedded data has all the HSQLDB commands available. It would also make it possible to pick up data from more than one Calc file.

Doing the copy/paste manually is pretty easy
1. open the source .odb file and copy the table
2. open the destination .odb and paste into the tables section, a Copy Table window will pop up
3. choose the desired options in the Copy Table window (I need definition and data)
4. click next and choose needed columns (I need all)
5. click next to define the Type formatting (the defaults are ok to me)
6. click create and skip creation of primary key
I now have a copy of the data that originally resided in my Calc file. This data is embedded in the .odb file, so I can analyse it with advanced queries. The data is read-only because I didn't define a primary key, but that's fine to me, as changing and adding data needs to be done in the originating Calc file.

I am now looking for a way to automate these 6 steps. Since recording macros ain't possible in Base, unfortunately, I can't cheat on the things this wizard is doing.

Up to now, I can get to the collection of tables (GetTables()) and the individual tables (getByName()), but I don't find an easy method to do some copy /paste action (I am using XRay to search for available properties, methods, ...).

I would like to work something out that stays as close as possible to the way the wizard is managing it: just accepting all columns, detected type formatting and skipping a primary key. This way I can loosely connect to different Calc files and resolve, if needed, some data anomalies within my queries.

As far as I see, I would need:
- a method to pick up the definition of the source table (definition of the columns and data types is enough)
- a method to use this definition to create another table
- a method to copy the records from the first table into the second one
In the worst case I will have to enumerate all kind of properties to let them match, in the best case there is some nifty CopyTable(default_options) routine that would make the whole stuff far easier. Have been looking meanwhile, but didn't find it yet.

Is it an option to use SQL in the macro (executeQuery() method) ? Don't know if it can retrieve the definition of the table in the linking .odb file.

Thanks for any advise, help …

PS. Would be nice if the SQL for File-based drivers had the same possibilities as HSQLDB. Is there some place to launch such a request ?

[eremmel]

Never done this myself, but a search on the forum (macro copy table base) showed me this. This is the other way around, but it might help you further.

[Balam]

@eremmel
Yep, I already stumbled upon that post. The other way around (Base into Calc) is easier: In a spreadsheet you can just drop content in cells, but when dropping content into a database, there has to be a match between the type of content and the definition of the field (column).
The code Villeroy suggests in that post has some interesting routines to import data (getImportDescriptor(), doImport()), but they are specific to import into Calc. If something similar would exist to import into Base, that would be great.

[eremmel]

I expect that with a result-set like object there will be meta data around. Villeroy might know more in this area.