CRecordSet from 1 or N Tables

[Applebee]

I guess what I want to know if there are pro/cons to creating a CRecordSet Object that is built on many tables of the datasource? or should I make one object for each table??

[PeterK]

Typically it is done by creating one CRecordset derived class per table.
Otherwise an implied JOIN (SQL command) takes place.

[John Holifield]

Generally, I find it easiest to use create a CRecordset derived class for every table in the database. But If you are going to use a JOIN, you'll probably find it more convienent to use a dynamically bound recordset. There are samples of dynamic recordsets on this site somewhere.

If you decide not to use a dynamically bound recordset, you can always derive from CRecordset and override GetDefaultSQL(). You also have to make sure that you declare and initialize all the correct members and RFX_ them appropriately in DoFieldExchange(). This is kind of a pain in the butt in my opinion, when dynamic recordsets are so much easier.

One other thing to consider also is that different DBMSs use different syntax for joins, and this may cause a problem if your app must run against various data sources.

Just My Two Cents...