A more sophisticated way of importing a database

The current Wessex Premier has a very simple way of checking a database to import - if it's not called "V3.mdb" it won't be imported. Now this works at a very simple level, but it leaves two problems

The first is that all the backed up files have to be called "V3.mdb" (rather than anything more convenient like say "ShopBackup_16_6_09.mdb"). You can get round this by renaming the file after exporting and back again before importing, but this is an awkward and fiddly way of dodging the issue.

The second problem is that although the file might be called "V3.mdb" the internal structure of the database (called the "schema") could have been changed and this would make it incompatible with the program.

So, is there a way of checking the schema of each table against a master schema-file?

There is, of course, otherwise I wouldn't be writing this!

This first thing to do is to extract the schema from the file to be imported. The crucial line of code is this -

dstSchema.Tables("V3Schema").WriteXmlSchema(xmlWriter)

Which writes the schema to a temporary (xml file) to compare against master xml schema.

Then the temporary file is then run (byte by byte) with the master file and each byte is compared, if they don't match then the file to be imported is rejected. The heart of the code is this -

Do

MasterByte = MasterStream.ReadByte 'master table

CheckByte = CheckStream.ReadByte 'table to be imported

If MasterByte <> CheckByte Then

blnOk = False

Exit Do

End If

Loop While MasterByte <> -1

This is done for each of the four tables in the program's database, as long as the file-to-be-imported passes the test it can be imported (its name is changed to "V3" for internal use).

Obviously there is a lot more code to set up the above lines - checking that the files exist, that the user hasn't pressed "Cancel" as so on - but in testing I haven't yet come across any problems, so it looks like this feature will be in Wessex Premier II.

Sneak preview

"So, what are you working on now?" is a question I've often been asked at the Spring Fair.

The answer is a completely rewritten Wessex Premier. The basic idea being 2 programs in 1 - a basic frame pricing version which uses just the mouldings part of the database, but no worktickets or customers list. This could be upgraded to the full version when the user needs to - using the same database that has been built up. In this way one could get use to using the program just to give prices, and because it works in exactly the same way as the full version it will be much more straightforward to upgrade - you wouldn't even need another disk.


Another aspect I'm working on is improving the handling of database records, particularly worktickets, in the full version. At the moment in WPP3 the lists of work to be done, low moulding stock etc. are separate from the records themselves (ie. you have to close down the list having made a note of the record, then go to the record itself. Not very efficient.) Now you will be able to look at the list, double-click on the line of interest and the individual record will come up. Also, visually, the workticket list is easier to read being in name order with the Overdue, For completion and Completed jobs in different colours.

The third big change is adding mountboards to the database. The idea behind this is to record the mountboard colour in the database and then print a spot of that colour on the workticket. This way mistakes can be minimised (for instance Aqadia 8643 in quite a different colour to 8043).

To make everything even more foolproof when the list of mount colours comes up each line is the colour of the mountboard. It was an interesting piece of programming as the lettering (normally black) has to change depending on the lightness/darkness of each colour.

Now I'm at the stage of "tweaking" the program, for instance - if when you click on a workticket, do you want all the other worktickets on that same invoice to appear, or does that just complicate the process? Is putting the mount colours on the workticket actually an advantage? Only by using the program for real do these thing emerge.

There is a technical price to be paid however. Whereas the current Wessex Premier (WPP3) uses VB2003 with the .Net Framework 1.1 (a quick install). This new program is written in VB2008 and uses the .Net Framework 2.0 ( a much longer install), which is needed because the program has to shut down and restart (when switching between versions) and also for the new menu controls.

Another reason for going with the new program is better integration with Windows Vista & Windows 7 (here at the end of October). While at the moment most people use XP, as computers get changed and upgraded those using XP will get less and less (in fact Microsoft plan to stop supporting XP completely in 2014).

Will the new program see the light of day? We'll have to see!