MS Access as a backend database

Is this good idea?  If not; is there an article I can referance for my managment?

> From: David Brown > > Is this good idea?  If not; is there an article I can > referance for my managment? Think of it this way MS Access is a desktop application, it's designed for working on people's desktops If you want a server application, then use a server application Just as a note, the Access ODBC driver was limited to 50 concurrent connections - as long as your website was getting few enough hits that the total connections to the database is less than 50, then you'll be fine, but once you break 50, the whole thing breaks

does it also have any of the limitations like excel does, where 65536 is its magic number of limitations? tony > Is this good idea?  If not; is there an article I can > referance for my managment? Think of it this way MS Access is a desktop application, it's designed for working on people's desktops If you want a server application, then use a server application Just as a note, the Access ODBC driver was limited to 50 concurrent connections - as long as your website was getting few enough hits that the total connections to the database is less than 50, then you'll be fine, but once you break 50, the whole thing breaks

>> the total connections to the database is less than 50 what is the ColdFusion pro version limitation for concurrent connections? Eric   does it also have any of the limitations like excel does,   where 65536 is its magic number of limitations?   tony   > Is this good idea?  If not; is there an article I can   > referance for my managment?   Think of it this way   MS Access is a desktop application, it's designed for working on   people's desktops   If you want a server application, then use a server application   Just as a note, the Access ODBC driver was limited to 50 concurrent   connections - as long as your website was getting few enough hits that   the total connections to the database is less than 50, then you'll be   fine, but once you break 50, the whole thing breaks

Bad idea for so so many reasons. First, not at all scalable as a DB.  Need more horsepower for an Access DB?  Buy a more powerful machine.  Need more power for SQL Server? Cluster it. Second, horrible for design & maintenance.  Need to make a change to an Access database?  You've got to take it offline...becomes even mroe of a pain if you don't have local access to the .mdb file. Go with SQL Server or Oracle.  If money is an issue, see if MSDE can work for you, or try MySQL.  Leave Access for the secretaries and the guys who run fantasy football leagues. Just my $.02 Pete David Brown wrote: > Is this good idea?  If not; is there an article I can referance for my managment?

David Brown wrote: > Is this good idea?  If not; is there an article I can referance for my managment? If you have multiple options, Access would typically rank quite low for a number of reasons: - not client-server based - bad concurrency - horrible recovery of space used by deleted tuples - runs on only one platform However, it is not as bad as some answers might suggest. You can absolutely change your schema using DDL without taking your database offline. There is no 50 connections limit. Not that anybody would have that number of connections because Access is not client-server based and is not very concurrent, but 50 connections still means 500 queries per second or something, and that is some serious website (I never had Access at over 80 queries per second with > 99% reads, which it handled just fine). Compared to the often mentioned MySQL as alternative, Access has views, subselects, Unicode support, sane foreign keys, decimal safe arithmatic etc. So that is really a choice between the performance and client-server model of MySQL vs. the features of Access. Compared to the bigger league alternatives such as MS SQL Server, PostgreSQL and Oracle which may or may not be available to you, the choice is really very simple: bigger is better. Jochem

It will work, but from what I've read isn't really suggested. If more than a handfull of users will be accessing access (no pun intended) things have the potential to slow down. If you don't want to spend the $$ for sql, go with MySQL which is free and for me has worked flawlessly with Coldfusion. -Mike > Is this good idea?  If not; is there an article I can referance for my > managment?

Can't handle medium to large user loads Is this good idea?  If not; is there an article I can referance for my managment?   _____  

While I wouldn't recommend Access to anyone - this isn't exactly true. When I use to run Death Clock, I would get 3-4.5 million hits a month. The site ran with no problems using Access as the back end database. Now - I cached the heck out of things - but not everything. I guess the point is - if you _have_ to use Access, you can support significant load as long as you code carefully.

