2

A client of mine reported troubles on one of their computers as freezing when it loads the database. This database IS THE application for this 24/7 non-profit agency. Since everything else checked out on the computer I looked at the database file. Is is an Access 2000 stored on a network share; 1 file; just a shade under 1 GB in size. No wonder it takes so long for this computer to load it (it is the oldest computer, only has a 10Mbps NIC).

This database is complex, likely full of macros and code to make it work properly, but I'm not sure how it was put together, and I'm not sure I could re-create it if I tried. I am NOT a database guy, but can do some simple things.

That said : Is there a SIMPLE thing I can do do make this database work better? The users are primarily social workers who need a "Click this button and everything will be the same as it was before" type solution. That said, if that button opens up Internet Explorer instead of Access, they will be okay with that.

They have a Windows 2008 server. I am the contract technician for this agency and can add any services to the server to make it work - subject of course to budgetary approval. The database is becoming a problem, and a simple solution is likely going to be a lot cheaper than hiring a Database guru to rebuild it from scratch.

Although the Access database is in 2000 format, the workstations are all running Access 2007, so upgrading to 2007 before doing anything else is an option. Of course I will be doing backups before attempting ANYTHING.

Calling the guy who wrote it is a non starter. No one here now knows who did it originally.

Edit to add: Up to about 6 or 7 people could be using the DB at any time. Not all actively, but it would be open and minimized on their workstation

Geldhart
  • 43
  • 1
  • 7
  • 1
    There is a compact/repair database option on the main access menu - that might help. Compact could reduce the size of the database. It's under Tools > Database > Compact/Repair if I remember correctly – cardmagik Feb 22 '13 at 19:46
  • 1
    Access 2007 / 2010 compact and repair, split front and back end, decompile -- have you done most of this? Back-up first, of course. – Fionnuala Feb 22 '13 at 19:46
  • I will try the compact option. I haven't done anything yet, I saw the problem this morning and haven't done anything. – Geldhart Feb 22 '13 at 23:31
  • As for splitting front and back end and decompile, I'm hoping this is something that is relatively automated. As I say, I'm not a database person. Of course, I will backup prior to doing anything. – Geldhart Feb 22 '13 at 23:32
  • 1
    Decompile with references: http://stackoverflow.com/questions/574587/debug-a-bad-dll-calling-convention-error-in-msaccess-vba-code/683399#683399. Splitting the database into data on the server and forms, code, reports etc on each user's PC will make the biggest difference, but it will take a little work. There is a walk-through, which I have not tried, here : http://office.microsoft.com/en-ie/access-help/split-an-access-database-HA010342026.aspx – Fionnuala Feb 22 '13 at 23:37
  • Even if you're a database person, Access is a little different. Compacting the database is something that can be done automatically - if not turned on, every change to the database is ADDED on instead of updated in place. Compacting cleans up the trash and it is amazing how small the database can become. If there's not a lot of data in the database, I would suspect doing this one thing will help. Do make a backup copy of the database first though using Windows. – cardmagik Feb 23 '13 at 01:20

2 Answers2

2

And to further make suggestions about this being a database system, the learning curve required to work and modify an Access database is NOT any different than an application based around vb.net or SQL server. If you going to performance tweak an Oracle system, you going to need someone with better than average Oracle skills. This thinking also applies to Access, or a vb.net application.

In MANY ways I found the learning curve for Access FAR steeper than using VS and .net. and especially in those cases where additional performance is required.

Access requires multiple skill sets ranging from coding to database design skills to tune performance. Don't be fooled here because this is Access. And worse you are talking about performance turning so now you talking higher end skills here.

You best find a real Access pro, since that person can do in a few hours what will take you weeks to learn.

And if there was a simple magic bullet solution, it likely would have been done already.

In most cases when an application is valuable, then it means it likely was created by someone with at least some good levels of ability. So this suggests this application likely has some good value and designs here.

You can however go over some of the basic checklists that all Access developers are used to. The first of course is to split the database (note that this often will not speed up the application, but it will increase stability and reliability). And if the application is not split then often this suggests the builder was less than optimal.

Next up is some basic other tips now that the application is split:

Always deploy to users a compiled version of the program.

If multiple users – check + ensure a persistent connection is maintained to the back end.

Turn off track name autocorrect.

The above steps, and a list of things to check are here:

http://www.granite.ab.ca/access/performancefaq.htm

The above is THE DEFINITIVE list of things to check without having to modify the application in any real material way.

However, at the end of the day, your best bet is to bring someone in with competent Access skills. And if they don't have budgets for this, then perhaps you find someone willing to volunteer some of their time. (after all, that is what we are doing here).

As I noted, often Access takes MORE loving care and is in many way LESS forgiving then if you have a .net or say oracle based solution.

And while I gave a link to a list of suggested approaches, you do want to exercise caution poking around and messing with a complex application – such applications require more caution since it easier to break or damage something.

However, do take a gander of the above list of things to try – it is a really great list.

And if you are on 10baseT, then jumping to 100BaseT will significant help. In fact it been years and years since I seen a 10baseT connection? (are you sure about this?).

I mean, they have 2008 server (that is costly), they have Access 2007, again rather recent, but how on earth are they using a 10baseT network card? I mean, what $10 for a 100baseT NIC card? I would most certainly get rid of that bottle neck.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • And if there was a simple magic bullet solution, it likely would have been done already. – Geldhart Feb 24 '13 at 14:55
  • We cannot assume that the person who created it was any sort of expert - likely a skilled amateur. Also, we cannot assume the "magic bullet" has been done; no one's done anything other than just use the database. I did confirmed the 10BaseT (looked up the specs on that computer), but that's the least of my issues, that computer is scheduled to be replaced anyway once the new budget cycle starts. The server was donated -- recycled from a local business that no longer needed it. My concern is that if nothing is done, it will continue to be a problem - the 100Mbps are still a bit slow right now – Geldhart Feb 24 '13 at 15:02
  • I completed the compaction today and was shocked -- less than 5 minutes and the db is now only 45mb. Is that normal? – Geldhart Mar 01 '13 at 00:43
  • Is there way to automate this process? (As in compact once a month without me having to remote in and do it?) – Geldhart Mar 01 '13 at 00:44
  • @Geldhart There is an option in Access to compact the database on closing it - I'm not sure about the Access 2000 so you'll need to search for it - it should be somewhere around the file menu - current database options – cardmagik Mar 02 '13 at 16:24
0

Thanks guys. After compacting the database, performance is much better on loading the DB. While I won't be redoing the db myself, at least my client can stop (the justified) gripes about how quickly it loads on the workstations, even the 10BaseT dinosaur (the NIC is a replacement card from before my time there, their old guy apparently was known to use a bunch of used parts).

We will look at splitting the database later, but they understand that's a "safety" feature, not a performance one.

Thanks for all the help on this one.

Geldhart
  • 43
  • 1
  • 7
  • 1
    Splitting the db IS a performance bonus, specially when you store the front-end on the client (=end user local) machine. – iDevlop Mar 06 '13 at 14:16