Comments requested on VB4 OLE2 client/server architecture with multiple RDBMS

We are developing a number of PC based Windows client/server applications using VB4 over the coming months. We are cutting our teeth on a corporate wide, multi-site telephone directory serving approximately 50 sites with 200 users at each site. We intend to develop applications based upon a three tier client/server model using OLE2 as the interface mechanism between the layers and the OLE2 Automation Server capabilities of VB4. Our aim is to provide completed applications which are composed of reusable OLE2 Automation Server 'objects'.

Our three tier model is as follows:

        PRESENTATION LAYER - VB4 Win3.x/Win'95 form based GUI

        (OLE2 interface)

        BUSINESS OBJECT LAYER - for internal processing of queries

        (OLE2 interface)

        DATA ACCESS LAYER - to access data from an RDBMS

We are aiming at a 32 bit client platform (Win'95 and NT Workstation) but may require a 16 bit version of our applications for some sites still using Win3.x clients.

The database is likely to change during the lifetime of the application (development in Access, initial rollout using Sybase, possible change to Oracle and/or SQL Server). Due to the multi-site nature of the company the application will need to configure itself to talk to the RDBMS currently in use at the site in which it is installed. We need to ensure that the application itself is insulated from any back-end database changes as much as possible.

We have considered a number of options and using the VB4 DAO mechanism to pass ANSI standard SQL queries (created in the 'Data Access Layer') to the database currently appears the best. The database location could then be stored in an INI file and we believe this is all that we need to change in order to direct the application to a new database.

Other methods considered were:

• Passing ANSI standard SQL queries thru ODBC to the RDBMS

This appears to be more flexible but may not give us access to some of the more advanced features available via DAO.

• Triggering SQL stored procedures in the RDBMS

This would be more efficient in terms of performance but would not be completely transparent i.e. if the database format changed then equivalent SPs would need to be created on the new DB.

• Using Remote Data Objects on the server

This may be efficient in terms of network traffic but may cause performance problems if a large number of clients attempt to access data simultaneously.

• Use of proprietary interfaces for each RDBMS (e.g. Oracle Objects)

May provide performance advantages but would require additional development effort to switch between databases.

Any comments/opinions related to the above (including any direct experience of likely performance issues) would be much appreciated.

TIA Derek Cummings
Derek Cummings
Leeds, UK

EMail: derek_at_dac1.demon.co.uk Received on Sat Jan 27 1996 - 00:00:00 CET