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!)