Virtual machine vs. partitioning

Topic

New Reply

Hi,

I’ve created several “Band-Aid” solutions in Excel for a friend of mine, who owns a dance studio. She had an old MAC (with an old version of Excel that did not have VBA).

She just bought a new MAC (within the last week).

And now we’re wondering how to do the right thing.

For one thing, I’ve convinced her that a data base solution would be the better solution than continuing the patchwork solution of spreadsheets.

First Question: What are some good DB programs running under MAC and what are their costs?

Second Question: since I’ve only done DB work in Access (and Office for the MAC doesn’t seem to include Access), how different are these than Access?

She’s not dismissed the idea of running Office for Windows on her computer.

Third Question: What are some good MAC virtual-machine programs?

Fourth Question: What are some good MAC partitioning programs?

Fifth Question: Advantages/Disadvantages of running a Windows virtual-machine versus a Windows partition.

And lest I leave out any possible solutions, although this is not an OS question, anyone know any good dance studio management software (either server-based solution or off-the-shelf software to run on her computer)? We know some but just wanted to check if we missed any.

TIA

Fred

Reply | Quote

Replies

You could use the free LibreOffice for Mac and write the DB in Base. https://www.libreoffice.org/download/libreoffice-fresh/?type=src
You should have no trouble opening old Office files, or new ones for that matter.

cheers, Paul

Reply | Quote

Hi Paul,

Thanks for the response.

I’ve heard of LibreOffice but don’t use it.

So 2 questions on it:
– if she had LibreOffice, would she be able to run Excel spreadsheets that I create under Windows on my PC in Excel 2003 or 2010? Her old machine had Office for MAC 2008 (no VBA, at least in Excel) but I think she got (or bought but not yet downloaded) Office for Mac 2015
– would she be able to run an Access DB, including VBA, that I create on my PC, again with 2003 or 2010?

Fred

Reply | Quote

Hi Paul,

I just took a look at the Libre Office web site, particularly the part that compares Libre Office to MS Office.

A possible show-stopper is the fact that LO does not support “Imported table from spreadsheets – keep fields format”. I’m not sure about the fields format part. Her existing set up uses a “master” Excel spreadsheet with probably about 40 fields/columns.

So does the item I copied from the LO web site mean that she can import a table from Excel but just lose the column formatting? That’s not a big deal. Or can she not import the table/spreadsheet at all?

What if I were to import the spreadsheet in Access? Would that allow me to take the Access version and have it run on her PC under Base?

Fred

Reply | Quote

I use Excel sheets in LO without issue, but you’d want to test, obviously.

You can’t run VBA in LO directly.

cheers, Paul

Reply | Quote

Fred, I’ve just opened Calc (Libre Office’s spreadsheet) and then opened one of my spreadsheets and it kept all the formatting, albeit the formatting is fairly simple

However, Base isn’t a database like Access, which is self-contained. As I understand it, it uses Calc sheets as the tables. It certainly won’t open Access databases, like Write opens Word docs and Calc opens Excel spreadsheets.

On the other hand, why not just download LO and play with it. You will then be able to try it for your specific situation rather than trying to work out what it can and can’t do. It’s possible that by using it, you could change how you do what you do with Excel and/or Access.

Eliminate spare time: start programming PowerShell

Reply | Quote

This is a bit “out there” in terms of my experience. However I do know some of the issues well enough to comment.

What are some good MAC virtual-machine programs?

The strongest and most popular VM software on the Mac seems to be Parallels.

What are some good DB programs running under MAC and what are their costs?

I believe FileMaker Pro runs on the Mac and I’ve heard good things about it over the years. Appears to be free.

http://filemaker.en.softonic.com/mac

… how different are these than Access?

With most database systems, you cannot easily move applications from one database to another. You didn’t ask about application portability but this is a good measure of how different the databases are. Conceptually they have a lot in common, but as you move away from the database core they get more and more different.

In very rough order of compatibility, they tend to graduate like this (from most to least compatible):

Tables
ODBC
SQL
Queries
Indexes
Reports
DB Management Tools and Utilities (incl. backup systems)
3GL Language Support
Support & Culture

On the other hand, once you know a database reasonably well, and if you have a strong grasp of database fundamentals, it’s usually not too hard to translate a feature or concept from one system to another. With databases the devil is in the details.

Reply | Quote

Hi All,

Thanks for the responses.

From what I’ve seen from the responses:
– 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.

– 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).
— 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.

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

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?)
– “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

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.

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?

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).

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

