A Database tweak

Fiddly things databases - just when you think you have everything just so, something else comes along to upset the balance.

I recently came across one such hiccup (or a least potential hiccup) in the "Mouldings" table of the Wessex Premier database.

The field "Supplier No" is structured not to allow duplicates. Now 99.9% of the time this will make no difference at all, in fact it can be very useful as it means that the mouldings which are duplicated between Wessex & Frinton cannot be duplicated in the program's database records. However, it could be that 2 different suppliers use the same number, or the user may want to show both the Wessex and the Frinton records.

So, how to solve the problem? I tried various programmatic solutions, but none worked consistently. So, given that most users probably wouldn't want to change the database file, what's the answer for those that do?

You'll need to use Microsoft Access (I tried this on Open Office Base, but no joy).

First of all "Export" (copy) the database file (called "V3.mdb") to a suitable location, as it's always best to work on a copy rather than the master file.

Double-clicking the file will open it in Access (assuming that is your default database program). On the left hand side you'll see a list of the tables ("Customers", "WorkTickets" and so on) Double-click the "Mouldings" table to open it. A chart with all the records will be displayed.

In the top left just above the table list is "Views", click this and select "Design View". The main window will now show a list of the fields in the Mouldings table and their properties. (see below)

Select "Supplier No". Below is a list of the properties for that field.

Select "Indexed" and change "Yes, (No Duplicates)" to "Yes, (Duplicates OK)".

"Save" the changes and you're done, all that remains is to "Import" the modified file back into Wessex Premier.

Points to note -

You should regard this "tweak" as one way only, because, once you have added a record with the same supplier number as another record, Access will not allow you to change back.

You change any of the other properties at your peril!

The above screenshot was from Office 2007, be sure, however, to save the database as an Access 2000/2003 file (ie. a .mdb file not a .mdbx file).

The steps in Office 2003 look different, but are basically the same. (If that's not too double-dutch!)

Backup or C***up?

As you continue to use Wessex Premier you accumulate data in the form of database records. Because we're using this program in the real world these records have a real importance to our businesses. In fact we would definitely have problems if the records suddenly disappeared. At the very least we would have to re-input all the moulding records and as for reconstructing the work ticket records - well, it doesn't bear imagining!

When the program was originally released there was the usual menu option to copy the database file to somewhere and a recommendation in the Help section to do this at least once a week. With 20/20 hindsight this was hugely optimistic.

Although computers and more specifically hard drives have become far more reliable over the years - them can still fail suddenly. In fact many take the view that once a hard drive is 4-5 years old you are on borrowed time.

So, what's the answer?
Of course, there are lots of answers (you knew that was coming didn't you?) and the trick is to find one that suits you.

In the (good?) old days of MSDOS you just got a bunch of floppy disks and copied the whole system onto them. With the coming of Windows that became unrealistic and just backing up (or not) your documents became the norm. With the greater reliability it was (is) very easy to let things slide and not take any precautions at all. Of course, as in other areas of life - take no precautions and you'll eventually get caught out!

After a couple of phone calls from framers telling me their computers had died, was there any way to get back their data? And no, they hadn't taken any copies! Well, I started to wonder how to lessen the worry and let the computer do the work.
The answer I came up with was to get the program to copy the database file to a specified location every time it was shut down (in most framing shops this means at the end of each day). Simple, eh? Of course it requires a bit of thought as to where the file is to be copied. The easiest being a USB flash drive permanently plugged in, or else a second hard drive in the computer (a network place should also be possible, though I haven't tried it). What you don't want to do is to copy the file to the same hard drive, because if that fails it takes your backed up file down too.

There are some points to beware of however, one is that USB flash drives can and do fail - so make sure you have a spare. The other is bit more complicated - suppose for some reason the database file you are using with the program becomes corrupted, when you shut the program down the corrupted database will over-write the previously saved good file. Not a good idea, - so if you suspect your database is not right then the correct action is to copy the backed-up file somewhere else before you shut the program down.

So much for protecting the data produced by Wessex Premier, but what about all the other important documents, photos, movies etc. that accumulate on your computer? Well, the obvious thing to do is copy them to an external hard drive (which are pretty cheap nowadays) or use another solution that I've been impressed with - namely backup to an external server. I've used Humyo (www.humyo.com) and the software they provide (so if you add or make changes to your documents these are uploaded to their servers straight away). This way, if the house goes up in flames it won't mean the lose of years of accounts, letters, pictures and so on. Another bonus is that I can access the account from any internet-connected computer - surprisingly useful.