How to Update Database Table using a COM Collection Object?

[antzzz]

I have an Employees table in MS Access. In Visual Basic, I created an Employee Class and put the properties the same as the fields in my table. I also created an Employees collection and put the Employee class under it. I'm using the Class Builder utility and so I already have the Add, Remove, etc. created automtically for me. I would like my Visual Basic .EXE application to do normal database operations (add,delete,update,etc.) using the Employees collection object. But I also want to hide the database access functionality from this application. Now that I have the Employees Collection object, where do I go from here and where do I do the database operations? Do I need to create another class to handle database operations and how do I link my Employees collection object to these database operations? Do I embed the database functionality in the Employees collection object itself (eg in the Add method)? What's the best and proper way to go about it? Please help. Thanks!!

 

[BigDaddy]

Antzzz,
Yes, you will want, not necessarily need, to do this for scalability, but the cost is complexity. On the plus side is portability of interface and database, scale of users, plug and play code maintenance (even more so with VS.NET coming), proper location of responsibility (each object is asked to do it's job and only it's job).

There is a great book for building an application with this kind of architecture. It is a WROX Press book written by Rockford Lhotka, Business Objects. The book was written for VB 5.0 but the theory is very sound. The goal of the book is to teach how to write a true distributed n-tier application. By this I mean, writing classes that handle your business logic, database persist and leaving your interface to only deal with how to display the information to the user. This makes your business logic easily portable to many different interfaces (Access, VB, web,...), and your persist layer (database) portable also. You could move your database from a MS Access 2000 database to a SQL Server database with one quick code change. This does not require any modifications to the Business Logic or the User Interface levels.

The trick is for your interface project to reference the business logic project which in turn references your data persist project. Your business logic layer keeps track of its state (IsDirty, IsNew, IsDeleted) and at the appropriate time your database gets updated to reflect these changes.

So for your project:
You would build your colEmployees Collection made up of a group of clsEmployee class objects. To these you would add your properties (IsDirty, IsNew, IsDeleted) and any data fields that your application tracks, these would be written to update when the business object is editted.

Now the business objects should be written in a way to allow deletes, updates or adds, but as to how these are accomplished, your business objects don't care. They are only responsible for notifying the Persist objects of what should be done, the how is left to the Persist.

For an example of this idea, I can e-mail you parts of one of my projects, but I think the book would explain it better. Within the sample I would also show how the data is marshalled from Data Persist Objects to Business Objects to User Interface and back again. This idea is a stateless environment, disconnected might be a better term for the idea. The connections to the data base are made only when needed.

Good luck. Sorry for the length of explaination.