Am I making the right decision with Base?

Discuss the database features
Post Reply
jm-trigger
Posts: 5
Joined: Sat Nov 13, 2010 11:00 am

Am I making the right decision with Base?

Post by jm-trigger »

So, I've spent all day yesterday messing with Base, looking through tutorials, the online documentation and this board. And I'm still not quite sure on whether Base is the right tool for the job...

For years I've created the tools, spreadsheets and "faux" databases my company needed in Excel using VBA. Now we're at a point where things are getting too big. Even our modern machines are struggling with the size of the spreadsheets and the sheer amount of code running in the background. Yeah, Excel was never meant to handle this amount of data, hundreds of spreadsheets with data being cross-referenced at all times. This is obviously a job for a proper relational database with forms and reports. I don't have much experience in that regard, know little about SQL and the likes, but I'm sure I'll get a handle on it eventually. I had to teach myself VBA in a fairly short time and that worked out pretty well... I think.

Anyway. My main concerns are multi user access/multi mode, protection/security and speed. Right now we're talking about an office network of ~5 users. I'm confused about multi mode. Is it too complicated/are there better databases or frontends out there to set up something that several users in a network can access at the same time? Or is it near impossible to do this with Base/HSQLDB? Would i be better off with an external database, using Base as the frontend primarily? If so, are there free or low-cost alternatives here? If i could edit the tables, create forms and reports for an existing database in Base, actually setting up a non-HSQLDB database would seem like a pretty straightforward thing to do to me, since everything after the initial setup could be done in Base, which seemed fairly user-friendly to me. Or am I missing something here? From what I can tell password protection and security features are nowhere to be found in Base. Is there a way around this? It also seems like remote access with HSQLDB is a hassle and not very secure. This isn't something I need right now, but I could see us using it at some point in the future. I also keep reading about problems with forms and reports for existing databases...

As you can see, for a newbie like me this is a hard decision to make. Maybe with all my concerns and all the potential workarounds I should look into both a different RDBMS and a different frontend? I know little about available licensing models or potentially free alternatives, so it would be enough for me to know if there are low-cost or free alternatives for the RDBMS component and maybe even the frontend (should Base turn out to be insufficient for that task as well).

Any advice from more experienced people would be greatly appreciated!
OpenOffice 3.2 on Windows XP
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Am I making the right decision with Base?

Post by RPG »

Hello

I think, HSQLDB is capable to do your task.
There is no problem to chose an other backend.

I think for more information read posts of dacm. Maybe start with this one.

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Am I making the right decision with Base?

Post by FJCC »

Be sure to check out this list of Some RDBMS Pro's and Con's related to ooBase:
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
Steve R.
Posts: 163
Joined: Mon Sep 21, 2009 12:06 am
Location: Morehead City, North Carolina

Re: Am I making the right decision with Base?

Post by Steve R. »

I share your concern. Currently, I have developed a multi-user MS Access database running for my work environment. I am experimenting with BASE and MYSQL, as personal projects, to move away from MS Windows into LINUX. I've successfully imported a magazine database into BASE and MYSQL, but progress has been haphazard and slow in adapting the user interface. But i will keep plugging away at it.

Hopefully as we learn more, BASE will become more understandable. The big benefit, if we can get BASE to work, would be a divorce from MS Access.
(PS: I do like MS Access, but I want to move to LINUX open source programming.)
Ubuntu 16.04 and Windows 10
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Am I making the right decision with Base?

Post by DACM »

That's a lot of questions so it's hard to find a place to start answering. AFAIK, Base is the premier, FLOSS, front-end. But you're a Windows user so MS Access is an even better option using the free runtime for multi-user deployment, although you're already bumping the max users for MS Access alone. But that's not ultimately a limitation at all because MS Windows smart client products look and feel like MS Access products but scale to thousands of users (at a price). And there's many options in between with MS Access. There's also FileMaker Pro but that can get pricey as you pay for each user (~$280/user). But then if you're a hand-coder, there's always good options (Java, WPF and LINQ for .Net, etc.).