>>this isn't exactly true. When I use to run Death Clock, I would get 3-4.5 million hits a month. I will vouch this statement. The "user limit curse" of Access is pure urban legend. The PROGRAM Acces installed in a lan does have a limit, but as far as CF is concerned, the Access program is not used nor is installed on the server. Only the ODBC driver is used on a flat .mdb file, and only ONE user at a time accesses the database: ColdFusion is the user. This driver is just as good as the Oracle or SQL Server driver. -- _______________________________________ See some cool custom tags here: http://www.contentbox.com/claude/customtags/tagstore.cfm Please send any spam to this address: piegeacon@internetique.com Thanks.

> From: Claude Schneegans > > I will vouch this statement. > The "user limit curse" of Access is pure urban legend. > The PROGRAM Acces installed in a lan does have a limit, but > as far as CF is concerned, the Access program is not used nor > is installed on the server. I should change my name to Mr Urban Legend then I used to run a site (many years ago) on Access, and because most of the queries were dynamic to the user (the user's settings and personal information), I ran into the "maximum user limit" all of the time It depends on what you're using it for - if you CAN cache your queries, then do, but if you can't, then don't use Access

> Only the ODBC driver is used on a flat .mdb file, and only > ONE user at a time accesses the database: > ColdFusion is the user. This driver is just as good as the > Oracle or SQL Server driver. The driver is only part of the puzzle.  There is a hard limit on what Access can handle (I don't know where it is), but that's not my biggest gripe.  The fact that you have to take the database offline to change it is way more significant.  Sure, performance is important, but as Ray points out, if you build the code right (and have the RAM and processor cycles on the app server), you can run amazing load with a POS backend, regardless of the actual storage engine.  Backups are also problematic, because you have to deal with the file as a whole, rather than individual tables, or the like. Also, you have to have it local on the CF server, as far as I know, which can lead to problems. I can't say I've ever had major problems with Access, but at the same time, I'll never use it for a site unless I have no other choice. Cheers, barneyb ----- Excess quoted text cut - see Original Post for more -----

>>The fact that you have to take the database offline to change it is way more significant. The fact that you CAN take the database offline to change it is way more significant for me ;-) -- _______________________________________ See some cool custom tags here: http://www.contentbox.com/claude/customtags/tagstore.cfm Please send any spam to this address: piegeacon@internetique.com Thanks.

You can do that with any client-server db in exactly the same way as you have to do it with Access, as well as numerous other ways (disabling user accounts, locking specific resources, etc).  So it's a moot point if you want to kill the DB to change it, but if you don't, then Access is a burden. Cheers, barneyb ----- Excess quoted text cut - see Original Post for more -----

> From: Claude Schneegans > > The fact that you CAN take the database offline to change it > is way more significant for me ;-) You like being able to kill your site in one easy step? :P

Should be used for testing only. If you are going to use it on a production website, the site should have very little traffic. If not, be prepared for trouble. I could list a million and 1 horror stories where an Access DB was the root of the problem. Dan Phillips CFXHosting.com dphillips@cfxhosting.com Can't handle medium to large user loads Is this good idea?  If not; is there an article I can referance for my managment?   _____   _____  

I concur.  We started out on Access and what a pain after about two years.  Moved to SQL Server.  Now I get sleep... ;-) At 12:32 PM 5/25/2004, you wrote: ----- Excess quoted text cut - see Original Post for more -----

Also have a very small limit on concurrent access (queries) ====================================== Our Anti-spam solution works!! http://www.clickdoug.com/mailfilter.cfm For hosting solutions http://www.clickdoug.com http://www.forta.com/cf/isp/isp.cfm?isp_id=1069 ======================================   Can't handle medium to large user loads   Is this good idea?  If not; is there an article I can referance for my managment?     _____

