Making and breaking ODBC connections ?

[taylorkh]

I posted this to the "Unofficial" forum a while back and received a work around for my immediate need. However, I wonder what the "real" answer is??? Cut and past options are OK for a few hundred or even thousands of rows. However, in a real world setting I need to connect to a selected few tables in a DB2 on MVS database with 2000+ tables, 1000+ views and MANY tens of millions of rows of data.


Being new to Base but not to databases, please allow me a newbie sort of question...

How to I create and then remove a connection to an external database? An ODBC data source is what I am attempting to connect to at the moment.

If I create a new database the "Database wizard" allows me to "connect to an existing database." I did this and the tables of my source database (an MS Access database with an ODBC connection established on Win XP) become available. I need to make tables in the Base database from the external data and then remove the ODBC connections. I cannot simply delete the connection as I would in MS Access for example. It seems that Base is trying to delete the table in the Access database not the ODBC connection to the table.

If I create a new, empty Base database I can find no way to create an ODBC connection to an external source. I am probably missing something here.

TIA,

Ken

[TerryE]

The connection wizard allows you to map external tables into your Base D/B. These mapped tables a virtual and not physical copies so if you the remove the link the local view disappears. What you seem to want to do is to map to an external B/D; copy some view into a local table and iterate on this. You can do this programmatically as described in Section 12 of the SDK using one of OOo supported scripting languages such as Basic.

However there isn't a painfree manual way of doing what you want.

[taylorkh]

So how do I invoke the "connection wizard" once I have opened an existing Base file? and how do I remove the link to the external table? Those are the tasks I was unable to do.

Thanks,

Ken

[TerryE]

You need to start writing scripts, so pick a language and read up on it. Download the SDK and read sections 3 and 12 at a minimum. That's what I did when I decided to start to move from MSOffice to OOo. I can't read them for you. If you have some specific Qs on this material then come back to the forum and ask them. Sorry.

[DrewJensen]

I think there is a little misunderstanding here.

Base can work with exactly one data source at a time - no more. This is different then in MS Access. There are no exceptions to that rule.

So when you create a Base file that connects to an external datasource such as a mdb file that is the only data source that file can work with. If you create a table in that file the file is created in the mdb file, not the Base odb file.

If you create a new Base file, just File > New > OK you have embedded the data for a HSQLdb database into the file and this file can work with that data source only.

If you want to copy a table from a MDB file to a ODB file with the embedded data type HSQLdb then you need to create two Base files - one connecting to each of the data sources - at that point simply drag the tables from one to the other, or drag queries.

[TerryE]

D, my understanding isn't any different from your last para, except that if you want to do this lots of tables you will need to script this process.

[DrewJensen]

For the moment that is correct. A couple of new API calls where added to the package, currently only available in the 2.4 developer builds just so that an extension could be added to do multiple table copies. There are a few guys working on those extensions and I am sure at least one will be ready to go by then time the 2.4 RC happens. That should be I suppose 9, 10 weeks.

There is also the possibility that an addon is in the works not for Base for one of the FOSS ETL packages to support native Base databases.