On the other hand, Base+HSQLDB 2.0 is one of the easiest, and among the most secure, multi-user, LAN solutions. The security features include: encrypted network protocol & database, SQL-standard user rights & roles, restricted Tables & Views, and perhaps restricted schema (schema appear to be supported with the Base+HSQLDB 2.0 upgrade; simply delete "default_schema=true"). So Base allows extensive database security features, but they're a function of the back-end as opposed to the Base front-end. The Quick Fix-associated, HSQLDB 2.0 installers will have your Base environment configured for hundreds of users in minutes (multiversion [MVCC] transaction support is available as necessary). After a 5 minute download and install process, you'll have one-click, seamless, client-server access using the provided Windows script (START.vbs). Then setup each client/user/group just as you would with any SQL back-end. As long as you're using Base as the exclusive front-end, even a remote (internet-based) database is relatively safe and secure with HSQLDB 2.0 or H2. It's only when you move into thin-client tools like web-browser forms and general-public access leveraging PHP/Perl/Python to access the back-end server that security concerns force the issue towards MySQL, PostgreSQL, Firebird, Oracle, SQL Server, etc. But if that's down the road for you, then SQL SCRIPT commands or Base Table/View drag&drop procedures could perform the necessary migration between Base-compatible back-ends; and you'll want to enable schema now so your Forms, etc. won't require modification upon migration.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
jm-trigger
Posts: 5
Joined: Sat Nov 13, 2010 11:00 am

Re: Am I making the right decision with Base?

Post by jm-trigger »

Thanks for the replies. Much appreciated.


DACM, I had already read/skimmed many of your posts from the other forum that have been linked in this thread. They are very helpful and insightful. Truthfully, I suppose I had hoped there would be an easier way to do everything I wanted. Granted, the "Quick Fix" guide you wrote is fairly straightforward. I suppose I was just a little intimidated by the language and jargon in most of your posts. But now, from what I understand this new, simpler guide makes a lot of the older ones redundant...?

I'm still a tad confused about the multiple options in the Quick Fix guide. For a user like me, who is looking for multi user/networking support and is starting a new database (nothing to migrate) would you recommend going with HSQLDB 2.0 straight away?

And just so I'm sure I'm not getting anything wrong here: This installer would have to be run on the computer that acts as the server only? After that would I still need your older "multi-user server mode tutorial"? Or does the installer set up the respective computer as the server already? And the clients would only have to install vanilla Base for use as the frontend and then connect to the DB on the server? Because when you say "Then setup each client/user/group just as you would with any SQL back-end.", I'm not entirely sure what that entails. Excuse my ignorance. :oops:

Would this also allow me to implement password protection/user log-in options as well?

Also, would it be smart to switch to the other forum for this? Am I asking too many questions? :P
OpenOffice 3.2 on Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Am I making the right decision with Base?

Post by Villeroy »

jm-trigger wrote:And just so I'm sure I'm not getting anything wrong here: This installer would have to be run on the computer that acts as the server only?
A http server is a program waiting for http requests.
A mail server is a program waiting for mail requests.
A file server is a program waiting for file requests.
A database server is a program waiting for row set requests (from Base for instance)
Even the JET db-engine built into MS Access can act like a server for simultanious access.
You can run OOo in server mode waiting for commands.
... to be continued ...

And yes, when you serve email or web-pages to thousands or millions of clients you should run your server applications on one or more dedicated machine(s) doing nothing but these particular jobs.

On your desktop system you want to restrict access to a running server to some local network or to the very same machine only (usually localhost, with IP-address 127.0.0.1).
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
jm-trigger
Posts: 5
Joined: Sat Nov 13, 2010 11:00 am

Re: Am I making the right decision with Base?

