Database locking with linked tables

[Guest]

I have an Access database which is used by one user. The database is stored
on a shared file server. The database is structured with an application
database containing all the queries, forms etc. and a data database
containing all the data. I have other databases and excel sheets which link
to the data in the database for read only purposes.

My problem is that I sometimes get database locks that I do not know the
reason and I am therefore unable to resolve. These locks prevent me
compacting the database. A database compact is part of my daily routine.

I have configured the Excel (MS Query) data source not to keep the
connection open until MS Query is closed. Is there a way of making linked
tables read only so they do not lock my database?

 

[Guest]

Actually there is no good reason to compact a database daily or even weekly.
Maybe once a month. If you compact the database and run a few of the normal
queries, forms, and reports, the database just balloons up to about the same
size. This is what it needs to work.

Now if the databases uses a lot of temp tables and such, then again
compacting isn't really necessary. Maybe a look at the design of the database
but not compacting.

If you are getting near the 2GB file size limit, then maybe you need to
split some tables off to another BE; archive some data (bad idea); or upgrade
to SQL Server.