A Database for the Office? 156
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?"
So wrong it isn't even right? (Score:3, Insightful)
Re:So wrong it isn't even right? (Score:2)
What about a webserver? (Score:4, Informative)
Is it hard to set up an office webserver with some sort of content management that everyone can use?
Re:What about a webserver? (Score:2)
Re:What about a webserver? (Score:2)
http://www.joomla.org/ [joomla.org]
Take another look at Sharepoint (Score:5, Informative)
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.
Claris FileMaker (Score:5, Informative)
Re:Claris FileMaker (Score:4, Informative)
As for SQL support in Filemaker though, I must say that it is pretty poor. As far as I know, Filemaker can only IMPORT from SQL sources. It can't access them live.
-matthew
Re:Claris FileMaker (Score:2)
YMMV
Lotus Notes (Score:3, Informative)
Easy to use with a little bit of training, and works wonders with documents (suppost to be better at it than sharepoint)
Re:Lotus Notes (Score:1)
Re:Lotus Notes (Score:1)
Re:Lotus Notes (Score:2)
This is what Notes was designed for. Everything is a database, and it's just as easy as in access to create nice little front-ends for them. Plus it was designed to be stable and secure in multi-thousand user environments.
Notes got a bad rep, because they put the Notes v4 UI up against Outlook when they tried to compete against exchange. The UI isn't that sucky anymore, and Notes does 10x more stuff than Exchange, so it's not even close to a fair comparison. Unfortunatly, once
Re:Lotus Notes (Score:2)
Re:Lotus Notes (Score:2)
Experiences with it as an e-mail application I would say give poor representation as to how it would function as a database manager. The e-mail functionality in Notes is implemented as a Notes database application, and the shortcomings you describe are not something that would have to be translated into pain
Re:Lotus Notes (Score:2)
Give it a try if you must, but I doubt you'll find a single user who enjoys using the program.
Plus, onc
Re:Lotus Notes (Score:2)
But at least for database-type stuff it's tolerable. Barely.
Could not agree more (Score:2)
It also has simple actions for most business type stuff and it is dead easy to create a new database from a template.
A good IT department can provide the users a selection of templates to base their ideas on. If you let the business users prototype some solutions, you will probably be able to help them take it to the nex
Try a CMS? (Score:1, Interesting)
FileMaker (Score:5, Informative)
All in all, FileMaker is a great tool for this sort of thing.
Yep... (Score:3, Funny)
Groove? (Score:2)
Re:Groove? (Score:2, Interesting)
I admit SP doesn't look like much now, but as you dig and see the Object Model, how it can be integrated into user environments, workspaces, audiences, etc. - it's quite powerful.
No, I'm not blindly pro-MicroSoft and they've still got a long way to go (apparently SP07 slices bread, too) it's just that I can see how MicroSoft is going and it's going to clean Linuxes clock for collaboration.
Coincidentally
Rethink the Process (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.
Re:Rethink the Process (Score:5, Insightful)
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.
Re:Rethink the Process (Score:2)
If IT runs out of human resources whenever they proactively "step up to the plate", they are underfunded and will forever by acting in a reactive role, hence the bad image in the workplace.
Re:Rethink the Process (Score:5, Insightful)
Where I've been, once IT takes control of the databases, you never see them (or your data) again.
DT
I disagree (Score:2)
Perhaps there's some GUI tool based on SQLite?
Re:Rethink the Process (Score:2)
By way of example, I worked in Vodafone's global HQ where a system for billing premium rate SMS was developed and
Re:Rethink the Process (Score:1)
Yep I've seen this too. It's never a good thing to just take something away without giving an alternative.
Beside as soon as people starts involving the IT, it's inportant enough so they'll pay for it. If not, it's not worth the effort. We have made very good experience with applying a price tag to such kind of stuff.
O. Wyss
Re:Rethink the Process (Score:2)
If I had a pound for every time I've seen Excel stuffed with information and being used as a hacky database just waiting to fall over...
TWiki or some other internal wiki? (Score:5, Informative)
Re:TWiki or some other internal wiki? (Score:2)
-Rick
Re:TWiki or some other internal wiki? (Score:3, Informative)
Re:TWiki or some other internal wiki? (Score:1)
I've had big successes at two companies, but you have to make it super easy to get into, and you have to be encouraging, and it does take time.
Re:TWiki or some other internal wiki? (Score:2)
Solve next years problem as well as todays... (Score:4, Interesting)
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
Re:Solve next years problem as well as todays... (Score:5, Insightful)
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.
Re:Solve next years problem as well as todays... (Score:2)
Move them from Access to SQL or something. If you want to allow "rogue" data sets; I much prefer a spreadhseet; creating a macro to allow a pretty front end for data query and management is trivial and the underlying document data is *significantly* less fragile. Recovering data from them years after the fact is easier, converting the data into a manageable dataset within a "real" database is easier etc..
This is, of course, IMHO; but, I can see no meaningful arguement to ever allow MS Acce
Re:Solve next years problem as well as todays... (Score:1)
Don't confuse Access the tool with all the people who use that know nothing about structuring data well. You can normalize data in Access as easily as you can in any RDBMS.
Re:Solve next years problem as well as todays... (Score:2)
That *IS* interesting and now I have another bloody tool to go and learn about! I suppose I should have realised it was possible for the product to evolve an
Re:Solve next years problem as well as todays... (Score:2)
Great idea! Shall we also move them from Word to C++? I hear C++ has a much better spellchecker!
Re:Solve next years problem as well as todays... (Score:3, Interesting)
Re:Solve next years problem as well as todays... (Score:2, Insightful)
Programmers v
Teach them SQL? (Score:2, Insightful)
On a more serious note, you might just consider rolling your own application. Set up a MySQL (or Oracle, or MS SQL, whatever you like) database, then roll your own application that will meet the needs of the various departments. Keep tally of the features that people are asking for on their specific projects, and include the most common ones. Once everything is finished, then just allow departments to port the database ove
Re:Teach them SQL? (Score:2)
-matthew
Re:Teach them SQL? (Score:2)
OSS, need one part (Score:2, Insightful)
Database or development platform? (Score:2)
This seems to be more of a policy/management issue than a technology issue. If people even think for a moment that what they want may one day be used over the net or by more than one or two people, they should be cont
Axis (Score:2)
http://entropy.homelinux.org/axis_not_evil [homelinux.org]
Re:Axis (Score:5, Interesting)
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 [homelinux.org]
Re:Axis (Score:2)
Re:Axis (Score:2)
Re:Axis (Score:2)
Re:Axis (Score:2)
Re:Axis (Score:2)
Using gladexml and perl is a great idea. All it needs is a 'wizard' to install everything required on to a computer, and a 'wizard' to start a project.
Then, the people who would normally use "Excel As A Database" may be able to understand and use it. That may save countless hours and frustration and cost due
Re:Axis (Score:2)
As for using GTK, I find it hard to believe you consider it the worst cross-platform GUI. I find it the best. I have developed a large number of applications under Linux and deployed on Windows. They of course all work flawlessly. Perhaps you are simply not very good at what you're doing? There are 2 ports of GTK for OS-X. I begun development on my Powerbook, and was using the X11-dependant port at the time. It worked quite we
Re:Axis (Score:2)
Re:Axis (Score:2)
What are you talking about? How can you say that X11 on the mac is not an option? If you're after a cross-platform, open-source set of libraries, but then turn around and proclaim that you refuse to use X11, because, why exactly? It's not integrated? Well, fucking EXCUSE ME sir, but I think you're being a bit difficult on yourself, aren't you? I've used X11 on the mac. In a couple
Step one: drop the politics (Score:2)
Your project may be absolutely brilliant, but don't go out of your way to antagonize your potential customers even if your friends also think it's funny.
Access or SQL 2005 Lite (Score:5, Informative)
(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.
Doesn't matter (Score:3, Insightful)
They do the work; you give some input and assistance, but don't turn any of them into full-blown development projects. All you have to do is manage the backend. They get to scratch their itch, you get to look helpful and enabling, and no one gets sucked into big, expensive tools or projects.
SharePoint! (Score:1)
But don't feel bad - our company did the same thing that you're company probably did when it rolled out SharePoint - we didn't assign admins or permissions correctly, train people, or provide design guidance which caused the project to be a failure.
So we bough
Choose Visual FoxPro (Score:1)
Re:Choose Visual FoxPro (Score:2)
My company's spending a lot of time and money migrating away from that hideous trashheap. Gentle readers, I assure you this: the only people who would ever actually recommend
Glom (Score:1)
http://www.glom.org/wiki/index.php?title=Main_Page [glom.org]
From the front page...
Access 2007? (Score:2)
ZOPE is all you need ... (Score:3, Informative)
- runs on almost everything (Windows, Mac OSX, Linux, *BSD - from Servers to Laptops),
- is very easy to set up and maintain, - has an easy-to-understand web-based user interface,
- has a simple but powerful user management
- can store data in almost any SQL database, but
- comes with its own, very powerful object-oriented DB (ZODB).
Especially the last point makes it appear "naturally" to many users: They can store data as they are used to do in their filesystem inside folders, documents etc. There is a LOT of additional, easy-to-use plug-ins (called "products") that allow, for instance, to put files onto the filesystem through-the-web -- and: all is very easily scriptable with Python.
So: Welcome to the Zope/Plone Community
Re:ZOPE is all you need ... (Score:2)
The original poster was considering sharepoint but found it lacking. I would totally agree that plone might be just what the original poster is looking for. It is easy to integrate your own apps into this CMS by writing your own document types (called archetypes [plone.org]) and skinning them using TAL [zope.org]. Aggregate, navigation, or reporting pages can be easily cooked up with a little python script and skinned by TAL.
Sounds Like You Need a CMS (Score:3, Insightful)
Sounds like you need a content management system, not just a database. Your users basically seem to wish for a way to share project-related materials. I see you've already considered that...
...so I'd definitely be interested to hear what limitations you ran into there. It's highly possible that some of the open CMS systems (Drupal, etc) could offer you what Sharepoint doesn't, but it's hard to say without knowing exactly what parts of Sharepoint you found limiting for your needs.
You might also consider a hosted collaboration tool such as Basecamp [basecamphq.com]. I haven't used it myself but it has quite a few fans. It's probably more limited (and certainly less extensible) than software like Drupal but the ease of administration (since it's hosted) and easy accessibility (since it's not on your LAN, it's on the 'net) could compensate. Then again, if you're the IT guy... perhaps you don't want a zero-administation solution for job security's sake. :)
Alphora Dataphor (Score:2)
Take a look at Alphora [alphora.com.] Dataphor. While it is not an end-user tool, it has the potential to lower your development costs so much you will be able to actually serve your users.
Plone (Score:2)
don't waste your time (Score:1)
there are also a few others: http://www.dabbledb.com/ [dabbledb.com] http://www.eunifydb.net/ [eunifydb.net]
http://oakleafblog.blogspot.com/2006/03/dabble-db- new-look-in-web-databases.html [blogspot.com]
Not a "What" but a "Who". (Score:2)
The correct question should have been: How can we empower our users to get the functionality they want without going into the
Access - SQL migration (Score:3, Informative)
It will be almost transparent for users.
shared backend? (Score:2)
Is that the case? If so, would this solve your immediate problem since the users could continue to use Access for smaller projects that don't warrant a full web-based solution? At least the data will be centralized and routinely backed up.
Give them what they really want (Score:2)
to address the original questions (Score:2)
and
Bond (building object network databases) are two products that might interest you.
Alpha V5 is not open source, but it can certainly compete with filemaker in terms of scalability since version 7(it can use, among other things, mysql directly)
However, why doesn't the poster just start over, covering exactly what database/application needs are there, and start from there. Very few business environment develop continuously(except software developers/service providers), and those have people whose job
Access and SQL Server is your best choice (Score:2)
If security is an issue, then make a custom ADP for each user with thier own user ID's.
Re:It's not as bad as you think (Score:1)
Re:It's not as bad as you think (Score:2)
Re:It's not as bad as you think (Score:2)
Mod parent down! (Score:3, Informative)
Access CAN store anything as blobs.
As for supporting the rest, you're wrong there too.
-Access security model is a total JOKE - and a bad one at that. No normal RDBMS security, just some shitty broken built-in sorry excuse for security (and shitty cmd line tool to recover it).
-It *doesn't* scale. At all. This is thoroughly documented (you'd know if you had even read a FAQ or something). It wasn't EVER meant to be used for more than a handful of users (MS KB even state this). Put a fe
Re:Mod parent down! (Score:2, Interesting)
Re:Mod parent down! (Score:2)
Unless you're just serving ads or a message boar
MySQL and other database choices (Score:2)
MySQL is a fine product when used in a manner that fits its original purpose, but it is a poor choice for an enterprise-class database (with 200 employees and multiple sites, the article poster is moving beyond "departmental" and into the realm of "enterprise"). As another poster commented, MySQL's heritage is "ISAM on 'roids". It is easy to set up and use, has a small footprint and is fast at retrieving data. That's what makes it great to set up blogs ore
Re:MySQL and other database choices (Score:2)
I wrote a professional "store my dvds and tv shows" app. SQL Server 2K and IIS, only because I have it on my dev box for work. Database, subdomain, separation of business and display logic, hardened against SQL injection and other common hacks, yadda yadd
Re:Mod parent down! (Score:4, Informative)
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.
Re:Mod parent down! (Score:2)
Cool! Are there a
Re:Mod parent down! (Score:2)
Re:It's not as bad as you think (Score:2)
-Rick
Re:It's not as bad as you think (Score:2, Interesting)
Sort of. You can embed things like word documents, then launch them from the Access app, but Access does some weird stuff with filesizes. Expect to see even simple documents consume an additional half a meg once they're added, and big documents to chew up even more space.
Re:It's not as bad as you think (Score:1)
What you need is something like WebDB or Zope. As a programmer -- I hate it! It's to restrictive. However, as a la
Re:It's not as bad as you think (Score:1)
Re:It's not as bad as you think (Score:4, Interesting)
The biggest issue is that Access has a ~2GB file limitation so storing large things in it is not a good idea. Secondly, JET is well and truly dead and unsupported and doesn't support things you really might need for multi-user system such as referential integrity, triggers, stored procs.
MS provide a SQL Server Express 2005 with a 4Gb DB limit for free that would be a good option. Oracle has something similar. There is also PostgreSQL that has no limits. The last option has the benefit of being totally unencumbered, but I don't believe the ODBC or .NET drivers are quite as polished as the other options.
Re:It's not as bad as you think (Score:2)
ODBC (Score:2, Insightful)
Using the native jet database is bad anyway, as you mentioned.
Re:It's not as bad as you think (Score:2)
Re:It's not as bad as you think (Score:2)
I'm not entirely sure that's what he means, although I can't say for sure, since I don't know him. What I have done with Access DB "security" is to change the file permissions to reflect the AD users who can access it. IIS is set to deny anonymous access and use "Windows Integrated Authentication." As long as the people visiting the site are on domain PCs and are logged in, the authentication is automatic (
Re:servoy (Score:1)