Max size of XP front end

[SJ]

Hi all,

We have a large front end that comprises

700 forms
125 reports low, because most are crystal now
140 queries
10 macros
36 modules

150 ODBC linked tables
back end is SQL server

That means there are over 1000 objects.

We started out in Access 97, we used Access 2k for a while but we hit a
limit there because Access 2k will not support that many vba modules (a
documented limit). So we started using Access XP.

Now to my question,, Quite often when using AccXP, i'm noticing that it
takes a long time to save a form and sometimes there is problems with
saving, such as the code dissapearing etc...

Does anybody out there have any experience with a front end this size????
Are there "practical" working limits.???

Occasionally it gets so slow to load that we have to create a new DB and
import all the objects, then its really fast again.

I've recently turned Name Auto Correct off.

But I think this large size is a problem. You can imagine the importance
of this question, as there would be a lot of work to move away from access.

Thanks, in advance.

Sean

 

[Van T. Dinh]

Not sure of the limits in A97 or A2K but in AXP, you can have up to 32K
Objects and 1000 Modules (including Form/Report CBF Modules).

I *think* Access actually checks the RecordSource i.e. Table Structure when
you opens the DesignView of the Form and when you save the design of the
Form. Thus, the delay may happen at the SQL Server end, especially if you
use Dynaset (the default option) for the RecordSource. In my experience,
the SQL Server deadlocks and blocking connections happen frequently during
this time. Thus, the delay may actually happen at the SQL Server end rather
than your Access MDB. You probably need to use the EM to see what happens
at the SQL Server end at the time.

I have since change most of my RecordSources to Dynaset (Inconsistent
Updates) and that seems to help.

OTOH, I don't have as many Forms (about 200) and Modules (about 70) as you
do. I have about the same numbers of Tables. I have a version of the
database (only for my use) I use to manipulate data that has thousands of
Queries without any problems.

I only lost a Form Module once and the cause was likely to be something else
rather than Access.

Check B.O.L. / your SQL Server book on deadlocks & blocking connections.