Fred

Reply | Quote

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

Reply | Quote

The Base database originates with a database technology called HSQLDB, or Hyper SQL Database, which itself originated from a system called HypersonicSQL database. It’s a Java based database that is intended to be small and fast. I don’t know a lot about this system because it’s not one of the majors (Oracle, Microsoft SQL Server, IBM DB2) or even one of the leading personal databases.

https://en.wikipedia.org/wiki/HSQLDB

Further to some of your questions.

The compatibility of forms between databases would be very similar to the compatibility of reports, which is to say not very. Typically you will have to rebuild forms and reports. What you can often do is to reuse the design techniques and concepts, but you will have to manually restate those techniques and concepts into terms the target database can implement.

I’ll give general guidance on the spreadsheet/database schism you are wrestling with. The specifics can go on endlessly. If you intend to use Base, use the concepts (including the design concepts) you’d use in Access or any other database. DO NOT use your friend’s spreadsheet designs and concepts.

The problem is, if you attempt to translate Excel into Base, you are crossing the spreadsheet to database divide. That’s not something that should, generally speaking, be done. You can directly translate Excel to Calc because that’s spreadsheet to spreadsheet. You can directly translate Access to Base because that’s database to database.

But why? Why is it a bad idea to do simple translations of Excel to Base? The short answer is that spreadsheets use a simplified data model. For one thing, spreadsheets combine formatting with data. In a database these things are always separated. For another thing, spreadsheets typically use only basic table lookup systems. In a database everything is a lookup table, based upon knowledge domains. As another example spreadsheets only have very limited concepts of data types, whereas databases require all data to have declared types and that means schema planning.

The longer answer is that databases obey relational theory concepts and follow E.F. Codd’s 12 rules of database design. That sounds short but it implies a lot. Spreadsheets skip most of that. A spreadsheet may superficially resemble a database but it never is.

If you want an analogy to classic (and rebooted) TV, consider the Odd Couple. A spreadsheet is Oscar Madison, and a database is Felix Unger. In a spreadsheet anything goes and rules were made to be broken! In a database, it’s nothing but rules and without rules there is chaos, and chaos leads to madness so the rules must be obeyed!

Therefore you can switch from a spreadsheet to a database. However you wind up adding a lot of rules and structure that the spreadsheet lacks. And when should you do this? Size is the usual determinant. Spreadsheets do not scale well, whereas databases scale easily.

Reply | Quote

Hi BHarder,

Thanks for the reply.

Not to worry about trying to jump the spreadsheet/DB divide. I’ve told her from Day 1 that a DB solution was needed.

Having designed several spreadsheets for my friend to accomplish numerous time-savings chores, I want to get out of the spreadsheet maintenance job for her. Seeing how she’s entered data (eg, trailing spaces at the end of a name forcing me to use Excel’s TRIM function to maintain my sanity when doing matches), I want something that I can do extensive error checking on the data-entry side.

*** I’d appreciate it if you could address the comment from your email 2 back that mentioned that Base is unlike Access, which is self contained. While I think I understand that per my previous post, I’m not sure if I do. That, to me, could be a big deciding factor on which DB direction to go.

My only concern in this area is saving time of having to re-enter data that’s already in her “Master Student File,” which is her only “table.”

The other issue is the MAC-PC issue. She’s had a MAC for years and just bought herself a new MAC about 2-3 weeks ago. So I have to deal with that. Hence the questions about partitioning and VM’s. If she had a PC or MS had included Access in MAC Office, this thread would never have started.

So the question in my mind is not whether to continue the spreadsheet band-aids I’ve developed (that’s a definite NO) but how to bridge the MAC-PC divide in terms of developing a DB solution for her. Partitioning vs VM for an Access solution (which I’ve done for other projects) or a non-Access solution having product on both PC and MAC platforms, like LO or Filemaker – forcing me to address some amount of learning curve but perhaps not being adequate for the job (forgetting any Excel issues entirely).

Thanks.

Fred

Reply | Quote

I’d appreciate it if you could address the comment from your email 2 back that mentioned that Base is unlike Access, which is self contained.

That wasn’t my comment but I’ll take a swipe at it anyway. I’m quite familiar with this issue.

“Self-contained” is an odd issue. I’m not sure that this should be the determining factor in your product choice; it isn’t for most people. Familiarity, cost, design, features and product support all usually feature higher in the prioritization stack.

