[Solved] Getting started in Base, Working on multiple DB's?
[Solved] Getting started in Base, Working on multiple DB's?
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?
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?
Last edited by RandV80 on Tue Jan 24, 2017 4:38 am, edited 1 time in total.
Open Office 4.1.3
Re: Getting started in Base... Working on multiple DB's?
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.
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Getting started in Base... Working on multiple DB's?
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.
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Getting started in Base... Working on multiple DB's?
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:Villeroy wrote: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.
If I look at the SQL query Base is auto generating, it looks like this:[Microsoft][ODBC SQL Server Driver][SQL Server]'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name.
Code: Select all
SELECT "OrderNum", "OrderXML" FROM "UDC"."dbo"."SalesOrders" AS "SalesOrders" WHERE "OrderNum" = 'W000000597''
Code: Select all
CREATE VIEW dbo.TEST
AS
SELECT OrderNum, OrderXML FROM dbo.zeckoShopSalesOrders WHERE OrderNum = 'W000000597'
Am I missing something here or is it not possible to create a view from base to an MS SQL Server database?
Open Office 4.1.3
Re: Getting started in Base... Working on multiple DB's?
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.
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Getting started in Base... Working on multiple DB's?
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.Villeroy wrote: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.
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.
Open Office 4.1.3
Re: [solved]Getting started in Base... Working on multiple D
This is a RFE: https://bz.apache.org/ooo/show_bug.cgi?id=42464
OpenOffice 4.1.12 - Windows 10
Re: [solved]Getting started in Base... Working on multiple D
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.Mr.Dandy wrote:This is a RFE: https://bz.apache.org/ooo/show_bug.cgi?id=42464
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Getting started in Base, Working on multiple DB
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.
This trick does not work with forms that are embedded in the Base document because they refer to nothing but the embedding database.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice