[Solved] Getting started in Base, Working on multiple DB's?

[RandV80]

Hello,

I'm experienced with MS Access but completely new to Base. I have a project that I'd normally do in Access, but it's unavailable on this particular server so I'm going to see what Base could do.

Anyways first order of business, with Access I can start a new .mdb file, and under the Tables section create new local tables, as well as link or import tables from different databases. I have ODBC connections setup to the two databases I need to work with, and I'm going to want to keep a separate table to store the last ID queried. However I don't see how I can connect these together in Base? If I start a new blank file I don't see anywhere to link outside tables. When starting a new Base file I can select one of the ODBC connections, but that seems to create a file that is exclusive to the specific ODBC database. When I tried creating my separate table in one of those it created it in the actual live database. So how exactly do I do this?

Here's the full list of what I'm trying to accomplish, all easy for me in Access but I'm off to a poor start in Base:

1. Read records from one column from one table from ODBC database 1
2. This column is an XML form, which I need to process ideally with code to pull one value out of...where is the code editor?
3. Update record in table in ODBC database 2 with value from step 2.
4. When all records are read, save the last ID value from db1 so the next time it runs it only reads new records
5. Capture steps 1-4 in a macro/script so that it can be automated with the Windows Task Scheduler

Is this all possible in Base?

[Villeroy]

This is just a tiny addition to the office suite so you can link office suites to data. Yes, to some extent you can create a new database in a single file. But this is a million of miles away from a database development suite like MS Access.
Whatever you are trying to do here, do it with a dedicated database software and link the resulting record sets to your office suite where you can feed your spreadsheets, form letters, labels etc with it.

[Villeroy]

Connect a Base document to database 1. Add a view which selects all the fields to be exported in the right order of fields.
Connect a Base document to database 2. This database should be editable in Base, i.e. the target table must have a primary key.
Open both documents side by side and drag the view from db1 onto the table of db2.
You will get an error about index violation. Click "Continue" to proceed anyway. This will import all records which do not violate the primary key.

[RandV80]

Connect a Base document to database 1. Add a view which selects all the fields to be exported in the right order of fields.
Connect a Base document to database 2. This database should be editable in Base, i.e. the target table must have a primary key.
Open both documents side by side and drag the view from db1 onto the table of db2.
You will get an error about index violation. Click "Continue" to proceed anyway. This will import all records which do not violate the primary key.

The OO documentation seems to treat Views the same as Queries, but when you say view do you mean selecting Insert/View (Design or Simple)? It works on the destination database 2, but the source database 1 (the MS SQL Server DB) generates the error:

[Microsoft][ODBC SQL Server Driver][SQL Server]'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name.

If I look at the SQL query Base is auto generating, it looks like this:

Code: Select all

SELECT "OrderNum", "OrderXML" FROM "UDC"."dbo"."SalesOrders" AS "SalesOrders" WHERE "OrderNum" = 'W000000597''

While on the other hand to create this view in MS SQL server the code is:

Code: Select all

CREATE VIEW dbo.TEST
AS
SELECT OrderNum, OrderXML FROM dbo.zeckoShopSalesOrders WHERE OrderNum = 'W000000597'

Looking up the error seems to suggest that in the first query the portion FROM "UDC"."dbo"."SalesOrders" needs to be cut to FROM "dbo"."SalesOrders", as it is in the first query, but the same error is generated. I also noticed if I try and add the CREATE VIEW portion to the Base query, "VIEW" comes in as green text rather than blue suggesting it's not picked up as a command word.

Am I missing something here or is it not possible to create a view from base to an MS SQL Server database?

[Villeroy]

Views are queries in the syntax and name space of the database backend. Queries belong to the Base frontend only. MySQL has a slightly different syntax than HSQL which is shipped with OpenOffice. In fact we do not even know which software we are discussing right now.

You can try to copy a query to another database table but I would be surprised if it works ... No, it doesn't.
You can copy (or even link) a query, table or view to a spreadsheet area, select the area and paste to another table icon. This always worked for me but all that data conversion can be very, very slow. But you can use the sheet to filter the new records where A.ID > MAX(B.ID) or filter by some timestamp field.

[RandV80]

Views are queries in the syntax and name space of the database backend. Queries belong to the Base frontend only. MySQL has a slightly different syntax than HSQL which is shipped with OpenOffice. In fact we do not even know which software we are discussing right now.

You can try to copy a query to another database table but I would be surprised if it works ... No, it doesn't.
You can copy (or even link) a query, table or view to a spreadsheet area, select the area and paste to another table icon. This always worked for me but all that data conversion can be very, very slow. But you can use the sheet to filter the new records where A.ID > MAX(B.ID) or filter by some timestamp field.

Okay thanks for the help, I guess Base can work for me in some areas but in this specific instance it isn't going to work out.

And sorry for not being clear, DB1 is on Microsoft SQL Server 2012 (the one I was having trouble getting a view with and I did notice was quite slow), while DB2 is a obscure one called Pervasive SQL.

[Mr.Dandy]

This is a RFE: https://bz.apache.org/ooo/show_bug.cgi?id=42464

[Villeroy]

This is a RFE: https://bz.apache.org/ooo/show_bug.cgi?id=42464

12 years old. Even before the realease date of OpenOffice.org 2.0 which was the first version with the so called "Base document". The Base crap should be removed leaving the database connectivity, forms and reports as it used to work so fine in OOo 1.x.

[Villeroy]

However, we can use a stand-alone form, link a main form to database A, a subform to database B so B is filtered by A and then we can use the subfom's filtered grid view (last icon of navigation toolbar) to copy records to a spreadsheet, text document or to another database table.
This trick does not work with forms that are embedded in the Base document because they refer to nothing but the embedding database.