Hi David, It depends on many factors: budget, size, infrastructure, security, concurrent users, etc. We've been using Access as a backend db for an internal app for 2 years without a hitch... That is, until 2 weeks ago, after a migration meeting (oracle) with my boss (approved :) -- when we stepped out of the conference room, a user was standing in the hall to show me an error: [S1001 (mem alloc err) ODBC Access driver, bla bla] -- Yes, Access had failed us (and given the timing, I couldn't have been more pleased ;) The site met its goals (helping others make money) and now it's time to move to a *real* database management system. http://www.macromedia.com/support/coldfusion/ts/documents/tn17034.htm http://www.clearform.com/microsoft_access.htm -mike Is this good idea?  If not; is there an article I can referance for my managment?

> Only the ODBC driver is used on a flat .mdb file, and only > ONE user at a time accesses the database: > ColdFusion is the user. This driver is just as good as the > Oracle or SQL Server driver. There are plenty of problems with using Access for web applications. While it may work in some cases, such as Ray's Death Clock, it will certainly not work acceptably in many other cases. The problems with Access have nothing to do with the database driver itself, although, under CFMX, people typically use SequeLink to connect CFMX's JDBC drivers to the ODBC driver used by Access, which can't help performance any. These problems also have nothing to do with the Access desktop application itself, which is simply a GUI used to work with Access databases. There are several significant problems with Access, which I'll list in no particular order. First, Access files are typically locked by the application server, and they typically can't be modified while the application server is running. Second, Access files must be directly accessible by the CF server's filesystem, which is not very secure. If your CF server is compromised, so is your data. If you have any "due diligence" security requirements, Access simply won't meet them. Third, Access doesn't automatically reclaim unused space. When you delete records, Access doesn't put new records where the old ones were, it just grabs more space for new records. As a result, within a sparsely populated database in which deletions are frequent, Access might use a tremendous amount of space for hardly any records. A prime example of this is when you use Access to store Client variables within CF. I encountered a situation where a ~2GB Access database had about 200 records total. Needless to say, every page request was excruciatingly slow! Most significantly, Access isn't designed to handle multiple concurrent connections very well. The default granularity of locking within Access is table-level, if I recall correctly. This means, you may have a table with one million records (Access can actually handle large numbers of records with no problem), but if someone's inserting a new record, you won't be able to touch any records within the table until the insert is complete. This isn't to say that Access isn't suitable for any web applications, or that it's an inferior product. It's simply not designed to be a transactional database for applications that support multiple concurrent users. To the degree that you can get away with using it as such, good for you! However, you should expect - and prepare for - the worst. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444

Original Message: > From: Dave Watts > There are several significant problems with Access, which I'll list in no > particular order. First, Access files are typically locked by the > application server, and they typically can't be modified while the > application server is running. That's only true if "Maintain Connection Across Client Requests" is checked.  I leave that unchecked and, considering I never have this problem with my CrystalTech accounts, apparently they don't either.  (I suppose there's a bit of a performance hit with leaving that unchecked, but I've never noticed it). (Now that CT has mySQL support, I'd consider using that, but I haven't had any new projects on CT come up since they started supporting it) Scott --------------------------- Scott Brady http://www.scottbrady.net/

----- Excess quoted text cut - see Original Post for more ----- But without that checked, then your database accesses are going to be slower as it's having to re-make the connection every request when it's not connected

