I need to know what are the concerns to store data in microsoft access for a web site.
I know we could have a lot of problem
example : - Multiple connection on the database
Thanks
views:
94answers:
5
A:
Access databases (in my experience) seem to be very poor at allocating space so their size grows larger than expected. I've also had data corruption issues.
If someone has the database opened for editing, that could lock other connections out.
While all db's require maintenance, try running the "maintenance" (compact and repair) on an extremely large database (1GB+) and see what happens. Not only will it likely lock users out, but it could very well corrupt the whole thing
Cody C
2009-08-17 18:52:28
voted it up cause ive had the same experiences with it. curious as to why it was voted down
Matt Briggs
2009-08-17 19:09:48
I'm voting it down because it's an ignorant comment. Server database files also bloat, and you usually schedule routine cleanup processes. If you don't do the same for a Jet/ACE database, you'll run into problems, but it's not a problem with Jet/ACE -- it's caused by your failure to realize you have to do regular maintenance, just like with every other database.
David-W-Fenton
2009-08-17 23:52:12
I'm certainly not going to argue Access or a real db engine. There is a reason few (any?) enterprise class applications are running successfully on Access databases.Try running that "maintenance" (compact and repair) on an extremely large database (1GB +) and see what happens. Not only will it likely lock users out, but it could very well corrupt the whole thing. If you had to store all your critical financial data and couldn't have any backups, would you feel comfy with Access not corrupting that data?? Not me!
Cody C
2009-08-18 00:25:41
Cody, you make a very good point. If your answer had said that you would have gotten my vote, but you didn't say anything about maintenance requiring exclusive access in your answer. Your answer was about how space is allocated in the database which is arguably not exclusive to Access. @David: I was waiting for you to weigh in on this. I knew you wouldn't be able to resist. =)
JohnFx
2009-08-18 14:44:02
@Cody C: Who has suggested a Jet/ACE back end for an "enterprise" application?
David-W-Fenton
2009-08-18 17:07:53
+1
A:
Data size is another concern. For larger sizes you need a more serious database engine.
Scalability, availability, backup support.
Cătălin Pitiș
2009-08-17 18:54:55
-1: How long is it going to take to crush the myth about Size being a key factor for considering a client-server system over Access.
JohnFx
2009-08-17 18:59:46
As long as Access will have problems dealing with big amounts of data, like SqlServer or Oracle...
Cătălin Pitiș
2009-08-17 19:07:15
Behind every instance I have found of "Scalability problems" in an MS Access database (regarding DB size only, not concurrency) there was always a programmer behind it that didn't know what they are doing. Telling this type of programmer to switch to an enterprise grade DB is like advising a person who keeps shooting themselves in the foot to buy a bigger gun.
JohnFx
2009-08-17 19:12:41
In fairness, however, it isn't usually a career programmer behind the problem. Access is often treated as an end user tool (for example it was bundled with office) which encourages people who don't know what they are doing to try to build systems. I think that often we programmers are brought in to fix the inevitable scaling problems and based on pre-existing bias blame the tool instead of the poor DB/Application design.
JohnFx
2009-08-17 19:17:27
JohnFx. And that's fine. At least using Access in a poor fashion is better than using Excel or even Word tables.
Tony Toews
2009-08-18 01:54:07
+1
A:
The problem is that it won't scale - and it really isn't meant to. You have to pick the right tool for the job.
Avoid the trouble now and pick SQL Server (there are free versions - if you qualify for BizSpark you can get powerful free versions), MySQL, or Postgres.
Andy Gaskell
2009-08-17 18:56:10
It depends on the site, don't you think? A read-only site is likely to not be too much of a problem, while one with lots of user edits is going to fall over pretty quickly.
David-W-Fenton
2009-08-17 23:51:00
Indeed Michael Kaplan's multi lingual site www.trigeminal.com is almost read only and serves all the pages from an Acceess database file.
Tony Toews
2009-08-18 01:55:09
That's why I said pick the right tool for the job. If you're an Access pro and you know choosing Access will work, then go for it. The trigeminal reference is suspect - the site serves between 500 and 1500 people a month. I'm sure Access can handle between 20 and 60 visits a day.
Andy Gaskell
2009-08-18 02:21:30
Trigeminal used to get 100K page views a month. It's a dead site since Michael became a Microsoft employee instead of a contract employee.
David-W-Fenton
2009-08-18 17:06:46
@Andy Gaskell: I concur. Access must be fickle and unintuitive. Seems you need to be an Access experts to avoid problems. I had a .accdb go corrupt on me yesterday: 4MB, a few weeks oold, only used by me locally (no network) to test SQL DDL and DML via ADO (never even opened in Access), no forms, no reports, no VBA, etc.
onedaywhen
2009-08-19 08:55:28
@onedaywhen: are you patched up to the latest service pack of A2007?
David-W-Fenton
2009-08-20 19:37:16
@David W. Fenton: well, I don't have Access2007 installed on the machine, only the "2007 Office System Driver: Data Connectivity Components". The download page states, "This package contains components that are at the same version as the “The 2007 Microsoft Office Suite Service Pack 1 (SP1)” release" (http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891)
onedaywhen
2009-08-21 07:35:13
+3
A:
A key problem you will have is availability. Specifically the need to lock the users out to do repairs and make backups, which may not be practical for the 24/7 availability needs of a typical web site.
Concurrency is your #2 concern. A well designed Access DB can often support as many as 50 users, possibly even a few hundred depending on the specific types of access needed (read-only for instance). However, with an app doing a lot of updates you are going to have concurrency headaches much sooner than you would with most client-server type database packages.
Ignore any comments about scalability in terms of DB size. For most applications you won't come close to the limits of MS Access in that regard.
The flip side of the coin is that you add a lot of complexity and cost by switching to a client-server DB. So if you are building a small internal use web-site, Access can be a reasonable choice too. It is all about matching your requirements to the technology.
JohnFx
2009-08-17 19:05:56
+1
A:
Something nobody has mentioned is that how well things work with a Jet/ACE back end (there is no such thing as an Access back end from the standpoint of a web site) is that it depends on what interface you use to access its data.
If you use ODBC or DAO, you can run into problems because neither is threadsafe. But Accessed via OLEDB/ADO Classic, you get threadsafe access. Thus, the only viable method for working with a Jet/ACE data store in a web application is OLEDB/ADO Classic.
(I am reporting what Michael Kaplan has said many times, and he has forgotten far more about Jet than I ever knew)
David-W-Fenton
2009-08-17 23:54:54