Slashdot Mirror

← Back to Stories (view on slashdot.org)

A Database for the Office?

Posted by ryuzaki0 on from the something-to-start-small-and-build-on dept.

travellerjohn asks: "I work in a small company (200 people in 7 offices), where the staff uses Microsoft Access to create various databases. Most of the time they lose interest before the databases become complex or important enough to warrant the IT department getting involved. However, from time to time, someone turns up at our door looking for help with their pet project, often starting with statements like 'it should work over the intranet' or questions like 'why can't it store documents and pictures?' or 'how do I control user access?' When we sit them down and explain how much it will cost to rewrite their database in PHP/VB/JSP, or whatever we sound unhelpful and expensive. What database tool does Slashdot recommend I provide our staff? It has got to be easy to use, web enabled, capable of storing documents and pictures and offer user level security. We have tried Sharepoint with some success but that is pretty limited, too, and I have looked at Oracle Application Express. Open source would be good, but I would pay for the right product. Any suggestions?"

12 of 156 comments (clear)

  1. Take another look at Sharepoint by ednopantz · · Score: 5, Informative

    A relational db is one thing, a document collaboration tool is another. If it is a MS Office environment, get someone who knows Sharepoint to come out and show you and one of your power users what it can do. You can even buy/build modular web parts if your document needs are out of the ordinary.

    You'll need MSSQL on the backend, so that solves your "bigger than Access" problem right there. These tools dominate their markets for a reason.

  2. Claris FileMaker by sakusha · · Score: 4, Informative

    FileMaker seems to be the easiest for non-techies to grasp, and supports image storage, publishing to web servers, and other goodies they want. Also hooks to SQL if you need more horsepower on the backend.

  3. FileMaker by doj8 · · Score: 4, Informative

    I've used FileMaker quite successfully for many years. It is simple enough for most folk, but extensible. It can store pictures and other binary data. The web interface can be customized. User level access control is built-in. It runs under Windows and Mac and in Wine under Linux. Databases can be migrated to a FileMaker Server, if they go beyond the standalone limits (10 simultaneous users, typically). There's also a compiler to create standalone applications from databases, without needing a license per user.

    All in all, FileMaker is a great tool for this sort of thing.

    --
    -- Dan Jenkins, Rastech Inc.
  4. Rethink the Process by moehoward · · Score: 5, Insightful


    In a 200 person company, I would get rid of Access on the desktop. I see the appeal, but it's time for the IT department to step up and consolidate database development/maintenance so that it is more centralized.

    Once IT takes control of all databases, all sorts of things fall into place, such as security, backups, moving to a single technology (SQL Server or MySQL), etc. At first it is a bit more costly and people will complain about losing flexibility. But in the lgng run, it is cheaper and people who do OTHER work will find it nicer to be able to focus on their core expertise.

    --
    "If you want to improve, be content to be thought foolish and stupid." - Epictetus
    1. Re:Rethink the Process by Tablizer · · Score: 4, Insightful

      but it's time for the IT department to step up and consolidate database development/maintenance so that it is more centralized.

      In theory this sounds great, but the problem is that the centralized place becomes a bottleneck, sort of like "free"-ways. It gets overworked and delivery times start to slip. If a smaller department has control, then they can decide how much resources and effort to put into things on a case-by-case basis and ramp up quickly if needed. Yes, it results in more duplication, but sometimes that is preferrable to lack of service response.

      --
      Table-ized A.I.
    2. Re:Rethink the Process by DuctTape · · Score: 5, Insightful
      Once IT takes control of all databases, all sorts of things fall into place

      Where I've been, once IT takes control of the databases, you never see them (or your data) again.

      DT

      --
      Is this thing on? Hello?
  5. TWiki or some other internal wiki? by allenw · · Score: 4, Informative

    Why not use something like TWiki? It can store those things plus it has decent enough access control. We've moved almost our entire business unit (around 600 users) web content and migrated a lot of processes to one centralized TWiki installation running on a Solaris box and couldn't be happier.

  6. Solve next years problem as well as todays... by riprjak · · Score: 4, Interesting

    ... BAN Access. One day the database "created just for a simple task" may become the repository of mission critical business data. Access is inappropriate and incompetent to the task of being a "database" in any meaningful sense of the word.

    Training is critical; ensure staff recieve spreadsheet (excel or your chosen open source brew) training in reasonable depth... Then encourage them to use spreadsheets for "simple tasks" involving data storeage. Making some "standard" macros for query dialogs is useful here. Then if the data does become important, it is a trivial task to move it into a real database (unlike access!).

    One solution I have seen effectively used is the creation of a "general" database using mysql and a rather clever PHP front end. The database allowed for 8 "fields"; each field was really three fields, Data descriptor, Data name and Data type. Essentially the ID-10-T entered a name for the data field, its data and selected a type from a drop down box. They could select previous "name and type" combinations they had used. This then spawns a copy of this "standard" database with user access privelges set to a default rule; another interface allowed advanced users to adjust this. Finally a generic PHP gateway presented them a data entry/query sheet that formatted itself based on type... Sure, it was probably alot of work, once; but it ensured that all future databases created were in "real" databases that were relatively easy to maintain for the IT department.

    Essentially, my suggestion is to encourage them to work with excel or similar with a few standard macros/dialogs created to allow data entry and search to be "simple" (small up front work by IT, maintenance required); or create a more complex "standardised" database and access system (alot of up front effort, minimal maintenance). This trades effort for ease of future scaleability and maintenance.

    Just my $0.02
    err!
    jak

    1. Re:Solve next years problem as well as todays... by ednopantz · · Score: 5, Insightful

      Holy crap. Moving them from Access to Excel is a good thing? Are you nuts? If you don't like rogue Access apps, the solution is to offer a better solution, not ban the technology that comes closest to solving their problems.

      Assuming there aren't internal resources, get some broad guidelines for those rogue people and better yet, cultivate a stable of smart outsiders who can be the "approved" rogue IT for when business people bypass IT and do their own thing using Access. If you are going to have rogues, have good rogues.

  7. Access or SQL 2005 Lite by Planesdragon · · Score: 4, Informative

    For 200 users, with user-level security, you just need to find a tech willing to actually spend the time to make Access work. 2007 has plenty of additional gizmos, incluing a new "attachment" data type to, well, store those documetns you can't really store in Access.

    (You can store Images in Access. You use the "image" file type.)

    Now, if you just want to upgrade their database, the SINGLE CHEAPEST thing you can do is setup SQL Server 2005 Express. Access can upgrade itself to use the server (Use the "SQL Database Engine" if you're version-shy), and you gain all of those things that you don't have now.

  8. Re:Axis by vandan · · Score: 4, Interesting

    Sorry. I suppose I could have elaborated a little further.

    Axis is a collection of 3 projects:

    - Gtk2::Ex::DBI ( forms )
    - Gtk2::Ex::Datasheet ( datasheets )
    - PDF::ReportWriter ( reports )

    They're all cross-platform ( heavyily tested under Linux and Windows 2000 ) and open-source.

    The basic idea is that you create your GUI in Glade ( ie Gtk2 ). You then create a Gtk2::Ex::DBI object, pass it your Glade XML file, and it will connect to the table you specify, and 'bind' all the widgets in your Glade XML file with a name that matches a fieldname in the table.

    The datasheet module is similar, but instead of creating a GUI and laying out widgets and such, everything goes into a treeview ( datasheet ).

    PDF::ReportWriter makes high-quality reports from XML report definitions. It supports unlimited grouping, group functions such as sum, count, etc, intelligent page breaking, page headers & footers, and a WHOLE lot more.

    There are plentiful screenshots on the website. All modules are under active development ( ie right now ). All feature requests, bug reports and patches welcome. Check it out :)

    http://entropy.homelinux.org/axis_not_evil

  9. Re:Mod parent down! by johnashby · · Score: 4, Informative
    I have designed databases in Access that support over 250 users concurrently, and there are no issues of latency or corruption. These databases are not simple "advance to the next record" databases either: they are comprehensive, feature rich applications that tie in seamlessly with the Office applications my users expect to be able to use. I even coded my own security system that is "good enough" in our intranet environment to keep any nosy users out.

    What can Access do easily and well? How about slapping together a presentation in Powerpoint and e-mailing it directly to users? Dumping database content directly into PivotTables for executive analysis...and providing a form to allow them to build their own custom data views. Using Excel objects to chart directly in the database...and provide the ability to get that data out for more detailed analysis. All with no servers, no full-time team of empire-builders who insist everything has to be done in an overly complex way to justify their own jobs.

    The snobby dismissal of Access is generally the result of seeing bad implementations of it. There are places where Access is a horrible choice, and there are "developers" who will mangle anything they touch, including Access. But I will tell you this: nothing can touch Access for speed of deployment for its scope. Paying through the nose for a PHP/Java/MySQL/whatever solution that the users have NO chance of being able to tweak by themselves is only a good deal for the developers, who can hold the users hostage when they need changes. I would say that for most small-to-mid-sized organizations(up to around 250 users per database), Access databases can fulfill many of their ::internal:: needs. The Internet? That's a different question entirely...run away screaming from Access for that.