N
Neil
Alex,
Thank you for your very helpful post. A few comments below.
The database file itself is about 200-270 mb; but it doesn't have that many
tables. There are two main tables, which make up most of the data; a few
medium-sized tables; and a slew of small lookup tables -- roughly 80 tables,
though I'd say that at any given time less than 10 are in use by any single
user.
As noted, there aren't a lot of tables in use at any given time. And our
most complex form has maybe 40 combo boxes. So we don't seem to be
approaching this limit.
But, be that as it may, the situation I noted in this thread regarded a view
which was fast in an ADP file, and used to be just as fast in an MDB file;
but now is slow in the MDB file. This situation occurs:
* when I use the database files on my unnetworked PC running MSDE with no
other apps running;
* when the MDB file is open without any forms or reports open;
* when the linked view is opened by itself from the database window without
a form.
As noted previously, it takes about 5 seconds just to bring up a screen of
about 20 records, and each time the scroll bar is clicked (even when moving
down a single record), the same or a similar delay occurs.
This same view was working fine until recently, and was used for several
years as the recordsource for a form that edited it in Continuous Forms
view. I added a few fields to the view, and things slowed down. I tried
taking the fields out, but the results are the same. (The adding of the
three fields may have been coincidental to something else that may have
caused the slowdown.)
Either way, the view is still fast in an ADP file, but is slow as molasses
in its original MDB file.
That's good to know. One of the things I've been discussing here is that
this database is going to be rebuilt from the ground up (was converted to
Access from an old database program by a developer, then was modified by
another developer who didn't understand principles of normalized design,
then was inherited by me). I am leaning towards redoing it in MDB,
especially given the many testimonies of problems people have had with ADP.
But your input is certainly significant here as well. So that's good to
know.
Yeah, well, here the whole thing is in the back end. The whole idea is that
SQL Server does all the work and the MDB just has to get the data. But,
apparently, it has problems even with just that!
I didn't see a "FOR BROWSE" option in the SQL 7 BOL. Is that a SQL 2000
option?
Thanks,
Neil
Thank you for your very helpful post. A few comments below.
The database file itself is about 200-270 mb; but it doesn't have that many
tables. There are two main tables, which make up most of the data; a few
medium-sized tables; and a slew of small lookup tables -- roughly 80 tables,
though I'd say that at any given time less than 10 are in use by any single
user.
As noted, there aren't a lot of tables in use at any given time. And our
most complex form has maybe 40 combo boxes. So we don't seem to be
approaching this limit.
But, be that as it may, the situation I noted in this thread regarded a view
which was fast in an ADP file, and used to be just as fast in an MDB file;
but now is slow in the MDB file. This situation occurs:
* when I use the database files on my unnetworked PC running MSDE with no
other apps running;
* when the MDB file is open without any forms or reports open;
* when the linked view is opened by itself from the database window without
a form.
As noted previously, it takes about 5 seconds just to bring up a screen of
about 20 records, and each time the scroll bar is clicked (even when moving
down a single record), the same or a similar delay occurs.
This same view was working fine until recently, and was used for several
years as the recordsource for a form that edited it in Continuous Forms
view. I added a few fields to the view, and things slowed down. I tried
taking the fields out, but the results are the same. (The adding of the
three fields may have been coincidental to something else that may have
caused the slowdown.)
Either way, the view is still fast in an ADP file, but is slow as molasses
in its original MDB file.
That's good to know. One of the things I've been discussing here is that
this database is going to be rebuilt from the ground up (was converted to
Access from an old database program by a developer, then was modified by
another developer who didn't understand principles of normalized design,
then was inherited by me). I am leaning towards redoing it in MDB,
especially given the many testimonies of problems people have had with ADP.
But your input is certainly significant here as well. So that's good to
know.
Yeah, well, here the whole thing is in the back end. The whole idea is that
SQL Server does all the work and the MDB just has to get the data. But,
apparently, it has problems even with just that!
I didn't see a "FOR BROWSE" option in the SQL 7 BOL. Is that a SQL 2000
option?
Thanks,
Neil