I am in the process of rewriting a large motor vehicle related application (currently in DOS) in VB6 using SQL Server as the database.

I have opted for the <n> tier approach as follows…
1) The Client UI
2) Middle tier (business rules)
3) Server object (.dll or ActiveX.exe)
4) The database

Data will be passed back and forth from the server object, via the middle tier, to the client. Each table in the database basically has its own .dll (in the middle tier) to Load and Save data etc. These .dll’s generally contain three classes i.e. a public server class doing the loads and saves, a class holding column data for each row in the table and a collection class that holds each row (class) in the table. It is the collection object that is passed to the client for updating. This object is then returned to the server class which saves the updates. So, I have now ended up with around 100 .dlls of this type servicing the client UI. I am hoping to install all of these (plus the server object) on the server pc and just the UI on the clients. This is the first project of this size and complexity for me. Therefore, has anyone any experience and/or comments of this configuration regarding performance, practicality and installation techniques etc.

Also, as far as the server object is concerned, I am thinking of an ActiveX.exe which will show a list of user login names and times etc. as well as providing the connections to the database called from the above .dlls. Any thoughts?

Remember there are lots of routes to the same destination. Basically what I understood is that you want a thin client. Personally I find having a dll for every table a hassle for maintenance. I would do this instead.

1) The Client UI
2) Middle-tier (business-rules)
3) Server / Database

The middle-tier would be the go-between your client and server. The middle tier would only execute stored-procedures and have access to views (never the tables directly). i.e. The client needs to delete a record, it would send the delete command to the middle-tier, the middle-tier would call a delete stored-procedure on the server. The server would return a success or fail to the middle-tier and the middle tier would send that to the client. This method is good for maintenance and security. And changes in the future would be done directly to the stored-procedures and can be made transparent to the client.

However this is just one idea there are lots of different methods and techniques to consider.

Thanks for that

i'm using the three-tier approach when dealing with such complex app development (SCIROCCO is right, this is for the benefit of easy maintenance and security). three-tier for me is: (1)the client or GUI tier, (2) the middle or business tier, (3) the data tier. i chose this three-tier approach coz of the following: (this is based on my developing experience)

* applications you developed are easier to deply especially when you have to scale your application for use on the Web.
*business layer can ensure the security of data in a standard manner as well as the stableness of the application.
*by including or should i say encapsulating the business rules in the middle tier, the application that access these rules can make use of shared components (active-x dll or exe's).

this is just my thought.