You're absolutely right that it works well for small groups of people (5 concurrent, 15 or 20 or so total), and for small to medium amounts of data.
The biggest bit of advice I can offer to non-DB professionals building a db in Access is the same one that many professionals forget about: DOCUMENT EVERYTHING.
If you've documented everything correctly, you know what the relationships are, what all the properties are, what exceptions are allowed, and as importantly, WHY they were built that way. Not only does it make maintaining and modifying the database easier (and being able to write reports out of it); but is also makes a good base for those situations where you have to upgrade to a bigger database engine - you still may have to design from scratch, but you've got almost a complete analysis to work from then.
Keep Up with TechRepublic