Post by jm-trigger »

Villeroy wrote:
jm-trigger wrote:And just so I'm sure I'm not getting anything wrong here: This installer would have to be run on the computer that acts as the server only?
A http server is a program waiting for http requests.
A mail server is a program waiting for mail requests.
A file server is a program waiting for file requests.
A database server is a program waiting for row set requests (from Base for instance)
Even the JET db-engine built into MS Access can act like a server for simultanious access.
You can run OOo in server mode waiting for commands.
... to be continued ...

And yes, when you serve email or web-pages to thousands or millions of clients you should run your server applications on one or more dedicated machine(s) doing nothing but these particular jobs.

On your desktop system you want to restrict access to a running server to some local network or to the very same machine only (usually localhost, with IP-address 127.0.0.1).
Hm. I'm not sure if I'm misreading your reply or if you were misreading my question. I'm pretty sure I understand what a server does ;) The emphasis was supposed to be on "...on the server ONLY". As in: "Does the installer have to be run on the clients as well or ONLY on the server?". I wasn't sure if the clients would have to be set up to access the multi user database too (and if the installer would do that job) or if everything was to be run on the server exclusively and the clients would be able to access the DB without any additional steps.
OpenOffice 3.2 on Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Am I making the right decision with Base?

Post by Villeroy »

Code: Select all

---------Server-Machine-||-----Client-Machine----
[Data]<->[Server-App]<->||<->[DB-Driver]<->[Client App]
What you need on the client machine is some type of driver which enables the client to send meaningful requests to the respective server.
Regarding Base this can be a generic ODBC driver, JDBC(same thing in Java) or a driver integrated in Base (so called SDBC).
Database engines written in Java come with JDBC drivers.
Running a MySQL-server, the client can access through an ODBC driver, JDBC and meanwhile there is a native SDBC driver installed with Base.

3 ways to connect with MySQL
menu:File>New>Database...
[X] Connect to existing database...
Type: ODBC|JDBC|MySQL
The latter makes use of the native SDBC driver for MySQL, [J|O]DBC may be useful with more than one client application.
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
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Am I making the right decision with Base?

Post by DACM »

Those are good questions but as Villeroy mentioned you'll need connection software known as a "driver" on the clients to access the database server. These comes in many forms (JDBC, ODBC, etc), but if you're choosing HSQLDB the required [JDBC] driver is stored inside the database engine executable (hsqldb.jar). So that makes it easy with HSQLDB because we can simply run the Quick Fix installer (mydb.jar) on each client while de-selecting both "new database" options. Likewise, if you're using HSQLDB 2.0 run hsqldb2_OOo.jar exclusively on each client. In the end, you'll need the same version of HSQLDB installed on the host-server and all clients.

Here's the basic steps: (notice I'm simply stepping through several links in this tutorial)
(1) Use the installers to get up and running:
  • (1a) To begin a new database project, run the Quick Fix (mydb.jar) -OR- run the associated HSQLDB 2.0 installers (hsqldb2_OOo.jar & hsqldb2_mydb.jar).
    (1b) With an existing database project, the Quick Fix (mydb.jar) allows you to migrate an "embedded database" to multi-mode/file. And then if you wish, you can upgrade that existing database to HSQLDB 2.0 using the main HSQLDB 2.0 installer (hsqldb2_OOo.jar).
(2) Open the new database in Base:
  • (2a) Single-user ('file:') mode: Click on the new shortcut (or open mydb.file.odb directly).
    (2b) Multi-user ('server') mode: Click on 'START.vbs' (Windows) which seamlessly starts HSQLDB-server and your new database front-end (mydb.server.odb) in Base.

    NOTE: Once you begin development your selected Base (.odb) file will contain Forms, Queries, Reports and Macros, so that particular (.odb) file becomes your master front-end file. But understand that you can copy&paste the connection setting (Data-source URL) from the other, un-used (.odb) file in about a minute to change connection modes, so don't worry about which mode you use to develop your database application; you can switch back and forth during development or testing as desired. You'll need to use the mydb.server.odb file name for compatibility with the Windows script files when switching to 'server mode.'
