Practical Limits (Access 2k, XP)

Topic

New Reply

I’d like some advice on the real practical limits on a network based/shared Access database. We use a few applications we have purchased that are based on Access 97-2K and we are planning to develop a couple using 2k or XP. What I haven’t found is a good bench mark of how many records or how large the database can grow before it starts to get corrupted or response times degrade. I have heard that after about 10,000 records we should consider migrating to MS Sequel server. A couple of the vendors have suggested that the small business versions of their products are limited and we need to go to the expense of upgrading to Corporate level products. As a medium-to large church, we want to be cautious about expenses, but not limit our capabilities unnecessarily. We have a membership of about 8000, but with all the extended ministries, we maintain 50-100,000 records per year on an older legacy system. In addition, financial transaction details can exceed that number as well. I would appreciate any practical advice you may have.
Thanks for any input!

Reply | Quote

Replies

There are limits to the size of an Access database, but depending on the version, that is either 1 or 2 gigabytes, which is far, far more than 10,000 records. I’ve run many Access database that had as many as 400,000 records overall in them and I know of much larger databases that have run happily in Access. The degradation comes from the number of simultaneous users and from poor database design, not from the record limits within Access. Corruption is a function of a number of conditions and user actions, not the result of database size per se.

Ten thousand records is a modest database size and certainly doesn’t require SQL Server. However, if you want to maintain historical records over a long period of time, then SQL Server might be a wise choice as a back end.

Reply | Quote

Just to add to what Charlotte said. There are no definitive guidelines for determining when a database’s performance will start to degrade. The factors include: # of concurrent users (and what they are doing), the overall size of the database, form design, database design, etc.

I would not consider 10,000 records to be a problem at all.

Reply | Quote

Thanks folks! It was not the 10,000 that really worried me so much as the 100,000 we usually end up with by the end of the year and what I had read mentioning 10,000 records as a kind of milestone. Would these limits also apply to a database we might access using FrontPage2002 for browser display?
Thanks again!

Reply | Quote

Hi – it’s great to have another grandparently type (judging form your signature) in the fray. Regarding your FrontPage question, that can get sticky. The problem with web pages – assuming it’s not intranet – is that you don’t know how many users are going to be banging on it a once. I don’t know enough about the FrontPage extensions for web servers to know whether they do things in serial fashion, or whether each access to the MDB has it’s own connection. The issue is that 256 users is all Access allows for. If you are using ASP pages, I know for a fact that each web session uses it’s own connection, so it is a concern in that kind of design. The other problem of course, is that you want to be sure to design your web pages so that only a few records are returned at a time. You’re welcome to look at a web site powered by an Access database, though it’s still a work in progress. The url is http://www.cfmf.net[/url%5D – it contains about 5000 records at the moment and is a relatively simple database with one main table.

I should add that I recently built a church database that tracked about 5000 members, and performance was quite acceptable. And there are several church-oriented databases that are Access based, and at least one that you can have a choice of an Access or a SQL Server back-end. Of course the bigger ones tend to be expensive, and frankly in my humble opinion, sport rather clunky user interfaces. Hope this rambling is useful.

Reply | Quote

Thanks Wendell, Yeah, I’m grandparently!
The use of FrontPage would be for Intranet, but its nice to know the number of user “limits”. I have looked at many CM (Church Management) programs and I agree, most have clunky interfaces, and many are overpriced and not particularly interested in customizing for our use. OBTW, You helped me a couple of months ago to move legacy CSV files from our AS/400 to Access for a membership reaffirmation project we had going, remember the Julian dates? It worked out great, I was able to create nested tables and provide a custom query for a report that the tech staff created and used for the dinners. Our 400 is aging and we use and older version of JDE on it for the membership and as a bulletproof accounting program. They now want to add pictures to a browser based query for the senior pastors to use, and I was planning to use FrontPage to do that, since JDE is exhorbitant on their upgrade costs for a version that would do that kind of thing. We are thinking of dumping updates, say weekly, to an Access file, as they only need a passive display at this time. But we also use other apps, like EMS for facilities scheduling, and the number of records skyrockets over the course of a year. EMS tells us that we need to move to EnterPrise versions to handle all the queries and scheduling activity, so the metrics are helpful to try to make decisions on expenditures with a limited budget. I appreciate your interest and comments!

Reply | Quote

I hesitate to recommend any of the commercial systems specifically pointed at churches, but what the heck! A church I know about has just converted to a system from Shelby Systems that they seem to like. It has an integrated accounting and payroll function along with about every function known for chuches. It is a Visual Basic app with either Jet/Access backend or a MSDE/SQL Server backend. It would probably set you back at least 5 figures for a church your size. And I personally think the user interface is kinda clunky and not very intuitive, but it is a pretty popular system. For what it worth.

Reply | Quote

Thanks Wendell,
We used to use Shelby for one of our stand alone ministries, back when it was a DOS upgrade to Win3.1. I spent lots of hours fixing stuff and interfacing printers. I have not looked at it in several years, but I’m sure its a fine product. Our Director of IT is just returning from a sabbatical the 1st of August and this is one of many issues we need to address. We’ve also looked at CMS and others, some have slicker interfaces, but all seem pretty pricey for what they offer. That’s why I was curious about file and record # limits. If we use the existing accounting backend with SQL Server, the browser interface may suffice just fine and would be a lot less costly.
Thanks again for your interest,
Gloria

Reply | Quote