sql or access?
March 29, 2006 8:21 AM Subscribe
Small database project: Should I use Access or SQL Database?
Its a school competition: the winner will have their real project used by real customer. Customer is small clothing company with around 20 products, and less than 1000 customers. They don't have large expectations, but would like to have a web interface to the db, to add entries and see inventory. Best case it could add accounting data also, and be 'synced' with an accounting program. We have about 10 weeks. I have worked a bit with SQL in visual studio in small projects with others but I have to go it alone now, and am not sure about how to set up the real database all up on a server. I have used access before but its reputation is that its not scalable.
Its a school competition: the winner will have their real project used by real customer. Customer is small clothing company with around 20 products, and less than 1000 customers. They don't have large expectations, but would like to have a web interface to the db, to add entries and see inventory. Best case it could add accounting data also, and be 'synced' with an accounting program. We have about 10 weeks. I have worked a bit with SQL in visual studio in small projects with others but I have to go it alone now, and am not sure about how to set up the real database all up on a server. I have used access before but its reputation is that its not scalable.
My vote would be for MySQL. Lots of very helpful support out there and it's free.
posted by oh pollo! at 8:34 AM on March 29, 2006
posted by oh pollo! at 8:34 AM on March 29, 2006
A license for MS SQL Server may be too expensive for a company with only 100 customers. Like WAY too expensive.
Access doesn't scale well for the web. Since only one person can use it at the same time, you can very easily bring the whole site to a crawl by using Access. But it will work.
MySQL is free, powerful, robust, and easy to use. In addition there is mountains of documentation and example code out there for it.
posted by y6y6y6 at 8:42 AM on March 29, 2006
Access doesn't scale well for the web. Since only one person can use it at the same time, you can very easily bring the whole site to a crawl by using Access. But it will work.
MySQL is free, powerful, robust, and easy to use. In addition there is mountains of documentation and example code out there for it.
posted by y6y6y6 at 8:42 AM on March 29, 2006
There's lots here that isn't stated.
What language will be used for the web interface? (I'd recommend PHP) What will the web server be? (Apache?)
It looks like you're considering Windows, but is this the correct solution for a web-facing, no-administrator machine?
Access can easily handle the load you're talking about, but as databases go, it's a Yugo in a land where free Batmobiles are given away.
Consider a Linux/Apache/MySQL (db)/PHP (language) solution. It will cost you $0 and, unlike Access/IIS, has an active, open developer base that you can utilize for help.
posted by unixrat at 8:45 AM on March 29, 2006
What language will be used for the web interface? (I'd recommend PHP) What will the web server be? (Apache?)
It looks like you're considering Windows, but is this the correct solution for a web-facing, no-administrator machine?
Access can easily handle the load you're talking about, but as databases go, it's a Yugo in a land where free Batmobiles are given away.
Consider a Linux/Apache/MySQL (db)/PHP (language) solution. It will cost you $0 and, unlike Access/IIS, has an active, open developer base that you can utilize for help.
posted by unixrat at 8:45 AM on March 29, 2006
Access would probably work fine, but can be an admin nightmare if you don't have the tools to connect directly to the database on the server (i.e. some software to do this online, or tunnel access).
Start with MySQL. It will scale nicely and you'll get a chance to learn something new.
posted by blueplasticfish at 8:46 AM on March 29, 2006
Start with MySQL. It will scale nicely and you'll get a chance to learn something new.
posted by blueplasticfish at 8:46 AM on March 29, 2006
dont use access. MSDE comes with visual studio. Use that or mysql.
posted by PaddyJames at 8:46 AM on March 29, 2006
posted by PaddyJames at 8:46 AM on March 29, 2006
Response by poster: unixrat: I+customer have no platform or program bias, except i have a little experience with Mysql server+access, and none with others. No language is specified: caveat I love java. About that 'open developer base' any website recommendations? thanks!
posted by uni verse at 8:49 AM on March 29, 2006
posted by uni verse at 8:49 AM on March 29, 2006
Also, be very very afraid when a client tells you any application needs to be "synced" with their accounting or HR system. Clients always say this. And it always ends up being a cluster fuck. It's like going to a car dealer and saying, "And the car should also be able to fly. Is that possible?"
The answer of course is that, yes, it's possible. But it can turn the project into a whole different animal. However...... If you just say that your application will be able to export delimited text files, and the accounting app can suck those in if it has that capability, (or vice versa) you dodge a major bullet.
posted by y6y6y6 at 8:51 AM on March 29, 2006
The answer of course is that, yes, it's possible. But it can turn the project into a whole different animal. However...... If you just say that your application will be able to export delimited text files, and the accounting app can suck those in if it has that capability, (or vice versa) you dodge a major bullet.
posted by y6y6y6 at 8:51 AM on March 29, 2006
OpenOffice.org 2.0 has a quite Access-like component called Base that you could also consider. It allows you to choose a variety of backends - you could start out with a simple flat-file database (which is just an XML document in OOo, unlike the proprietary and version-specific Access things) and migrate later to something more flexible. I believe it's supposed to be able to work with Access database files, too; if you've already got something started, you can probably import it.
OOo is also free. If you've got clients with the cheapo edition of MS Office that doesn't include Access, and you don't want to stuff about with the Access runtime, this could be good.
posted by flabdablet at 8:51 AM on March 29, 2006
OOo is also free. If you've got clients with the cheapo edition of MS Office that doesn't include Access, and you don't want to stuff about with the Access runtime, this could be good.
posted by flabdablet at 8:51 AM on March 29, 2006
Sql Server Express 2005
This version of sql server is free and has nearly all the bells, whisltes, and performace of a pay-for version of Sql Server.
While I'm often not a fan-boy of MS products, Sql server is something that just works.
The development tools will also give you a great edge... they are quite good.
Additionally, Access plays very well with Sql Server and since it is a full blown sql server, it is ideal for the web interface. Web interfaces to access databases are not a good thing.
I've done extensive development with sql server express and I would be happy to answer any questions for you.
posted by killThisKid at 8:52 AM on March 29, 2006
This version of sql server is free and has nearly all the bells, whisltes, and performace of a pay-for version of Sql Server.
While I'm often not a fan-boy of MS products, Sql server is something that just works.
The development tools will also give you a great edge... they are quite good.
Additionally, Access plays very well with Sql Server and since it is a full blown sql server, it is ideal for the web interface. Web interfaces to access databases are not a good thing.
I've done extensive development with sql server express and I would be happy to answer any questions for you.
posted by killThisKid at 8:52 AM on March 29, 2006
Don't use Access. It falls down under any significant level of concurrency. msjetdb is very, very, very, very buggy. I've had relatively simple but high concurrency Access-driven web sites fail so spectacularly that the (quite competent) senior engineers that Microsoft flew out to troubleshoot the problem left scratching their heads and suggesting to never, ever use Access in production.
By and large, I'd also suggest avoiding Windows for production use. It takes a very experienced Windows system administrator to make a Windows server reliable enough for production use. Out of the box, it will give you nothing but frustration.
posted by majick at 9:00 AM on March 29, 2006
By and large, I'd also suggest avoiding Windows for production use. It takes a very experienced Windows system administrator to make a Windows server reliable enough for production use. Out of the box, it will give you nothing but frustration.
posted by majick at 9:00 AM on March 29, 2006
uni verse:
The PHP Manual has a discussion area available below each section for people to review the content above.
IBM's PHP Reading List is a wonderful collection of essays on nearly every topic around.
I've also had great luck on #php on the various freenet IRCs.
Do you belong to your local LUG? There's always a number of developers who can answer questions via that.
posted by unixrat at 9:02 AM on March 29, 2006
The PHP Manual has a discussion area available below each section for people to review the content above.
IBM's PHP Reading List is a wonderful collection of essays on nearly every topic around.
I've also had great luck on #php on the various freenet IRCs.
Do you belong to your local LUG? There's always a number of developers who can answer questions via that.
posted by unixrat at 9:02 AM on March 29, 2006
you say it's a competition, with the goal being use by a real customer. are there any requirements for what code you have to write yourself?
if not, don't reinvent the wheel; use a free, open-source system like osCommerce.
there's still a lot of work involved in getting it set up and running, customized, and visually designed; all that customization work can be claimed as your own. and you can use the extra time for the Accounting integration that they're really hoping for.
otherwise, 10 weeks of part-time work probably isn't going to get this customer a particularly useful website.
posted by el-gregorio at 9:26 AM on March 29, 2006
if not, don't reinvent the wheel; use a free, open-source system like osCommerce.
there's still a lot of work involved in getting it set up and running, customized, and visually designed; all that customization work can be claimed as your own. and you can use the extra time for the Accounting integration that they're really hoping for.
otherwise, 10 weeks of part-time work probably isn't going to get this customer a particularly useful website.
posted by el-gregorio at 9:26 AM on March 29, 2006
what he just said. use whatever db you need for the off-the-shelf app you use.
posted by andrew cooke at 9:29 AM on March 29, 2006
posted by andrew cooke at 9:29 AM on March 29, 2006
Response by poster: el-gregorio: good question, about the project: its vague, but my gut reaction is not building it myself is not going to fly. But if you know about oscommerce, how much work does it NOT save me (so i can justify that I am still building my project)?
posted by uni verse at 9:39 AM on March 29, 2006
posted by uni verse at 9:39 AM on March 29, 2006
Response by poster: Also, killThisKid, what did you mean by saying its easy to integrate with Sql Server Express 2005 with a web-front end? thanks, and all others!
posted by uni verse at 9:42 AM on March 29, 2006
posted by uni verse at 9:42 AM on March 29, 2006
Access doesn't scale well for the web. Since only one person can use it at the same time, you can very easily bring the whole site to a crawl by using Access. But it will work.
I hate Access. Pretty much any alternative is better (SQL Server, MySQL, SQLite -- which is a standalone file like Access), but this statement seems misleading: assuming you haven't done something extremely silly, the only user using the Access file is IIS/IUSR/the aspnet worker process.
posted by yerfatma at 10:05 AM on March 29, 2006
I hate Access. Pretty much any alternative is better (SQL Server, MySQL, SQLite -- which is a standalone file like Access), but this statement seems misleading: assuming you haven't done something extremely silly, the only user using the Access file is IIS/IUSR/the aspnet worker process.
posted by yerfatma at 10:05 AM on March 29, 2006
Access doesn't scale well for the web. Since only one person can use it at the same time, you can very easily bring the whole site to a crawl by using Access. But it will work.
Eh, I'm almost positive that an Access MDB can be used by more than one person at a time.
Or so I seem to remember from an Access app that I built for a power company in... 1998. With the LDB and MDE front-ends, it seemed to be pretty distributed without many problems...
Wow, has it been that long?
posted by unixrat at 10:09 AM on March 29, 2006
Eh, I'm almost positive that an Access MDB can be used by more than one person at a time.
Or so I seem to remember from an Access app that I built for a power company in... 1998. With the LDB and MDE front-ends, it seemed to be pretty distributed without many problems...
Wow, has it been that long?
posted by unixrat at 10:09 AM on March 29, 2006
This is a fantastic FUDfest if I've ever seen one, so it can't hurt to throw in my own underqualified opinion.
With the arrival of SQL Express Edition, I don't think there is much reason to use Access for new custom projects anymore. MSSQL Express can be deployed without paying a license fee, and it has a much more robust engine that Access.
I'd skip mySQL, unless it's required by some framework or application that you are using and customizing. It's support for transactions is relatively recent and is dependant on code that is now controlled by Oracle. Postgres is also open source, has had transactional support for years, and isn't in the process of being encircled by Larry Ellison.
posted by Good Brain at 10:41 AM on March 29, 2006
With the arrival of SQL Express Edition, I don't think there is much reason to use Access for new custom projects anymore. MSSQL Express can be deployed without paying a license fee, and it has a much more robust engine that Access.
I'd skip mySQL, unless it's required by some framework or application that you are using and customizing. It's support for transactions is relatively recent and is dependant on code that is now controlled by Oracle. Postgres is also open source, has had transactional support for years, and isn't in the process of being encircled by Larry Ellison.
posted by Good Brain at 10:41 AM on March 29, 2006
Avoid Access like the plague. It will come back to haunt you. OpenOffice Base is even worse than Access so avoid that as well.
I recommend you use PHP+MySQL instead. Visual Studio Express 2005 ASP.NET+MySQL/MSDE is another alternative.
And you should really consider something like osCommerce instead of reinventing the wheel.
posted by Sharcho at 10:41 AM on March 29, 2006
I recommend you use PHP+MySQL instead. Visual Studio Express 2005 ASP.NET+MySQL/MSDE is another alternative.
And you should really consider something like osCommerce instead of reinventing the wheel.
posted by Sharcho at 10:41 AM on March 29, 2006
osCommerce can be a *bitch* to customize.
if the customer has something in mind that osCommerce doesn't do, forget it -- it's not going to do it, unless you want to start pulling it apart from the insides and adding hacks. IME, this sort of process doesn't end well.
If you have ten weeks of part-time work you should be able to bust out something custom that may have less overall features, but *has the features the company actually needs*.
btw: if you look at a lot of the new successful web apps out there, they actually have *fewer* features than many other products, but just do them damn well. Think basecamp, for example.
Just sayin'.
posted by fishfucker at 11:04 AM on March 29, 2006
if the customer has something in mind that osCommerce doesn't do, forget it -- it's not going to do it, unless you want to start pulling it apart from the insides and adding hacks. IME, this sort of process doesn't end well.
If you have ten weeks of part-time work you should be able to bust out something custom that may have less overall features, but *has the features the company actually needs*.
btw: if you look at a lot of the new successful web apps out there, they actually have *fewer* features than many other products, but just do them damn well. Think basecamp, for example.
Just sayin'.
posted by fishfucker at 11:04 AM on March 29, 2006
also, I'd cast my vote for PHP/MySQL here too. The one major site I've done in using an MDB backend is DOGGGGG SLOW (although that could also be 1) i didn't know what i was doing, and 2) coldfusion. eewwww).
posted by fishfucker at 11:07 AM on March 29, 2006
posted by fishfucker at 11:07 AM on March 29, 2006
If you don't have a programmer with web/db experience, you're doomed.
If you do, use Django or Ruby on Rails with PostgreSQL and you're golden.
posted by Zed_Lopez at 11:09 AM on March 29, 2006
If you do, use Django or Ruby on Rails with PostgreSQL and you're golden.
posted by Zed_Lopez at 11:09 AM on March 29, 2006
Response by poster: zed_lopez: how is it doomed more than those you mentioned?
posted by uni verse at 11:19 AM on March 29, 2006
posted by uni verse at 11:19 AM on March 29, 2006
I wonder if people bashing Access have used it in the last few years. I just finished helping a small company set up a modest networked Access app for multiple users. There are numerous provisions for user accounts, layered permissions, record locking, etc. to permit multiple users while maintaining data integrity and business processes.
A set of ColdFusion+Access internal & public sites I set up for an Inc. 500 company years ago have chugged along happily (on an out-of-the-box Windows server installation, no less) to this day.
Sure Access has limitations, but it is inexpensive, easy, pretty flexible, and if Itty-Bitty Clothing Co. becomes TransGalactic Clothing Corp. in the future, then you can charge them outrageous consulting fees to easily upsize them to SQL.
My real recommendation to a serious business is to use an established e-commerce package, whether OScommerce or something even easier, like a hosted service.
The syncing-with-accounting-package spec will be your big hurdle. Is that with some yet-to-be-chosen accounting app, or do they already use, say, Quickbooks? GoDaddy offers an inexpensive, flexible hosted e-commerce package that can report to Quickbooks online.
There would have to be some very compelling reasons to make me home-cook something that's already been thoroughly engineered, tested and proven by others. Database normalization, user authentication, usage logs, audits, reports... ugh - even a "little" application can turn out to be a big project with lots of areas to get stuck in.
But, maybe we're going overboard. This small business is willing to run with the winning project of a school contest, so perhaps all this hand-wringing analysis over mission-critical scalability and reliability just isn't justified. And your point was that you want to build something for this contest. Which brings me back to: go ahead and use Access if you want.
posted by Tubes at 11:36 AM on March 29, 2006
A set of ColdFusion+Access internal & public sites I set up for an Inc. 500 company years ago have chugged along happily (on an out-of-the-box Windows server installation, no less) to this day.
Sure Access has limitations, but it is inexpensive, easy, pretty flexible, and if Itty-Bitty Clothing Co. becomes TransGalactic Clothing Corp. in the future, then you can charge them outrageous consulting fees to easily upsize them to SQL.
My real recommendation to a serious business is to use an established e-commerce package, whether OScommerce or something even easier, like a hosted service.
The syncing-with-accounting-package spec will be your big hurdle. Is that with some yet-to-be-chosen accounting app, or do they already use, say, Quickbooks? GoDaddy offers an inexpensive, flexible hosted e-commerce package that can report to Quickbooks online.
There would have to be some very compelling reasons to make me home-cook something that's already been thoroughly engineered, tested and proven by others. Database normalization, user authentication, usage logs, audits, reports... ugh - even a "little" application can turn out to be a big project with lots of areas to get stuck in.
But, maybe we're going overboard. This small business is willing to run with the winning project of a school contest, so perhaps all this hand-wringing analysis over mission-critical scalability and reliability just isn't justified. And your point was that you want to build something for this contest. Which brings me back to: go ahead and use Access if you want.
posted by Tubes at 11:36 AM on March 29, 2006
unixrat: I+customer have no platform or program bias, except i have a little experience with Mysql server+access, and none with others. No language is specified: caveat I love java. About that 'open developer base' any website recommendations? thanks!
If you're using java, any database will work just as well with JDBC, although there are some some drivers work diffrently in unexpected ways.
I would actually go with PostgreSQL rather then MySQL. Postgre is more powerfull then MySQL, but also free.
posted by delmoi at 11:36 AM on March 29, 2006
If you're using java, any database will work just as well with JDBC, although there are some some drivers work diffrently in unexpected ways.
I would actually go with PostgreSQL rather then MySQL. Postgre is more powerfull then MySQL, but also free.
posted by delmoi at 11:36 AM on March 29, 2006
Like I said at the beginning, I've used Access as a database with hundreds of thousands of records, for a website that got about 2000 hits a day. Eventually I migrated to SQL server (which I got for free as a student) and the migration was painless and worked without a hitch.
And remember this is a tiny project. People talking about concurrency, high load, etc? Come on, none of that will be an issue.
I'm not saying that he should use Access, but it would be suitable.
Of course, these days there's no reason not to go with MySQL or (even better) PostgreSQL.
posted by delmoi at 11:44 AM on March 29, 2006
And remember this is a tiny project. People talking about concurrency, high load, etc? Come on, none of that will be an issue.
I'm not saying that he should use Access, but it would be suitable.
Of course, these days there's no reason not to go with MySQL or (even better) PostgreSQL.
posted by delmoi at 11:44 AM on March 29, 2006
Everyone's recommending [whatever they have most experience with].
You should go with [whatever you have most experience with].
As I read this, it's primarily an internal application for managing 20 stock levels, with a nice-to-have of a purely static website for viewing products. You could store this data in text files; any modern RDBMS is total overkill. Go with what works for you.
(What would work for me would probably be oscommerce with most of the features (like buying stuff) turned off, and maybe a more friendly stock-control page. But that's just me.)
posted by Leon at 11:57 AM on March 29, 2006
You should go with [whatever you have most experience with].
As I read this, it's primarily an internal application for managing 20 stock levels, with a nice-to-have of a purely static website for viewing products. You could store this data in text files; any modern RDBMS is total overkill. Go with what works for you.
(What would work for me would probably be oscommerce with most of the features (like buying stuff) turned off, and maybe a more friendly stock-control page. But that's just me.)
posted by Leon at 11:57 AM on March 29, 2006
I don't understand your question, uni verse. Without an experienced web/db programmer, you're doomed independent of platform. With one, any platform mentioned would be adequate; my recommendations were for two that are well suited to the task.
Even for a small problem domain, developing a working, robust, secure production system is a hard problem, with lots of gotchas lurking. No matter how highly touted the shallow learning curve of PHP or whatever other system is, shallow learning curves don't, of themselves, make hard problems easy.
On the other hand, any company willing to commit to using the winner of a school competition as a production system almost certainly doesn't know how to distinguish a robust, secure system from one which isn't. Of the entries that aren't obviously broken, the winner will probably be the one whose design the judge finds prettiest. My statement of doom was about the chances of making it good, not winning the contest.
posted by Zed_Lopez at 12:15 PM on March 29, 2006
Even for a small problem domain, developing a working, robust, secure production system is a hard problem, with lots of gotchas lurking. No matter how highly touted the shallow learning curve of PHP or whatever other system is, shallow learning curves don't, of themselves, make hard problems easy.
On the other hand, any company willing to commit to using the winner of a school competition as a production system almost certainly doesn't know how to distinguish a robust, secure system from one which isn't. Of the entries that aren't obviously broken, the winner will probably be the one whose design the judge finds prettiest. My statement of doom was about the chances of making it good, not winning the contest.
posted by Zed_Lopez at 12:15 PM on March 29, 2006
Response by poster: zed:yes, thats what my comment asked, I see what you mean.
delmoi,
tubes,
leon, good points, thanks.
posted by uni verse at 1:01 PM on March 29, 2006
delmoi,
tubes,
leon, good points, thanks.
posted by uni verse at 1:01 PM on March 29, 2006
Just by way of mentioning it, have people noticed the new GUI MySQL management tools available for download? That will have decreased a certain type of gap between Microsoft and MySQL, I'd think. Windows, Mac and Linux versions.
posted by AmbroseChapel at 2:37 PM on March 29, 2006
posted by AmbroseChapel at 2:37 PM on March 29, 2006
Um... I'll put in for Microsoft Access plus a web server/host with Microsoft Office Extensions. If you're going for something quick and don't have a lot of experience, this is a great way to start, since office will write a lot of the code for you, and you can then customize and understand what it's doing in a context (your own data) that you already understand.
People can bash all they want, but starting by investing my time in Access provided easy-to-understand and learn insight into reporting, querying, data import and export, ASP and -- because it's integrated with Office -- invaluable exposure to standard aspects of Office, from Excel-style data manipulation to VBA.
I can code dynamic web pages with the same agility I can set up complex Excel reports today, and can then use ODBC to connect my report to a larger database and pull down data. All started with Access, and if I had invested my time in a less available system, there are a lot of skills I would have lost that were more than worth the personal costs of the the software's shortcomings.
posted by VulcanMike at 2:49 PM on March 29, 2006
People can bash all they want, but starting by investing my time in Access provided easy-to-understand and learn insight into reporting, querying, data import and export, ASP and -- because it's integrated with Office -- invaluable exposure to standard aspects of Office, from Excel-style data manipulation to VBA.
I can code dynamic web pages with the same agility I can set up complex Excel reports today, and can then use ODBC to connect my report to a larger database and pull down data. All started with Access, and if I had invested my time in a less available system, there are a lot of skills I would have lost that were more than worth the personal costs of the the software's shortcomings.
posted by VulcanMike at 2:49 PM on March 29, 2006
Of course, I'm saying that as someone whose career was only full-time development for a year or two. If you have professional dev in your future, I'd encourage you to immerse yourself in all of the software mentioned above!
posted by VulcanMike at 2:51 PM on March 29, 2006
posted by VulcanMike at 2:51 PM on March 29, 2006
Response by poster: vulcan mike, where did u get your info from (access data export, reports, excel)?
posted by uni verse at 3:31 PM on March 29, 2006
posted by uni verse at 3:31 PM on March 29, 2006
It strikes me that the SqlServerLite approach may be right. You have some experience with the tools, you can use Access as a front end, and you get the quite robust SQL Server engine.
It's possible to write Access databases that handle a lot of data, but in general, MDB files just aren't that good... the concurrency is weak and the reliability is poor. I had a customer running on MDB for years, and visiting their site was a perennial exercise in damage control. "Ok, how much data was lost this time?"
SqlServerLite will let you use the tools you like, has a reasonable level of concurrency support (5 sessions, isn't it?) and shouldn't lose data on its own, or crash. As long as the server is good, the database should be fine.
MySQL and PostgreSQL are also excellent, and if you don't mind the slightly cruder tools, you could implement the whole project for $0.
Even if you use the "free" SQLServerLite, you still have to pay for Windows, you may have to pay for Access (lots of $), and upsizing would get VERY expensive. If you're able to invest the extra time it takes to get the open source stuff working, you can scale it to any reasonable size, completely for free. And Unixy-style machines are easier to admin remotely, so if you need to fix something for them later, you can very possibly do it from your chair in your office.
Overall, basically, Windows is easier up front, Linux/MySQL is easier over the long haul. This may be totally a short-term project, and Windows/SQLServerLite may be eminently suitable.
Just remember, companies tend to keep things that work, and it's entirely possible you'll get a call five years from now asking you for help with it....
On preview: VulcanMike's arguments are pretty good, too. For a total amateur, Office really isn't a bad starting place.
posted by Malor at 4:51 PM on March 29, 2006
It's possible to write Access databases that handle a lot of data, but in general, MDB files just aren't that good... the concurrency is weak and the reliability is poor. I had a customer running on MDB for years, and visiting their site was a perennial exercise in damage control. "Ok, how much data was lost this time?"
SqlServerLite will let you use the tools you like, has a reasonable level of concurrency support (5 sessions, isn't it?) and shouldn't lose data on its own, or crash. As long as the server is good, the database should be fine.
MySQL and PostgreSQL are also excellent, and if you don't mind the slightly cruder tools, you could implement the whole project for $0.
Even if you use the "free" SQLServerLite, you still have to pay for Windows, you may have to pay for Access (lots of $), and upsizing would get VERY expensive. If you're able to invest the extra time it takes to get the open source stuff working, you can scale it to any reasonable size, completely for free. And Unixy-style machines are easier to admin remotely, so if you need to fix something for them later, you can very possibly do it from your chair in your office.
Overall, basically, Windows is easier up front, Linux/MySQL is easier over the long haul. This may be totally a short-term project, and Windows/SQLServerLite may be eminently suitable.
Just remember, companies tend to keep things that work, and it's entirely possible you'll get a call five years from now asking you for help with it....
On preview: VulcanMike's arguments are pretty good, too. For a total amateur, Office really isn't a bad starting place.
posted by Malor at 4:51 PM on March 29, 2006
Access is fine for the front end, but it is a lot easier to write the back end in SQL (I am only familiar with SQLServer) and then link it to Access.
posted by radioamy at 6:11 PM on March 29, 2006
posted by radioamy at 6:11 PM on March 29, 2006
If you like developing in Access but want to code in PHP, you could always code your front end in PHP and ODBC to your Access backend. I've done this before on a Windows box (running PHP and Apache). With UnixODBC, you should be able to pull it off on a Linux box as well. Running it on Linux would be better for a lot of reasons.
Even though I know how to do it with Access, and even though I spend quite a lot of time creating things in Access as a part of my day job, I'd still do this in MySQL. If you're developing on Windows (even for ultimate deployment on Linux), you can get an all-in-one dev platform going in minutes with WAMPserver. And there are several all-in-one installers of this sort for Linux. WAMPserver even comes with phpMyAdmin to make building tables and such easier and it has SQLite as a backend option, too.
And everyone is right about the accounting department/HR hookup. It's very easy to use SQL to spit query results out as CSV or even as an Excel file. Offer them that and no more. Let them deal with integrating the data into their workflow.
posted by wheat at 9:10 PM on March 29, 2006
Even though I know how to do it with Access, and even though I spend quite a lot of time creating things in Access as a part of my day job, I'd still do this in MySQL. If you're developing on Windows (even for ultimate deployment on Linux), you can get an all-in-one dev platform going in minutes with WAMPserver. And there are several all-in-one installers of this sort for Linux. WAMPserver even comes with phpMyAdmin to make building tables and such easier and it has SQLite as a backend option, too.
And everyone is right about the accounting department/HR hookup. It's very easy to use SQL to spit query results out as CSV or even as an Excel file. Offer them that and no more. Let them deal with integrating the data into their workflow.
posted by wheat at 9:10 PM on March 29, 2006
uni verse wrote "vulcan mike, where did u get your info from (access data export, reports, excel)?"
Hmm. Back in those days, everything wasn't at the touch of a Google, so I probably rocked a big thick book on Access with "Gold" or "Ultimate" in the title that touched on all those good things. Between Google, Wizards and (believe it or not) the Office Help system with its ability to search the Office website for additional answers, I'd think you'd be able to go step by step to reach any goal you're approaching.
I also did a lot of trial and error learning in each of the Access tabs by running wizards and then digging into what the app created to customize and improve. Once of the nice things about using a desktop app -- especially one with lots of visual tools and a familiar UI, if you can get away with it, is that you can do away with a lot of the configuration hassles, and focus on problem solving and quickly iterate step by step through your solution
It's certainly a lot more straightforward than doing a local install and configuration of a database server and web server for development, or worse setting up for local development with a remote install of both the database and web server (Though, depending on your OS setup, a local install of IIS might be as straightforward as going to Add/Remove programs, in order to complement your local Access work). If you're familiar with Access, also, your first experience in Microsoft SQL Server Admin will be a very unintimidating experience. as it used many of the same interface elements.
As I said earlier, if you want to get involved with all the technical intracacies of the other solutions, you certainly should... but it sounded like you were a bit pressed for time, and the one thing I've learned over the years -- for myself, at least -- is that getting too excited about the technology involved with the solution often makes solving the core problem a much more daunting task.
Every hour spent configuring software or learning a new system for little more than novelty is one less hour spent getting the data structure right or building out the best data entry and reporting interface. In the end, your customer cares that their system is stable and that it meets their needs, not what systems you used.on the backend.
If Access can't cut it, performance-wise, it's my understanding from limited work with SQL Server that upsizing is not a massive headache if the amount of data and necessary structure is as small as planned for this project... so you could ultimately build out a working prototype using Access and then upsize to one of the solutions mentioned above... putting that task further on in the process ensures that you're still able to cover your fundamental needs up front, and can do any necessary cramming on that final part of the project if it's necessary.
posted by VulcanMike at 10:11 PM on March 29, 2006
Hmm. Back in those days, everything wasn't at the touch of a Google, so I probably rocked a big thick book on Access with "Gold" or "Ultimate" in the title that touched on all those good things. Between Google, Wizards and (believe it or not) the Office Help system with its ability to search the Office website for additional answers, I'd think you'd be able to go step by step to reach any goal you're approaching.
I also did a lot of trial and error learning in each of the Access tabs by running wizards and then digging into what the app created to customize and improve. Once of the nice things about using a desktop app -- especially one with lots of visual tools and a familiar UI, if you can get away with it, is that you can do away with a lot of the configuration hassles, and focus on problem solving and quickly iterate step by step through your solution
It's certainly a lot more straightforward than doing a local install and configuration of a database server and web server for development, or worse setting up for local development with a remote install of both the database and web server (Though, depending on your OS setup, a local install of IIS might be as straightforward as going to Add/Remove programs, in order to complement your local Access work). If you're familiar with Access, also, your first experience in Microsoft SQL Server Admin will be a very unintimidating experience. as it used many of the same interface elements.
As I said earlier, if you want to get involved with all the technical intracacies of the other solutions, you certainly should... but it sounded like you were a bit pressed for time, and the one thing I've learned over the years -- for myself, at least -- is that getting too excited about the technology involved with the solution often makes solving the core problem a much more daunting task.
Every hour spent configuring software or learning a new system for little more than novelty is one less hour spent getting the data structure right or building out the best data entry and reporting interface. In the end, your customer cares that their system is stable and that it meets their needs, not what systems you used.on the backend.
If Access can't cut it, performance-wise, it's my understanding from limited work with SQL Server that upsizing is not a massive headache if the amount of data and necessary structure is as small as planned for this project... so you could ultimately build out a working prototype using Access and then upsize to one of the solutions mentioned above... putting that task further on in the process ensures that you're still able to cover your fundamental needs up front, and can do any necessary cramming on that final part of the project if it's necessary.
posted by VulcanMike at 10:11 PM on March 29, 2006
« Older Help an 11 yr. old bound for music school | Did Russian wipe out national languages? Newer »
This thread is closed to new comments.
Access will be fine. I ran a website with an Access database that had hundreds of thousands of records, it was fine. And it's easy to 'upsize' a database from Access to SQL Server if you do need to scale.
A bunch of people will tell you to do this in MySQL/PHP
posted by delmoi at 8:33 AM on March 29, 2006