From here the steps are similar with any back-end database solution...

(3) Use Base to develop your database application... (4) Get in the habbit of keeping database backups.

(5) Setup the clients computers for basic database-server access:
  • (5a) Install OpenOffice.org on each client and run the applicable installer while de-selecting both "new database" options. Also make sure you select any HSQLDB upgrades used on the server-host to ensure driver compatibility. If you're using HSQLDB 2.0, you can skip the basic installer (mydb.jar) and use hsqldb2_OOo.jar exclusively on each client.
    (5b) Make a copy of your latest Base front-end file (.odb) and modify the connection setting (Base > Edit > Database > Properties... > Data-source URL) to reflect the IP address (or hostname) of the computer running the database engine (containing the database files and running HSQLDB in server mode). Here's where the detail of this post (step 3) comes in. Don't change the user name and don't check "Password required" just yet.
    (5c) Run 'START.vbs' on the host computer and leave everything running.
    (5d) Move the reconfigured front-end file (mydb.server.odb) to a client computer and open it in Base and click on Tables to begin testing.
    Troubleshooting may involve host file-share settings, firewall-port settings, URL syntax, etc.
(6) Optionally, setup database access control; Adding user names & passwords, roles & privileges:
  • (6a) Setup users and passwords as desired using SQL (in Base > Tools > SQL...).
    This could be a tutorial in and of itself, but suffice to say that you can create a user with default access to the entire database using:
    CREATE USER <user name> PASSWORD <password>
    (6b) You can then add that user name and check-off "require password" as necessary in the reconfigured front-end file (.odb) for client distribution (Base > Edit > Database > Properties... > User authentication).
    (6c) You can assign USERS to ROLES with GRANT/REVOKE on a per Table/View or per Column basis.
    ...I'm more familar with H2 when it comes to fine-grain access control so we'll be learning this together as I experiment over the coming days with HSQLDB in this respect.

    Final tweaking will include: removing ";shutdown=true" from the batch file (server.start.bat) on the host-server machine only; switching to an encrypted protocol (hsqls); perhaps encrypted database (HSQLDB 2.0 only); switching to MVCC as user concurrency grows (HSQLDB 2.0 only), etc.
Last edited by DACM on Wed Nov 17, 2010 5:23 pm, edited 4 times in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Am I making the right decision with Base?

Post by DACM »

jm-trigger wrote:...would you recommend going with HSQLDB 2.0 straight away?
Yes...consider that in order to take full-advantage of some newer features you'd be facing manual re-work upon upgrade. For instance, binary and large-text (BLOB/CLOB) support is introduced with HSQLDB 2.0, but with HSQLDB 1.8 a workaround is necessary using two tables and the VARCHAR data-type. If you want to go with HSQLDB 2.0 then just download the associated installers (hsqldb2_OOo.jar and hsqldb2_mydb.jar). But if you really don't need the new features and/or you're concerned that HSQLDB 2.0 is relatively new to the Base community as well (although well tested by outside professionals), then just use the basic installer for standard HSQLDB 1.8 support (mydb.jar; please download the latest).
jm-trigger wrote:This installer would have to be run on the computer that acts as the server only?
You'll need to run it on the clients as well (without installing the two 'new databases') in order to configure Base on the client(s) and provide the [JDBC] driver, as necessary (especially when upgrading to newer HSQLDB versions).
jm-trigger wrote:After that would I still need your older "multi-user server mode tutorial"?
Probably not necessary, but may be helpful for client-specific URL syntax (step 3 notes there) or other general information.
jm-trigger wrote:Or does the installer set up the respective computer as the server already?
Yes the installer does all the necessary setup of the server-host computer. You'll need OOo installed on this computer prior to running the installer(s).
jm-trigger wrote:And the clients would only have to install vanilla Base for use as the frontend and then connect to the DB on the server?
The clients will need the installer configuration and any HSQLDB upgrades you selected on the host machine. Just skip the two "new database" options on the clients. You'll be clicking directly on the reconfigured copy of your master Base file (mydb.server.odb) on the clients for seamless database access across the network.
jm-trigger wrote:Because when you say "Then setup each client/user/group just as you would with any SQL back-end.", I'm not entirely sure what that entails.

