Re: Advice needed for a growing Access 2000 project

Larry Linson wrote:
In Access 2.0 days, I worked on an Access client to Informix, that had a few more than 1,000 total objects. However, it turned out that quite a few of those were "leftovers" from previous releases, no longer accessible from anywhere but the database window, and, thus, no longer used. When we did Y2K remediation, we removed about 30% of the 1,000 objects with no ill-effects. Had we been authorized more time and effort for analysis, it's possible we might have removed more.

As to the post you cite, anybody can post anything to this newsgroup (and most others, unless they are moderated), and I have no idea where he would have gotten the idea that there was a thousand-object limit. You'll also find some who contend that Access falls over with four or more users, too -- it is likely that those posters had a database that was so poorly architected, designed, and implemented running in an environment so flakey that their contention was true. But that certainly isn't the _norm_ -- without any 'heroic' measures, there are routine reports of split Access DBs (not client-server, which can support many, many more users) with 30 - 70, and some reports of 100+ concurrent users.

And, on the Access 2.0 client that I mentioned earlier, there were around 200 users on a WAN and never a performance hit as we added more, if you are having any worries about the number of users yours will support. Also, FYI, in direct contradiction of the marketing hype surrounding ADPs in the Access 2000 time-frame, the Access team at Microsoft now, once again, recommends MDB <-> ODBC <-> Server DB as the approach of choice.

But, there was no discernable performance penalty for having the extra objects there. I suspect that I would seriously consider doing some careful analysis, and probably "pruning" what turned out to be "deadwood", but I think you have a long way to go before you encounter problems due to the number of objects. Should that happen, you can likely resolve it by splitting your front-end into multiple databases, each addressing specific business areas of your database application.

Access 2000 is "out of normal support" from Microsoft at this point. When it was first released, it was arguably the worst-ever release of Access (with Access 95 being strong competition for that title). After three Service Packs and a number of hotfixes, it seems tolerably stable. I'd suggest you consider upgrading your users to Access 2003, and regenerating in Access 2002/2003 format, as I have had very good luck with Access 2003 (though if you go to Allen Browne's site and review the open issues, you'll see it is not 'perfect', either). I would not suggest considering Access 2007 at this time; and would even evaluate what is fixed when the Service Pack 1 is released before leaping feet-first into it. Office 2007 did make drastic changes to the user interface, and many companies are reluctant to invest in the re-training and re-learning curve to regain productivity that will initially be lost.

Finally, in my (not-so-humble) opinion, for "Windows apps", that is, individual-user applications, modest-sized multiuser applications, and client-server applications of any size, Dot Net does NOT "help along" any of these issues. But, FYI, there is no "migration to .NET"... there would be a "re-implementation in .NET." DotNet targets the distributed application in a (huge) enterprise environment via the Internet or an intranet. In my view, they have yet to catch up with the alternatives contemporary to the very first release of .NET for the "Windows apps" -- Access, FoxPro, classic VB, and classic C++, as a few examples. It's not a matter that they _could not_, it is a matter of Microsoft's emphasis for "real development products."

Best of luck,

Larry Linson
Microsoft Access MVP





"Howard Canaway" <hcanaway@xxxxxxxxxxxxxxx> wrote in message news:xfidnbyntK8sV0vbnZ2dnUVZ_sKqnZ2d@xxxxxxxxxxxxxx
Hello, I help maintain an Access 2000 front end for a rapidly growing business(our back end is Postgresql). As we grow we have concerns over the long term viability of Access 2000 to keep us going through all the growth we need.

Having read a post by one Bruce Pick about some maximums that Access 2000 has we have grown a bit more concerned than we were before, currently we have about ~800 forms and reports and Bruce lists the maximum number of objects at 1000.

My questions are:

Is this 1000 a hard limit? Do you or anyone you know run an Access 2000 DB with 3000 objects?

Would just upgrading to Access 2007 solve this or would we be trading a 1000 object limit for a 2000 object limit?

Other than migrating to .Net are there any other options available to help this along?

Thank You in advance
~Howard Canaway



Thank you for your reply, It gives me a good bit of confidence that we can move on as we are for a while longer. The post I reference was in reference its self to the MS Access Help file under "Microsoft Access database general specifications"

"Modules (including forms and reports with the HasModule property set to True) 1,000"

Maybe I was unclear about what I meant when I said Objects, I apologize. In my head I tend to group forms, reports, modules as objects; its a bit hard for me to separate them from each other in here, my background is more of a standard standalone applications programmer, I grew up with C and C++ so I don't think of them as different from each other.

I'm aware that moving to .Net would be a trial (by fire and brimstone). I could, in my mind at least, just as easily move the code base to Java or a web based solution, its just an example to get you to see where the only solution I could see, that being moving to an stand alone application rather than encapsulated in Access.

~Howard Canaway
.