[AccessD] Another database from hell

Mark,

A standard technique to improve performance is to set a database variable in
the FE to the BE file on opening (Dim dbsOpening As DAO.Database).  Let the
database variable close when the FE closes.

Go to www.everythingaccess.com to look at vbWatchdog.  You pay once (use
forever) for a set of classes that will record all errors in a database
without typing error handling code in every procedure.  They have a free
trial.  The Enterprise edition VariablesInspector feature is quite helpful.
I've used this for a couple of years now and it's never been the cause of
any problems.  This might give you good info on what's going wrong.

Also, I've made a utility to quickly export all the objects to text and then
import them back (except tables).  This can help eliminate issues with
p-code or find a corrupted object.  Let me know if you'd like to try it.

Good Luck, 
Dan


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Breen
Sent: Saturday, January 07, 2012 12:32 PM
To: Access Developers discussion and problem solving; Discussion of Hardware
and Software issues
Subject: [AccessD] Another database from hell

Hello AccessD Friends,

I have a new client and my task is to replace an aging Access database with
a DotNetNuke based system.

Because the Access db is due for early retirement, neither I nor the client
wish to invest any time or effort in re-programming.  I am prepared to
perform some minor improvements, but on the basis that it is working as is
for a number of years, I do not want to do too much work on it.

The reason I am seeking help from AccessD is that the Access App is loosing
connection with the network a few times per day.  The message the users get
is

*"Your network access was interrupted.  To continue, close the database and
then open it again.    You cannot save the record at this time"*

Now the scary bit,
1) the db is 120 MB when fully compacted but quickly grows back to 300MB.
2) There are 12 to 15 simultaneous users.
3) Each user always opens two instances of MS Access so that they can keep
two different screens open at all times.
4) It does not help if I configure a BE and an FE or simply include all
tables in the one mdb file.
5) They do not currently operate with an FE on the local machine (I am
considering moving FE's locally to test that).
6) All PCs are slow and old
7) Error handling and best practices are absolutely non-existent
8) Some PCs run Access 2007 and some run 2003.

The db files are stored on a Windows 2008 R2 Server, but the users think
they had better performance when it was hosted on Windows XP machines, I am
not sure if that is correct but can check.

Any suggestions ??

My advice to someone in my position would be to simply not touch the Access
App and focus on the new app, but if I could think of a quick win, I would
try it to simply prevent the error mentioned above.

Thanks for any advice and I will understand if you have non at all :)

Mark
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com