Hi All,

Thanks for the responses.

From what I’ve seen from the responses:

Hi Fred,

There’s already been great input from others so I thought I’d fill in some of the blanks and offer some additional suggestions…

– Questions 3-5, per my original post, on VMs vs Partitioning: thanks for the recommendation of Parallels (never heard of it). But I had spoken to a number of people who recommended a partitioning solution (need a copy of Windows in either case). So would appreciate anyone weighing in on that option.

Many folks recommend partitioning because they often don’t understand virtualization so they naturally go with what they know. There are certain instances where partitioning is a better solution such as when working with bus interfaced hardware that requires direct access, but for general uses like running software, a VM is better both for user-friendliness and user convenience:

[*]With partitioning, a user needs to reboot from his/her preferred operating system into another OS that might not be as familiar. With a VM there’s no need to reboot.
[*]With a VM, it’s easy to copy-n-paste and share files between the host and guest.
[*]A VM can be easily backed up and moved from computer to computer by simply copying the folder containing the VM’s files.

Parallels is an excellent option. VMware Fusion (http://vmware.com/) is also a great choice and competitively priced. But before purchasing either one, give VirtualBox (https://virtualbox.org/) a try. Parallels is for the Mac (there used to be a Linux version). VMware Fusion is compatible with VMware Workstation/Player for Windows and Linux but the GUI isn’t the same and there’s no VMware Player for Mac. VirtualBox uses a similar GUI for Windows, Macs and Linux which will make it convenient for you to build/test a VM on your own Windows computer and copy it to your friend’s Mac whenever it needs to be updated. Having similar GUIs will also make it easier to help your friend over the phone.

If you decide to go with a VM running Windows, consider using Microsoft’s free Access Runtime (https://www.microsoft.com/en-us/download/details.aspx?id=39358) instead of purchasing the full Office for Windows. There’ll be less risk of accidental changes to the database app and the VM will have a much smaller disk footprint.

– I’ve considered downloading LO Windows to my PC, where the development would get done. My concerns with this approach are:
— how similar Base is to Access in terms of development. I don’t want to have to go thru a large learning curve (hence the original questions on VMs or partitioning).

Conceptually, the process is the same: tables, relationships, queries, scripting, forms and reports. LO Base supports SQL and so does MS Access (for the most part). The difference in scripting language(s) is likely the biggest hurdle.

— whether Base has some kind of scripting language like VBA. I saw on the LO site at https://wiki.documentfoundation.org/Feature_Comparison:_LibreOffice_-_Microsoft_Office that LO has a version of Basic but its not clear it’s object oriented to the LO components. For example, in Access, I have written code to check for a valid zip code entered in a form for adding a new person (anything not 5 or 9 digits, etc); this code is automatically invoked when making an entry into the zip_code field.

LO Base supports more scripting languages than MS Access so if you already know C++, Python, Java and/or CLI it might be an easier transition compared to learning LibreOffice Basic.

BHardy:
– I see that Filemaker Pro is available for both Windows and OS X, as is LO, so that’s a plus. I’ve heard of it going back to ancient times of PC history.
– I noticed you didn’t include Forms in your hierarchy; also not sure where macro scripting languages fits into your hierarchy.
– I probably need to go down to at least reports in your hierarchy unless scripting goes down to DB Mgmt tools or 3GL support

So I’d say that I need the following:
– a way to import an existing Excel table to use in the DB. If I lose some formatting along the way, I don’t think that’s a big deal.
– something that works on both Windows and MAC that includes scripting

LO definitely fulfills the two requirements above. FileMaker offers a free trial but might be a bit pricey for your friend’s application. Another database application tool is 4D (http://4d.com/). Here’s how I would categorize the various database options:[INDENT]
Desktop Database: FoxPro, LibreOffice Base, Microsoft Access
Database Application: FileMaker, 4D
Database Server: DB2, Microsoft SQL Server, MySQL, Oracle, PostgreSQL

[/INDENT]
I consider FileMaker and 4D as the halfway point between a desktop database and a database server. Both are more powerful and flexible than MS Access and LO Base, but don’t scale a well as the standalone database servers.

What worries me about LO Base are the 2 entries I see in the comparison chart:
– “Importing data sources (tables) from within the database application” having to be done thru Calc given that the existing “DB” is in Excel (so Excel to Calc to Base?)

Chances are that the spreadsheet tables aren’t directly suitable for importing into a database table (e.g. missing column headers, no primary key column, etc.), so going from Calc to Base is actually less complicated because Calc supports a long list of file formats and import options. Within Calc, the tables can be massaged into proper tables for Base.

– “Imported table from spreadsheets – keep fields format” which is “not supported” but probably not a big deal if I only have to reset the format once

Excel and Calc record both the value and the format in a spreadsheet cell (e.g. a value of “1.00” could be displayed as “$1.00”) while database tables record the value and data type. During import LO Base does try to auto-recognize the data type for a column but it’s limited to numbers and text. In a database application, formatting (aka. presentation) is handled separately from the data.

access-mdb

Base isn’t a database like Access, which is self-contained.

I’ve always thought that might actually be a bad feature of Access, if I understand this “feature.” So my understanding of this is if I have 2 “identical” spreadsheets in terms of structure and I want them each to be an Access table (eg, one table/spreadsheet is “2014 results” and another is “2015 results”) but I’ve defined queries that operate independently of which source provides my data, then with Access I have to either jump thru hoops to switch spreadsheets for the table source (and 2016 is not that far out) or I need to define 2 Access files and switch the table in the copy. In my mind, what I’d want is something like Word’s Mail Merge where I’ve defined a report with “merge fields” (which are independent of source since they all contain the same fields) and I just have to tell Word which Excel file to use this time (or not make any change if it’s the same as last time).

In my case, my friend has a “Master File” for the 2015-2016 school year (in Excel bcs that’s what she knows), had one for 2014-2015, and will have one for 2016-2017. So if having the table source NOT part of the DB is what you’re talking about, I think that’s actually a plus.[/QUOTE]

By default, LO Base stores the entire desktop database in a single file much like MS Access does. Likewise, it’s possible to reference external tables such as ones hosted by a database server (MS Access calls these “attached tables”).

But now that opens a question to my Access-constrained mind: For this application, there would be several tables (in Access-speak) since not all the data is needed all the time. For example, the Master Student file contains basic info about the student: name, address, city. The student takes several classes (right now, 16 cols are devoted to classes in that flat Excel spreadsheet since no one takes more than that but that was just increased from 12). Per Access-speak, I’d have a separate Class table with a record for each class that each student takes. I’d also probably have a table for Class-Definitions (day of week, time, duration, cost, studio, type of dance, etc.). Can Base handle this multiple tables or is there no real need for it?

Yes, LO Base can easily handle multiple tables. Please correct me if I’m misunderstanding, but it sounds like you’re asking about database normalization? If so, then it’s a good idea to try to do that whenever possible. There are times when it’s alright to bend the rules a bit to avoid having overly complicated database relationships.

And if I do have all those tables set up for 2015-2016 somehow, how do I start over for 2016-2017? For example, looking at the Class-Definitions, the Mon 6pm in Studio 1 might be for tap for an hour this year; next year that same slot might not even exist (there’s a 5:30-6:30 jazz class in Studio 1) or the 6-6:45 slot on Monday in Studio 1 might be for ballet. Do I have to create the Class-Definitions table OUTSIDE of Base? While doing so might not be hard, I’d want to create some code to do some sanity checking – for example, a class in not longer than 90 minutes (or whatever max is set) and that the type of class is a valid one (not mistyped).

Without seeing your friend’s current Excel spreadsheets it’s difficult to picture how the various tables relate to each other, but personally, I would avoid managing separate tables for each class year. Instead, use a key to identify the year that a record represents unless the database schema will change a lot from year to year.

Rather than dealing with user typos, use drop-down lists of valid choices whenever possible. The lists can be generated from database tables for easier updating.

I think that’s about it. Appreciate all the suggestions and help.

Fred

Another option to consider if you’d prefer to stick with MS Access is Access web solutions for SharePoint. It might be overkill, but it does offer a way to run a MS Access application in the cloud. Your friend could use any modern web browser and Internet access without having to install any additional software on her Mac:

[INDENT]http://social.technet.microsoft.com/wiki/contents/articles/22231.sharepoint-online-a-no-code-solution-to-create-and-customize-access-service-app-using-access-2013.aspx[/INDENT]

Chung

fburg[INDENT]Chung – thanks for all the input![/INDENT]

You’re welcome. 😎

I understand there’s no need to reboot. But running Windows under a VM or in a different partition seems to still require familiarity with something she’s not familiar with – ie Windows. No? I think ease of use vs rebooting are 2 different things.

Yes, even with a VM she’ll need to get accustomed to the Windows UI, but not necessarily the whole Windows environment. VMware has a feature called “Unity Mode”. and VirtualBox has a “Seamless Mode” (it’s been a while since I used Parallels so I forget what they call theirs). In a nutshell, it’s a way to run an application (e.g. MS Access) without the rest of the Windows desktop. When the application is minimized it turns into an icon on the host desktop. The application behaves as though it’s running outside of the VM.

Not sure I get this. Why am I building/testing a VM on my Windows PC? I have Access so why can’t I work w/o a VM on my machine?

You won’t need the VM during development but you’ll more than likely need to help your friend out with using Windows including patching and other unavoidable maintenance so it’ll be much easier to build, test and then copy a working VM for your friend.

This sounds great but I don’t quite understand it. With my Excel spreadsheets, I end up having to protect the sheets and cells that shouldn’t be touched and also color the cells. Of course, when I have to make a change, I forget that something is protected. So I’d like to understand this a lot better! What does the Access run-time do that prevents my friend from making inadvertent changes? (Of course, I assume, I still have to do lots of error checking.)

The MS Access runtime doesn’t allow changing the design of the database. In a way, it makes an Access database behave more like a self-contained application. There are fewer menu items and other things to deal with, and as an added bonus, the runtime is free to download and use so there’s no need to purchase a license (e.g. Amazon sells Access 2013 for $114 per computer).

I should also mention that the DB has to run on several MACs: she has one at home, one at the studio, and her daughter also can enter info.

Do I hear a server-based solution? We’ve discussed this and she’s really not in favor of it.

By the way, there are server/web-based solutions for dance-studio management but they don’t do some of the things my spreadsheets do, do other things she doesn’t want, and cost too much.

While I was writing up a reply I had done a little searching and found some of those web-based solutions. I figured that the tailor-made spreadsheet was likely a better fit.

Any info on the chances that the DB will need to be used on multiple computers at the same time? If that’s not an issue, using Dropbox or some other similar cloud storage solution might be a viable solution. Use the desktop sync client to keep the database file backed up and mirrored between all of the Macs.

But it wasn’t clear if any of these, including LO Basic, can access a program’s object model. If not, to me, this is a clear advantage to Access with VBA.

Also, I read a comparison of the development community for Open Office vs LO (can’t find the link). It didn’t really seem that either’s scripting language were that good. While LO seems to have active development whereas OO was less active, I didn’t come away with a good feeling that LO’s Basic was very useful.

The key thing to keep in mind is that both OOo and LO support multiple scripting languages in addition to a form of Basic. The Access2Base extension might help with the learning curve and help answer a few questions:
[INDENT]http://extensions.libreoffice.org/extension-center/access2base
http://access2base.com/[/INDENT]

(it’s now bundled with the newer versions of LO.)

Desktop Database: FoxPro, LibreOffice Base, Microsoft Access
Database Application: FileMaker, 4D

I didn’t quite get the distinction between these 2 approaches.[/QUOTE]

Sorry, I couldn’t think of a better label than “Database Application”. Both FileMaker and 4D offer multi-user client/server configurations that support desktop (via a runtime package) and web-based clients. FoxPro, LO Base and MS Access are all-in-one solutions that are suited for desktops. For multiple users, the database files are often placed on network shares (a configuration that Microsoft discourages for both FoxPro and Access). FileMaker and 4D straddle the line between desktop databases and database servers.

In a database application, formatting (aka. presentation) is handled separately from the data.

I assume you’re talking about reports for presentation (or, worse case, queries).[/QUOTE]

Yes, reports and queries but also on forms.

Chung