I think your reply was well put! I also think that it applies to any application that requires security and stability. Access is for non programmer to catalog their CD collection. Put mission critical data in a real database like SQL Server, Oracle or MYSQL. Rick > Only the ODBC driver is used on a flat .mdb file, and only > ONE user at a time accesses the database: > ColdFusion is the user. This driver is just as good as the > Oracle or SQL Server driver. There are plenty of problems with using Access for web applications. While it may work in some cases, such as Ray's Death Clock, it will certainly not work acceptably in many other cases. The problems with Access have nothing to do with the database driver itself, although, under CFMX, people typically use SequeLink to connect CFMX's JDBC drivers to the ODBC driver used by Access, which can't help performance any. These problems also have nothing to do with the Access desktop application itself, which is simply a GUI used to work with Access databases. There are several significant problems with Access, which I'll list in no particular order. First, Access files are typically locked by the application server, and they typically can't be modified while the application server is running. Second, Access files must be directly accessible by the CF server's filesystem, which is not very secure. If your CF server is compromised, so is your data. If you have any "due diligence" security requirements, Access simply won't meet them. Third, Access doesn't automatically reclaim unused space. When you delete records, Access doesn't put new records where the old ones were, it just grabs more space for new records. As a result, within a sparsely populated database in which deletions are frequent, Access might use a tremendous amount of space for hardly any records. A prime example of this is when you use Access to store Client variables within CF. I encountered a situation where a ~2GB Access database had about 200 records total. Needless to say, every page request was excruciatingly slow! Most significantly, Access isn't designed to handle multiple concurrent connections very well. The default granularity of locking within Access is table-level, if I recall correctly. This means, you may have a table with one million records (Access can actually handle large numbers of records with no problem), but if someone's inserting a new record, you won't be able to touch any records within the table until the insert is complete. This isn't to say that Access isn't suitable for any web applications, or that it's an inferior product. It's simply not designed to be a transactional database for applications that support multiple concurrent users. To the degree that you can get away with using it as such, good for you! However, you should expect - and prepare for - the worst. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444

Claude Schneegans wrote: > >I will vouch this statement. >The "user limit curse" of Access is pure urban legend. >The PROGRAM Acces installed in a lan does have a limit, but as far as CF is concerned, >the Access program is not used nor is installed on the server. > >Only the ODBC driver is used on a flat .mdb file, and only ONE user at a time accesses the database: >ColdFusion is the user. This driver is just as good as the Oracle or SQL Server driver. > We've had several sites running quite happily on Access for years, but we're gradually moving up to MS SQL Server. A recent upgrade to our busiest client meant the (then Access driven) site fell off the web every 10 minutes or so. It was entirely down to the DB. They're on MS SQL Server now and running beautifully. Also here's another vote for this excellent article: http://www.macromedia.com/support/coldfusion/ts/documents/tn17034.htm originally suggested by mavinson@ra.rockwell.com, it was a big help to me convincing management to stop using Access. HTH Simon Riley

Original Message: > From: cfhelp > Put mission critical data in a real database like SQL Server, Oracle or > MYSQL. Of course, we don't know that the original question was for a "mission critical" application. Just a general question about "web applications". If this is a small business that is building a web site for dynamic content (to ease updating the web site) then I'd say SQL Server or Oracle is overkill and Access would work just fine for that. My general rule of thumb for moving up from Access is whether sensitive information will be stored in the database (such as potentially with e-commerce applications) or if I expect it to be high-traffic.   Scott --------------------------- Scott Brady http://www.scottbrady.net/

Original Message: ----- Excess quoted text cut - see Original Post for more ----- Hence my parenthetical talking about the performance hit.  Again, I've never noticed it.  If your site's performance is noticably bad because of that, then either your code should be tuned better or you're getting enough traffic to justify a non-Access database. Scott --------------------------- Scott Brady http://www.scottbrady.net/

> That's only true if "Maintain Connection Across Client > Requests" is checked. That's correct, but the default setting is to enable this option, I think. > (I suppose there's a bit of a performance hit with leaving > that unchecked, but I've never noticed it). In general, maintaining database connections can provide significantly better performance. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444

Original Message: > From: Dave Watts > > That's only true if "Maintain Connection Across Client > > Requests" is checked. > > That's correct, but the default setting is to enable this option, I think. That was true in CF5 (and presumably earlier versions), but not in CFMX. > In general, maintaining database connections can provide significantly > better performance. Again, I've never noticed huge performance problems with my sites that use Access.  If performance is your big issue, then, yes, Access may not be the way to go.  But, if simplicity and lower costs are more important, than Access may be the way to go.  (Most hosts I've seen will charge more for SQL Server accounts than for Access accounts) Scott --------------------------- Scott Brady http://www.scottbrady.net/