Hence the wordy reply above. ;)
jm-trigger wrote:Would this also allow me to implement password protection/user log-in options as well?
Yup, and much more with respect to fine-grain access control down to the column level as desired.
jm-trigger wrote:Also, would it be smart to switch to the other forum for this? Am I asking too many questions? :P
I don't know...it's hard to hijack your own thread. 8-)
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
jm-trigger
Posts: 5
Joined: Sat Nov 13, 2010 11:00 am

Re: Am I making the right decision with Base?

Post by jm-trigger »

Thank you, thank you, thank you! I cannot thank you enough for taking the time to spell everything out once again for me. :D

Before I start asking more questions - and I inevitably will - I will try to test this and set everything up on the server and on one client first (will probably be a few days until I actually get around to working on this again though). I want to be sure that I can get this to work, BEFORE I put in endless hours developing the db itself, creating the forms etc.

That said, there's still 2 tiny TINY questions I'd like to ask before I go ahead with this... :P

1) You recommend using a separate tool like SQuirrel to develop the tables. If I did that, the only use for Base at that point would be forms and reports, creating them and accessing them. This begs the question: Why use Base at all? We're not using its embedded database system, we're not using it to create the database, we're not using it for the tables. So we're left with forms and reports. I must assume that Base is simply the best free frontend and form creation tool out there, that there's no comparable alternatives, even when stripped of some of its most touted features. Or am i missing something?

2) This is somewhat off-topic and I understand if you can't or simply don't want to answer a question like that: We're currently using a small all-in-one (linux based) NAS Server (we only use it as a file/backup/printer server so it's good enough), the QNAP TS-109 http://www.qnap.com/pro_detail_feature.asp?p_id=78. It apparently has built in support for MySQL and SQLite, not that I'm entirely sure what that entails. I was just wondering if you had any idea whether it would work as a database server for a HSQLDB...? Would be neat. Otherwise I'd probably get a dedicated Windows Server machine at some point.

Thanks!
OpenOffice 3.2 on Windows XP
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Am I making the right decision with Base?

Post by DACM »

jm-trigger wrote:1) You recommend using a separate tool like SQuirrel to develop the tables. If I did that, the only use for Base at that point would be forms and reports, creating them and accessing them. This begs the question: Why use Base at all? We're not using its embedded database system, we're not using it to create the database, we're not using it for the tables. So we're left with forms and reports. I must assume that Base is simply the best free frontend and form creation tool out there, that there's no comparable alternatives, even when stripped of some of its most touted features. Or am i missing something?
You forgot "we're not using it for the joins/relationships" which is perhaps the only reason to consider alternatives now that we've stabilized the Base+HSQLDB configuration. And some folks may not prefer/appreciate the little workaround with Base. Actually I'm not qualified to answer your question because I use Base almost exclusively for these tasks including joins with the GUI (in Tools > Relationships...). I'll let Villeroy defend the acute reasons (problematic joins?).

Yes the front-end componets alone make Base quite useful, but Base is also critical to database access from Calc, Writer and Impress.
jm-trigger wrote:2) This is somewhat off-topic...(linux based) NAS Server...It apparently has built in support for MySQL and SQLite, not that I'm entirely sure what that entails. I was just wondering if you had any idea whether it would work as a database server for a HSQLDB...? Would be neat. Otherwise I'd probably get a dedicated Windows Server machine at some point.
An NAS Server that supports MySQL should support HSQLDB through a Java server applet like Tomcat, GlassFish or Jetty. But you could certainly use MySQL as the back-end -- but we've run into various incompatibilities with Base forms here in the forums. SQLite is not designed for multi-user access, and it's crippled compared to HSQLDB 2.0 or H2.

