Front-end Back-end both odb databases?

[rang]

Trying to move from msaccess to oobase.

Under masacces, I had all data (tables) in 3 separate databases (back-end), and a single database (front-end) with queries forms and reports.
Under msacess, I could link the front-end mdb to the tables in the back-end mdb(s)

I've got my data (backend) odb databases set-up, finally, but can't figure out how to set-up the front-end odb database with linking to the data (back-end) odb databases.

Can anyone help?

Explanatory Sidenotes:
It's not a matter of server-side multi-user.
My design admins companies.
Each company copies the (virgin data) base design to a companyname folder with front-end db and subfolder data db(s), and can exploit the system, on their pc. Depending on choices made in the front-end db, only one back-end db is used for basic admin, or a further one for accounting, or a 3rd one for project management.
Backup systems work on back-end dbs only.

[dacm]

[rang]

Many thanks dacm for your reply.
I read the tutorials - most enlightening.

The reason to move out of msaccess is the free availability of my system without the burden of a licence per pc for my small busineess friends.
Although msaccess may be better for the developer, for a finished design the only requirement is to get it working.

To resolve the frontend-backend issue, it seems for me the way to go is convert the tables to mysql.
Hopefully the conversion from msaccess to OObase of queries forms tables and many macros will not be too heavy -- including dde conection with writer (no idea if that's going to work, from my msaccess forms one could automatically launch a preadressed prefilled word or email document eg a sales confirmation).
Since all the presentation design and logic is already there, I can fall back on writing code in perl/php if this would be easier for me then conversion to OObase.

thanks for your guidance.

[dacm]

Are these business computers Windows machines?

[rang]

Good thoughts..........

I've never used the msaccess runtime system but I'm aware of the concept, you are right, that looks like a good approach and then use some way to connect to OOwriter.
I never needed to consider the free runtime method, the pc's with windows all had msoffice pro, but now I have new requests from users who don't have msoffice pro, and now also a mac user, which is why I'm reviewing how to do this.
I'm tempted to look for an open source way so that my users can tailor adjust-extend and not depend on me. With runtime, flexibility would be lost, and users' dependance on me would become primal (I guess it always was but I'd like to get away from that..)

I need to think more before committing to such a major change.
Thanks

[dacm]

[rang]

I do see your point.

My system was developed for my own business needs using msa2000, later several other business friends wanted to use it too, so it's not a commercial thing. But now with new users asking to use my system, new computers with windows7 (needs msa2003 or above so msoffice upgrades needed), a mac user, tendancy to have more computers per person/household/business, and me wanting to get users to help themselves more for tailoring, is why I thought of trying for an opensource environment (at least the argument "can't afford msofficepro on 2 or more pcs" wouldn't go down).
It begins to look like its just not realistically feasable.

Some new users (eg my son, the new mac user, starting his own business in another city) could learn enough to be able to make a query to print out some combination of data that was not foreseen. Fiddling an existing system (with all those technique examples) is much easier than a start from scratch.

By the way, in MSA I was using a macro function "AutoReattachTables" to link the backend db tables to the frontend db, anytime a new user setup with a copy of the (virgin) data design from the installation folder.
I don't suppose that will work either since you have said one cannot link 2 odb files, although if linking to other types of external dbs works I don't really understand why.
(I can post the code if that gives any insight)

thanks for yr guidance

[dacm]

Well once again, you can't link a front-end ODB to a back-end ODB in Base. And once again, you would never want to use a back-end ODB file in a production environment because it's about 10,000-times more susceptible to data loss through file corruption than an MSA MDB/ACCDB back-end.

Base should be thought of as a front-end ODB only with the ability to embed stored Queries, Forms, Reports and Macros...but no embedded Tables or Data. The Tables and Data should always be stored in a separate back-end database whether it be a Text file (CSV), dBase folder, mail-merge file (LDAP), MDB (Windows only), or SQL database engine. Of course you'll want an SQL database engine serving as the back-end with Base for a project like yours. And you can ultimately add polish to the design by using a standalone Writer Form as a 'switchboard' to open other standalone Forms or with macros you can open ODB-embedded Forms and Reports. The ODB-embedded Queries are accessible without macros in all cases.

Now keep in mind that several SQL database engines can be run seamlessly with Base (HSQLDB, H2, Apache Derby or SQLite) meaning they can be setup to run automatically when the ODB is accessed. HSQLDB, H2, and Derby require Java (JVM) but they provide significantly more function than SQLite including multi-user support. Besides, HSQLDB is bundled with Base so you have everything you need to get started with a standard Base installation. See my signature links for details setting-up Base+HSQLDB or Base+H2.

So that said, you can create a "virgin" database folder and copy that folder to the "working" database folder for new users, using a macro if you prefer. It will work instantly if the ODB front-end was already setup to access a database of the same name in "working" database folder. No problem there.

And I guess I should mention that standalone Writer/Calc Forms allow connection to any number of ODB files, so you can effectively manage data from multiple data sources, even from a single, non-embedded Form. The only limitation relative to MS Access here is that Queries are limited to a single data source per query. However, HSQLDB 2.x and others support multiple, internal databases using "Schema" (internal database folders), so I presume a single Base Query could access multiple databases across Schema.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Migrating 'Embedded databases'

[rang]

That was enlightening, I'm getting a clear focus now.

I well understand how I should view as front-end only ODB.

I also see clearly that my backend db's need to be in a SQL db.
I also see that I can keep my "attachtables" method even using macros, with indeed same name but different "working" folder. Also, I note the potential of Writer and Calc assessing odb queries, cool (presently it's the other way around, everything gets initiated from swithboards/forms on my frontend msa db).

So that leaves me with the task of converting my msaccess frontend to odb, and reviewing which SQL database to use to convert the msaccess backend tables. I'll have to study the alternatives since I have no experience with SQL databases other than Mysql.

Thanks very much for your help.