This utility started because of a project I was working on, that had several large lookup tables, that were semi-static, that means that they are not changed very often, but they can actually be updated when the user wish to add something. Until this I was using almost always linked tables, but this time the lookup tables were really large and the network as a bit slow. So I started thinking in a way to make this lookup tables local, this would increase performance and reduce network traffic.
But I had the problem of maintaining the data on the local, so I developed this Class, that will handle all the Insert's/Update's and deletes on the back end and
synchronize them with the front end, this mean that in the maintaining the data this class will be slower than linked tables, but then on the filling of Combo's and
List box's, this will boost your GUI.
This solution has some limitations, the first is that the only tested backen is JET, second all lookup tables must be on the most common format of Identification/description, third you have to implement your own referential
integrity functions. Some other things that I'm not remembering at the moment.
I'm providing a GUI for the Class, but you can use your own.
This work in the following manner, you have the lookup tables both in the front end and the back end, there are two more field's in each lookup table, the SystemLUT (the date/time the record was updated) and SystemState (indicating if the record is active or deleted). Two more tables are required that contain some information about the lookup tables that will be used in the application, one is located on the front end (tblLocalLUT), and another located on the back end and likend to the front end. This two tables control the date/time that each lookup table was updated, they to that with the field tlkpLUT. on the tblLocalLUT there are also other field that make some description about that particular lookup table.
Remember that this is a Beta release.
I'm using a function from the Access Developer's Handbook (Sybex) I have authorized permission from the author to use this module. If you still don't have this book I urge you to go at this moment and buy a copy of it right now !
I also want to thank Lyle Fairfield, Michael Kaplan, Stephen Lebans, Rui Lourenço.
© 2000, Pedro Gil. All
Please contact Pedro Gil for any additional questions.