As you shop for a "dedicated server" consider that a modern, general-purpose operating system (OS) can be as stable as most server OS's. And today's hardware is much more powerful than servers just a few years old. In other words, a modern laptop with battery, solid-state storage with Trim support including the OS, dual-cores, 2GB+ RAM, and Gbit LAN ($700 new or $400 used) would support a few hundred users on par with most, individual, database servers in production LAN-environments today.
Last edited by DACM on Mon Feb 20, 2012 3:04 am, edited 2 times in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Am I making the right decision with Base?

Post by Villeroy »

Before Base (OOo version 1.x) there was the database window where you could register data sources without any "Base document", writing the whole configuration into the user profile (where it belongs to IMHO). Forms and reports where file links to stand-alone Calc and Writer documents (much better than today's embedded Writer IMHO). The query designer used to be the same with even more bugs and limitations.
You registered your data source once and used it for ordinary office tasks like bibliographies, label printing, serial letters and to feed data into Calc models. There was no way to create a database table except dBase export from Calc.
The use cases where Base is really powerful and easy enough remains the same more or less.
5 years have been wasted for this caricature of a single-file database being so unsafe, insecure and slow.
When you create HSQLDB tables the graphical tool keeps away two fundamental HSQL features at least: dynamic defaults (DEFAULT CURRENT_TIMESTAMP) and constraints (ADD CONSTRAINT CHECK "Start"<"End", CHECK "Amount">0).
Today the same forms and subforms work as they were supposed to work 10 years ago but every single version since 2.4 comes with another surprise. There are far too many regressions (things that stop working in newer versions).
The query designer manages the most primitive selects where many users would not need any graphical designer at all (yesterday's bug: http://www.openoffice.org/issues/show_bug.cgi?id=115579 )
The table designer does not help by any means to create a set of interrelated tables.
The form designer manages a tiny subset of possible relations. It does not help to make all the table relations editable. Just create a many-to-many form counting the steps you have to perform without the help of a wizard. The wizard can not even add a list box to make a foreign key editable.
I spent so many hours with the ever changing report builder add-on that I removed it completely. I use Calc as report engine with calculations, pivot tables, charts and some page layout good enough.
Macro coding for Base frontends is a nightmare. Using a solid API you can be sure that it's your fault when things go wrong. In Base you stumble across unfinished and faulty implementations every time you try something exciting.

Learn SQL and do your thing with some appropriate and mature database keeping Base in mind when you need the stored data in office documents. Everything in OOo is about ODF documents. No email, no collaboration, calendars, web-content, no development platform for database solutions, just ODF documents and many channels to get data in and out.
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
jm-trigger
Posts: 5
Joined: Sat Nov 13, 2010 11:00 am

Re: Am I making the right decision with Base?

Post by jm-trigger »

Villeroy wrote:The wizard can not even add a list box to make a foreign key editable.
Haha, yes. I'd already noticed that limitation. But using the wizard seemed silly anyway.

Anyway, thanks for the additional insight, DACM and Villeroy. Right now, since we're not even using any of the OOo applications at work, I'm seriously re-considering the use of Base. I don't see much of a benefit.

At the moment we're using Excel basically as a combination of UI (data entry) and report tool. Not quite unlike in Base, you could say that the database is embedded. I use separate hidden sheets for the database tables and then reference, sort and filter those to get the equivalent of queries and reports. But there's lots of limitations in Excel. You can't get the equivalent of a relational database just with spreadsheets. And it can get very slow if you have a lot of data and a lot of cross-references and data being handed over, calculated and interpreted across multiple sheets. So I'd have to get creative. I'd use external .txt files and something called TextStream to write and read large chunks of data. It's much much faster than doing everything inside an Excel spreadsheet but still not close to the performance and speed of a proper SQL DB, plus it's really rather cumbersome.

Maybe the best way to go about this would be to use something like Postgres and the many free or cheap available db development tools for it to create the tables and then use the existing spreadsheets/UI we already have in Excel as templates and dump data from SQL queries (which VBA should be capable of) into those. And with a SQL DB running in the background, with proper relationships in place and SQL Queries none of the TextStream nonsense would be necessary anymore and it should speed things up considerably. Of course I have no idea if and how data entry would work like this and if Excel and VBA even support non-Access SQL databases and I have no idea how to set up any of this - haha. But at this point it seems like the safer and saner route. I'd "just" have to teach myself SQL... and figure out how to use Postgres and pgAdmin etc... and then somehow get it all to connect... oh who am I kidding! It's a headache no matter what. But would this not, after all the workarounds and limitations of Base and HSQL, be the better approach still (if it's possible and feasible at all and I'm not just making stuff up)?
OpenOffice 3.2 on Windows XP
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Am I making the right decision with Base?

Post by rudolfo »

If you are looking for a single file SQL database that is somehow comparable to HSQLDB or the MS Access JET database concept look for SQLite and use the ODBC connector. As the connection is via ODBC VBA and Excel should know how to work with it.
Well, ODBC is "only" a Standard, but not an advantage per se. If you rely on ADO objects in your sheets or macro code that's not yet supported for SQLite. But as you mentioned .csv and .txt files and TextStream I would say that is something that can surely covered by ODBC and SQLite.

If you need a more advanced permission concept ... I guess PostgreSQL is the way to go.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Am I making the right decision with Base?

Post by Villeroy »

jm-trigger wrote:
Villeroy wrote: At the moment we're using Excel basically as a combination of UI (data entry) and report tool. Not quite unlike in Base, you could say that the database is embedded. I use separate hidden sheets for the database tables and then reference, sort and filter those to get the equivalent of queries and reports. But there's lots of limitations in Excel. You can't get the equivalent of a relational database just with spreadsheets. And it can get very slow if you have a lot of data and a lot of cross-references and data being handed over, calculated and interpreted across multiple sheets. So I'd have to get creative. I'd use external .txt files and something called TextStream to write and read large chunks of data. It's much much faster than doing everything inside an Excel spreadsheet but still not close to the performance and speed of a proper SQL DB, plus it's really rather cumbersome.
I'd prefer the worst database frontend (which may be Base, don't know) over the best spreadsheet (which certainly is Excel) as soon as relations come into play.

Success story:
In 2008 I copied several thousands of records from various sources (dBase, xls, PDF via clipboard and Calc) into one embedded HSQLDB where I set up relations with input forms and reports across a dozend of tables. Thousands of persons mapped to hundreds of services and a dozend of insurances were involved. I was the one who used that stuff every evening, so it could be ugly and a little bit clumsy to use without a single line of macro code.
I spent a few dozends of hours for the creation of the database and about 500 hours using it without any complications but daily backups as a matter of course. While filling a spreadsheet with data you constantly copy formulas, adjust references, check for errors, wrong, inadequate or missing data.
A database will not accept any incomplete, inadequate nor inconsistant records. All you've got to do is to import/hack the raw table data correctly into an adequate skeleton of fields and relations and then edit the relations between the unique table items through forms. A session has one date/time, one existing and unique client, each client has zero or one particular type of insurance and zero or more sessions, each session has several services with a price and some refund depending on the clients insurance.
Finally, the Base report builder built a 18 month busines report and my wife got the credit. Now she runs some professional software system with full support and maintanance (on top of a fishy database but that's not my problem anymore).
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
Post Reply