<<(Most hosts I've seen will charge more for SQL Server accounts than for Access accounts)>> That's mainly because the SQL (either MySQL or MS SQL) run on a seperate server.

Original Message: > From: "Dan Phillips" > That's mainly because the SQL (either MySQL or MS SQL) run on a seperate > server. True (and a SQL Server license is more expensive than Access).  But to the customer, it's just an additional cost. (Of course, I think CrystalTech doesn't charge extra for MySQL accounts, so it may be more the license costs than the fact that they're on different boxes) Scott --------------------------- Scott Brady http://www.scottbrady.net/

> Again, I've never noticed huge performance problems with my > sites that use Access. If performance is your big issue, > then, yes, Access may not be the way to go. But, if > simplicity and lower costs are more important, than Access > may be the way to go. (Most hosts I've seen will charge more > for SQL Server accounts than for Access accounts) I'm not sure at what point one would decide that "performance is your big issue," except when one's application doesn't support its users. I've run into quite a few situations where Access was being used in unsuitable ways, and to the detriment of the application's users. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444

> Again, I've never noticed huge performance problems with my > sites that use Access. We built most of our sites in Access for a long time. They were low volume traffic sites that needed some basic commerce.  They seemed to work pretty well until we added one more low volume site and suddenly CF started pausing for long periods of time.  It seems that we hit a concurrency limit of some kind where each site by itself was fine but all the others combined caused trouble.  We then got tasked to upgrade a commerce site with loads of new features one was a cooperative shopping feature (People who have purchased this item have also purchased these).  We tested the query we needed to do against access and it routinely returned in 9,400ms. Way too slow.  We tested the same query against MySql with the same data set and it returned in 800ms we tested it against MS SQL server (separate box) with the same dataset and it returned in 12ms.  That alone made us move away from Access never to return. Not only that but our CF server has been up over 2,000 hours without a problem (we routinely re-boot once every three months just because) where it was hanging at least twice a week before. Another thing is that Search engines are no longer as “friendly” as they once were.  On a regular basis the Froogle bot and the Yahoo bot hit our servers and open thousands of simultaneous connections until they momentarily saturate our T1 line. CF use to choke on the searches and we would never get good ratings in the search engines.  Just by changing to MS SQL all of our database centric hosted sites get better than average ratings in the search engines. Best Regards, Dennis Powers UXB Internet- A Website Design and Hosting Company 690 Wolcott Road - P.O. Box 6028 Wolcott, CT 06716    tel: (203)879-2844 http://www.uxbinternet.com http://dennis.uxb.net

On May 27, 2004, at 3:17 PM, Dennis Powers wrote: ----- Excess quoted text cut - see Original Post for more ----- Something that the folks at http://www.coveryourasp.com/ did was they extensively use application and session variables to store data, which greatly eases the load on Access.  Might be worth trying... -- Damien McKenna - Web Developer - dmckenna@thelimucompany.com The Limu Company - http://www.thelimucompany.com/ - 407-804-1014

Dave Watts wrote: >I'm not sure at what point one would decide that "performance is your >big issue," except when one's application doesn't support its users. Well, there y'go... it works fine until it explodes, and then all you can really do is scramble around and pick up pieces.  One of those things no one who hasn't been through it will accept I think, since the preventive cure smacks the wallet squarely. Or not.  mySQL was my free ticket out of Access Hell.  I meant it to be an interim stop-gap, but something like 2 1/2 years later I haven't found a pressing need to spring the bucks for a SQL Server license (admittedly, thats due to my needs and won't work for everyone).   Still, for all its faults, if you are on an Access-served site and can't afford to get a 'real' database, mySQL is an easy and potentially permanent solution. -- ------------------------------------------- Matt Robertson,     matt@mysecretbase.com MSB Designs, Inc. http://mysecretbase.com ------------------------------------------- --