| Author |
Topic |
|
KnooKie
Aged Yak Warrior
623 Posts |
 Posted - 2003-08-12 : 09:19:18
|
| i have been asked whether Access XP will be able to handle a db with up to 10 concurrent users and with tables holding typically around 1 million records. I have said i will try and find out as i haven't as yet used Access XP. I know it would sound a bit dicey with other Access versions (well 97 that i have here anyway) but thought it a good idea to ask here as someone must have experienced a similar issue !?The alternative is SQL Server with either Access or VB front-end but obviously this will increase cost and development time.thanks for any responses====Paul |
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-08-12 : 09:25:57
|
| Don't know if they are entirely accurate, but have you looked at the specifications in the help section?According to them, Access 2003 can handle up to 2GB databases with up to 255 concurrent users. Don't have Access XP so you'll have to check that yourself. |
 |
|
|
KnooKie
Aged Yak Warrior
623 Posts |
Posted - 2003-08-12 : 09:30:19
|
| I don't as yet have Access XP either only 97 :(That's why i ask really in case someone has experience of it. I know what Microsoft say it will do but i was hoping for a 'real world' answer if you know what i mean.thanks though i didn't know those stats you mention.====Paul |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-12 : 18:47:52
|
| Access can't handle 255 concurrent users, I've had MS techs tell me so. 97 could handle maybe 20 reliably, I've had db's handle 40 without dying, but it was SLOW. XP won't do much better than 50. The problem is the locking architecture used by Access: in plain English, it sucks. But if you're never going to exceed 10-20 concurrent users then you should be okay as long as you maintain it diligently (regular compact and repairs, keep backups) It would be a good idea to use a front-end/back-end setup with linked tables to the data than keeping it all in one MDB file.Also try to avoid memo fields, when they get corrupted they can prevent a database from being used or repaired. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-08-12 : 20:27:13
|
| Another solution might be MSDE. It should easily allow for 10 concurrent users (I think 5 max concurrent connections). Depending on how hard and how long you are hitting the database, you should be ok.Another plus is that if you ever do out-grow MSDE, taking that to SQL server is SUPER easy with no changing of code.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2003-08-13 : 14:36:26
|
| Yes XP Access would handle 10 users. Yes It would Handle 1,000,000 records.To get a useable system you would need to place the main .MDB files on a server and have the applications on the local with a Link to the main. We are currently doing this.HOWEVER we are sorry that we did and are convering to SQL backend with a Access .adp front end. The Access .mdb crashes regularly and the access speed is getting slower and slower also the limitations of .mdb actualy make the devopment harder. The cost of the overall devopment would have been much cheaper had we designed the system with SQL from the start.In Short Do not go there! JimUsers <> Logic |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-13 : 14:39:35
|
| If cost is a limitation, how about SQL Server MSDE?Tara |
|
|
|
GreySky
Starting Member
20 Posts |
Posted - 2003-08-15 : 12:11:25
|
| I am almost exclusively an MS Access developer, having used versions 2.0, 95, 97, 2000, 2002 (XP) throughout my contract career.MS Access will handle the requirements you laid out. However, I do not recommend using MS Access for tables exceeding 250,000. Simply put, you need the power and control from SQL Server at that point.Developing an MS Access database .mdb front-ending via ODBC to SQL Server allows for a hybrid approach to development. I am completely unaware of the "crashing" and "limitations" referred to by JimL. That being said, there is nothing inappropriate about using an .adp, especially in XP (as they've finally fully integrated features such as dynasets being the recordsource for forms that you can actually edit).Bottom line: Yes, XP can handle it; however, one-million-record Access databases are not recommended.Recommendation: MS Access front-end (either .adp or .mdb, doesn't matter) with SQL Server backend.David Atkins, MCPMS Access Contractor - 9 years |
|
|
|
philico
Starting Member
1 Post |
Posted - 2004-01-28 : 12:23:53
|
| Hey buddy, don't believe anybody. People who respond to you are people who 'have been told' something not the kind who done it...Access, especially 97, is extremely capable and can scale very nicely to at least 50 concurrent users. There are no locking problems because it handles locking in a very tranparent way. Only when you step out of Access and use MS Jet or ADO to connect to an Access database from say VB6, you will encounter locking issues, but when things come to this you can use Mutex calls to handle the locking yourself.I developed an application than has 500+ tables, many with more than 3 million rows and 10 fields each. It is spread across 15 Access databases, the total size about 8 Gigabytes. I have 50 users on the system at any time. The front end is also written in Access and is no less than 400 screens, 150 reports, 2500 queries and 250,000 lines of code! What is most impressive, except the services which have been written in VB6 (and used Mutex for concurrent locking) it is all exclusively written in Access 97!Because it was developed to take full advantage of DAO's ISAM calls, it performs extremely fast. Multithreaded architecture aside, Access VBA data handling routines are usually 10-20% slower than their SQL server store procedure counterparts. This is impressive, and someone who develops extremely complicated business logic in SQL, will surely appreciate the simplicity and the power of a procedural language like VBA.Such an application like mine is hard to find out there, but sure enough it attests to the amazing capabilities of Access. Why did I do it in Access? Well, for a few years, I was the only developer in my company supporting 30+ users. Now, my IT department has grown to 3 developers and we support 50 people. We still program in Access, we rarely stay late and never work on weekends, all thanks to the rapid development environment that you can only find in Access. We very rarely experience corruption problems (typically, 2-3 times a year) but then again, there are steps you can take to avoid it. Mind you, SQL server and even Oracle dbs are not immune from data corruption themselves!Access 2000, 2002, 2003 are actually NOT as capable as Access 97 in handling corruption problems because Microsoft has taken away an undocumented switch than helps alleviate such problems. Only 97 has it. The rest try to do it automatically for you but do not do a good job. Also, 97's query compiler can handle more complex queries. And lastly, ADO is less rubust and capable than DAO when it comes to native Access DB handling!Access development is pretty much like street car racing. If you are a whiz kid and you know how to take a Honda Civic, tinker with it and make it beat a Ferrari, you are kicking butt. Otherwise, go buy the Ferrari, spend way more money and forget the whole thing. |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2004-01-28 : 14:15:28
|
| philico,your post is just a douche of balm for my soul. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-28 : 14:22:13
|
quote:
Originally posted by Stoad
your post is just a douche of balm for my soul.
I wonder what that means.Tara |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-01-28 : 14:33:09
|
| philicoAccess is a wonderfull front end. I do all my Forms webpages and reports in access. I have over 500 forms and at least 300 reports.But if you want Flexible security, High speed access on complicated Querries, Superb scheduling,Great Data transfer Capabilities and a DB that is dang near bullet proof. You want a SQL back end. JimUsers <> Logic |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2004-01-28 : 17:34:09
|
| douche (French) = shower (British English),hence,douche of balm (fig.) = smth. very pleasant. |
|
|
|
joynj2
Starting Member
1 Post |
Posted - 2004-02-18 : 16:05:22
|
| Bravo philico.. Finally someone else out there acknowledges that the current trendy standards aren't always the most efficient. I run a db in access with over 120 tables which is speed imperative .. and DAO blows ADO out of the water in terms of performance. If you're looking to do complex queries then ADO is nice but DAO = speed and simplicity. We're able to make close to 1000 calls to the DB per second .. at least 3x faster than ADO. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-20 : 12:14:07
|
Great knock yourselves out....How does one even respond to this?You're talking apples and oranges hereAnd since the thread is about the limitations of Access XP, I guess it's not fair to drag SQLL Server in to the mix....But there's no need to be rude....You really should know who you're talking about when you say stuff likequote:
Hey buddy, don't believe anybody. People who respond to you are people who 'have been told' something not the kind who done it...
ummmm...not exactlyLOL..."don't believe anybody"Brett8-) |
|
|
|
KirbyWallace
Starting Member
2 Posts |
Posted - 2006-01-06 : 12:21:06
|
| I guess I'll chime in here too. I've developed Access applications since 1994, and have even taken the time to write benchmarking applications to test the speed of DAO and ADO. And like other's I've found DAO to be faster - in some operations, MUCH faster than ADO.I've always been a big fan of Access. Back in the days of PowerBuilder, I always knew that Microsoft's answer to PowerBuilder wasn't VB. It was Access! But they never seemed to be interested in promoting it that way.I've had my share of "corrupt database" problems, in 95/97, but I've also found the way to correct the problem most often (ie, find the form that the problem occurs on, go to code view mode, cut out ALL code, change the forms "Has Module" property to FALSE. SAVE. Then Set back to TRUE and past in your code... fixes a LOT of problems - seems the internal compiler get's confused about something.)I have had databases approaching 1 million records, and then things do in fact start to get slow.For the last two years or so of my access development, I prefered data projects (ADPs) as front end to SQL Server (Note: Double Click a View in SQL EM, and it opens it for design. Do that in Access and it RUNS it. Been burnt there more than once.)Ultimately, though, I rarely do Access development anymore because web/intranet has pretty much killed that business, and I prefer it anyway. No rollout. No need for Access on each machine. The "Access Runtime" was a pain in the rear that I'm glad to be rid of. I like that I write it, and then call someone on the phone and say "hey, try this out... yeah, right now! It's already there!"That means Web, SQLServer and ADO for me. And I like the environment and the abilities of the browser better than Access forms anyway, even though I HATE the stunted abilities of HTML form objects - no events, limited control of appearance, etc. But by and large, the pros outweigh the cons. |
|
|
|
|
Access XP Limitations ?