Years ago it was very common that databases would create files prolifically. The entire dBase/Clipper/FoxPro ecosystem was like that. DB2 is still like that. If you had a “tidy” personality it could be vexing that you didn’t know what every object did, or how to clean up a system, even if you were the sole author and had total control otherwise. Mysterious objects were commonplace and you simply had to accept that.

Databases like Access and SQL Server almost seemed to be a reaction to that. They perform sweeping consolidation and have just one or two physical files to support an entire database, regardless of how big the system is by any measure. Access is the most consolidated of all with just one file to support most databases.

Yet they might have gone too far. And it’s valid to question what is really accomplished by this consolidation. You see object consolidation is a physical layer characteristic. It concerns how the data is arranged physically. Yet in a database the driving issues are invariably logical objects (Should I keep this data as one table or split it into two? Do I need another index? How do I archive data, or do I support data archiving at all? Etc.).

In fact the best practice for an Access database these days, is to split the data and the code. You have your consolidated ACCDB or MDB file, but internally it uses datalinks to an external database, frequently a SQL Server database. Microsoft pushes this method primarily because it buys you a superior database engine. But it also separates your data and code (mostly, I’m ignoring the role of any stored procedures).

Reply | Quote

I was the one who made the comment. I’ve used Access both at work and at home for many years and having got very used to what I could do with it. But after I retired, I only had Access 2003, so I decided to try Base (from both Open and Libre Office). For me, I found it clunky and I couldn’t get on with it at all. I couldn’t find how to do things with it that I could accomplish easily with Access. And as far as I know, Base requires Calc (and maybe other Office apps) whereas Access doesn’t. I also used VBA as well to do a fair bit of processing of data.

But I did advise that Fred downloads Libre Office (or Open Office) and try it. For his needs and and wants it might well be ideal. I was probably being less than precise in using the term self contained. As far as splitting the front and backends, I never had that opportunity (my work uses Oracle and I didn’t have the time or permission to go down that path). And definitely not that path at home!

Hope this clarifies.

Eliminate spare time: start programming PowerShell

Reply | Quote

I’m sure MACs support Virtualization acceleration so there’s little reason to have multiple boot partitions if you ask me. If anything, my XP VM runs too fast. lol

Reply | Quote

Chung – thanks for all the input!

A few thoughts.

•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.

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.

I get the other advantages. That said and from the feedback in this thread, it seems that VM is the way to go if I stick with Access.

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.

While I get that, it’s not clear to me that

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.

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?

If you decide to go with a VM running Windows, consider using Microsoft’s free Access Runtime…There’ll be less risk of accidental changes to the database app and the VM will have a much smaller disk footprint.

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.)

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.

already know C++, Python, Java and/or CLI it might be an easier transition compared to learning LibreOffice Basic.

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.

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

I didn’t quite get the distinction between these 2 approaches.

the spreadsheet tables aren’t directly suitable for importing into a database table (e.g. missing column headers, no primary key column, etc.

Actually, her spreadsheet “Master Student File” does have column headers but no primary key.

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).

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.

Yes, it is DB normalization. I am concerned about the complicated relationships. But as I’ve learned more about her system, we just keep adding more columns to her “Master Student File” to do things right. So there’s a trade-off on bending the rules more and more versus complicated DB relationships.

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.

I don’t think it will change much, once it’s done right! For example, as mentioned, this year we expanded the # of classes a student can take from 12 to 14 and created 16 “class columns” to give yet more room for growth. But that would not be a change in a DB schema (maybe a table of “values” somewhere that put a limit on the max # classes but can be more easily changed). We also added some extra billing info (columns) this year too bcs that was really a nightmare.

I could put in some year key. Her school is not so big that having records of students who’ve left would slow things down much. But a purge of graduated students every now and then wouldn’t hurt (and that’s what she does now: copies last year’s “Master Student File,” renames it for the current year, deletes graduated students, and goes from there).

BTW, her “Master Student File” is the only file and it has headings in ALL the Excel columns. Actually, that’s not entirely true: there are some separate spreadsheets where she lists all the classes (type, start time, duration, etc).

Rather than dealing with user typos, use drop-down lists of valid choices whenever possible.

I do that as much as possible. However, fields like names (I’ve already mentioned trailing spaces), zip codes, addresses, etc. obviously can’t use drop downs. I’ve even considered including an Excel Soundex addin to make sure she doesn’t enter a “new” student who’s already in the DB but spelling a name differently (and I’ve seen that).

Access web solutions for SharePoint

I’ll take a look.

Fred

Reply | Quote

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

Reply | Quote