Tables in Access frontend take more than 30 sec to open

M

MJ

Back end is SQL 2000 and for some processes we use Access as front end.
Something went wrong last couple weeks and it takes 30 sec to 2 min to open a
link tables or static tables from Access 2002-2003. Once it is open it
functions just fine. I have deleted the link tables and re-link again but it
didn't work. I have compacted & repaired it; it didn't solve the problem
either. This problem exists for all the preexisting access databases either
shared or not.

Only fix I found for this problem is that creating a new access mdb, re-link
the link tables and import the static ones to the new mdb. This way it works
just fine. It takes only one or two second to open the tables.

Any ideas or suggestions why such a problem occurs and what is best way to
handle it?

Thanks,
MJ
 
M

[MVP] S.Clark

Not uncommon, especially if more than 100,000 records.

Try NOT to open the tables directly. Use a query to open the table. Limit
the number of fields in the query. Use specific criteria to obtain the
smallest number of records.
 
A

Albert D. Kallal

Back end is SQL 2000 and for some processes we use Access as front end.
Something went wrong last couple weeks and it takes 30 sec to 2 min to
open a
link tables or static tables from Access 2002-2003.
Click to expand...

You seem to be un-clear here by static tables, and in the same sentence
mention sql server 2000?

Are you saying that sql server 2000 is slow in this case, and is it slow for
all applications working with the server, or not?

Have you talked to the admins running the sql server, and what changes have
been made here? Has the database server been upgraded, or perhaps some
permissions or something else changed on the sql server database? Has
perhaps the logging, or transaction systems been changed?

There is a LOT of issues with sql server, and you are likely in the wrong
newsgroup.

However, if you are talking about applications that don't use sql server
2000 at all, and they are slow, then there is a number of things to check:
Only fix I found for this problem is that creating a new access mdb,
re-link
the link tables and import the static ones to the new mdb. This way it
works
just fine. It takes only one or two second to open the tables.
Click to expand...

The above is often the result of track-autoname correct. You should turn it
off.

It sounds like you do have a split arrange, but do not that you DO NOT want
to allow multiple users into the same frond end. That means EACH USER gets a
copy of the front end on their PCs. Further, you should consider
distributing a mde, as that again prevents many problems.

The other thing here to try is a persistent connection. That means your
database opens a table (in the back end) during startup (this only apples to
a back end shared mdb..and does not apply to sql server back ends).

9 out 10 times, the above changes (track - name, and keeping persistent
connection) two fill fix your issues.

For the definitive list of the above, and more things to check, take a look
here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;162701
 
Top