September 14, 2001 at 1:17 am
We are building a business application which would be run mostly on Win9*. We are evaluating if the application should use MSDE or Access. The application may also be deployed on SQL. But since the application is fairly simple, we do not foresee problems on that front even if we go for Access. Security of data is of concern. Robustness of the application is very critical. Is there any downside to using MSDE as against Access?
D. Paul
Satyam Infoway Ltd
Chennai
September 14, 2001 at 2:01 am
Access 2000 ADP front ends may be worth investigating as well
Steven
September 14, 2001 at 4:39 am
I cant remember the number of users MSDE will allow, I'd dbl check that just to be safe. If you use MSDE an upgrade to SQL is as painless as you'll ever see. You definitely want to use Access 2000 if you go that way, so that either way you can use ADO and not DAO.
Andy
September 15, 2001 at 12:59 am
Thanks.....Let me explain the backrgound to my question better...The application is being developed in VB and is primarily intended for small offices. The application will be deployed by means of an installation CD which can carry .mdb files.
Users would not need to have a licence for Access and would still be able to run the application. However, we are wondering about wisdom of using .mdb files on account of the following downsides: One, the possibilities of data corruption seems to be higher with .mdb files; Two, Access appears be a technology of the past, MS seems to be focusing more on MSDE and upward, Enterprise-class products.
On the other hand, the benefits of using .mdb files seem to be: One, lower system resources required by applications running on .mdb - our target segment is likely to be running minimal hardware / software configurations; Two, the simpler process of copying compressed .mdb files onto backup media like floppy discs etc. - the application is less likely to be deployed in large networked environments.
In the trade-off, it appears to me that the critical decision parameters would be: stability of .mdb files and future support for Access from MS. Could you throw some light on these?
On your point, Andy, it would appear from our reading that MSDE is a little resource hungry and handling more than 5 concurrent users causes problems.
Have I managed to convey the problem? Shall be grateful if you would be able to help.
Thanks...Paul
D. Paul
Satyam Infoway Ltd
Chennai
September 15, 2001 at 7:05 am
I'd agree that the future of Access is unclear. I love it as a tool for ad hoc data work, never been fond of it for apps. I've used Access 97 a LOT, Access 2K not quite as much. Corruption has been rare. MSDE is a sturdier solution. Everything is log based. Backups don't require all users to exit the application. Either way you have the option of allowing users direct access to the data without going through your UI (your power users will appreciate this).
Whichever you choose, plan for the future and build a good data layer. This will pay dividends when you decide to port to Oracle or mySQL or whatever. It's more work of course.
Andy
September 15, 2001 at 10:34 am
My 2 cents:
Avoid Access.
It is intended as a low end, personal desktop database. While I have built lots of dBase/FoxPro/Access applications, they don't scale well and coruption is a problem at times.
Of course, there is additional overhead for MSDE. You have to devise a backup solution and manage that for your clients. It will make things easier to port to another RDBMS.
Keep in mind, whatever path you go down, you will likely be stuck with it. Redesigning is expensive and rarely undertaken.
Couple examples:
Goldmine CRM system. Written for Access files. When upsized to SQL Server, it is poorly designed, doesn't take advantage of mayn SQL features and ends up running poorly. You have to thorw hardware at it and it still has problems in SQL Server because it was not designed correctly. Especially with text fields. We get corruption becuase the app does not handle updates well.
2nd: I upsized a foxpro app to SQL Server, but we redesigned the app. this took 3 of us 2 years to do, but we ended up with a system that was able to handle more users (old system: 20 users, often corrupt, needed to rebuild dbs every weekend). New system, over 50 users, response time for invoice generation 3x, response time for inventory query 5x. This was an expensive proposition, but the redesign allowed us to take advantage of RI (declared and triggers), so less data integrity issues). We could run backups every 15 min, so more fault tolerance, plus we had the scheduling capailities of SQL Server to automatically run reports at night as well as build denormalized tables for reporting.
HTH. I'd love to hear more reasons one way or the other.
Steve Jones
September 15, 2001 at 11:32 pm
Have made up my mind on MSDE. For future proofing the system, for scalability and for for robustness. Overheads seem to be manageable, to my understanding. Have been facing some other problem with MSDE: will post that under a separate head. Thanks.
Paul
D. Paul
Satyam Infoway Ltd
Chennai
September 17, 2001 at 1:49 pm
March 2, 2002 at 11:34 am
quote:
I cant remember the number of users MSDE will allow, I'd dbl check that just to be safe. If you use MSDE an upgrade to SQL is as painless as you'll ever see. You definitely want to use Access 2000 if you go that way, so that either way you can use ADO and not DAO.Andy
I have been going through the same Access or MSDE question, and have decided on MSDE as best suited for my perposes because of the way that Access locks Pages instead of row-level locking. VB and MSDE will be my development platform, although in retrospect I wish I was using MS Access 2000 and MSDE, but am kind of "locked in to VB" by wording in my contract with my customer. I don't mind that much, because I am looking forward to keeping my VB skills polished.
Sorry, I guess I jumped off the track--MSDE will allow an unlimitted number of users, but is built so that performance degrades after around seven users are using the sytem. Access claims to be able to manage 20-30 users simultaneously, but I can't use Access in my application for even 3 or 4 users because of the Page-locking issues. Because my users will be editing records in close proximaty to each other, they would constantly be locking each other out of the system.
So MSDE is my choice as the development platform, and if larger users want to use the App (:)), then I'll load them with SQL Server.
Thanks,
Ross
Ross Ylitalo
Ross Ylitalo
ross@rossroads.com
March 2, 2002 at 6:37 pm
Access is ok for most small apps and when the only one user is using it locally. MSDE is sturdier and more scalable since you can move up to a full blown SQL Server in a fairly simple process. Also you can centralize the database and not lose a lot of efficiency in the process. And (forgive me since I don't use) I believe it is more secure.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 2, 2002 at 8:46 pm
Access is reasonably secure - even has an option to encrypt the mdb. I agree that MSDE gives you a better/easier upgrade path to SQL than Access does. Page locking is not all bad - in any app you normally want to make your changes quickly and get out. Locking is usually more of an issue when you're using pessimistic locking, sometimes is necessary but makes it hard to scale the app.
Andy
March 2, 2002 at 8:51 pm
Wasnt sure, had to go check - Access2K supports row level locking. For those of you who don't use Access very often you might want to take a look at the improvements made in 2k - not bad for the niche its in.
http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q275561&SD=MSDN&
Andy
March 3, 2002 at 1:47 pm
Yes Jet 4 had a lot of improvement over Jet 3. Access XP also made some improvements on the use of Jet 4 from what was going on in 2000 and I am waiting to see what happens with the next version. However I did see someone make the statement that Access is basically a dying platform but I disagree as it really does have advantages that MSDE and SQL don't have (mainly in the single user distributed area where there is no central server), but I anticipate Access to get more features from SQL server as it grows.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 6, 2002 at 4:53 pm
quote:
Access is reasonably secure - even has an option to encrypt the mdb.
I hate to tell you this but I can get the passwords for any MS Access version, encrypted or not with one littel executable with the mdb name passed as a variable. It immediately gives me the database password. I have tried it on all version of Access including XP and passwords of 250characters of numbers,letters,and symbols.
If you expect any security do not use access.
March 6, 2002 at 5:03 pm
Good point. The built in encryption in both Access and SQL so far has been weak, designed to make it harder to get to the data or the stored procedures, not impossible. Hard to say what the "average user" knows how to do, but in general I think using encryption is better than not if you are trying to safeguard some proprietary data or process.
Andy
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply