Er, your accusations about Acquia here are both incorrect and offensive.
For the record, Acquia was only marginally involved in getting the new database API into Drupal 7 in the first place (probably 95% or more of the work and technical direction for this particular feature was contributed by non-Acquians). Also, given that we (Acquia) own a MySQL-based Drupal website hosting business (and don't really have MSSQL or Oracle expertise on staff so it's certainly not our preference to have clients who want to run Drupal on those systems), the implication that this was some kind of Acquia-directed conspiracy really makes no sense.
Also, the primary motivation of the new database API was certainly not to support these proprietary database systems anyway. Other databases whose code is free (especially SQLite, which is now supported directly in Drupal 7 core) are much more important. And cross-database support is definitely not the only useful feature of the new database API, just one of them. I would suggest reading e.g. http://www.garfieldtech.com/blog/database-tng-lands for more information.
Regarding CCK, it's hard to understand why you are so concerned about features being "locked" in Drupal core for the next few years? For one thing, they're not locked (certain features might still be added to Drupal 7 if they don't change things in a way that will break existing code), but more important, the entire way Drupal works is that core functionality can easily be extended by contrib modules to meet additional needs. As described in the link I provided earlier, the functionality you're looking for is apparently being implemented by the Field Collection module in Drupal 7 (and due to the Field API, it's being done in a way that knowledgeable people seem to agree is much better than any implementation that would be possible with CCK in Drupal 6). So it's not really clear why are you are apparently comfortable typing "drush dl cck" to install CCK for Drupal 6, but not comfortable typing "drush dl field_collection" to install this (or other) modules you might need for Drupal 7.
1. It's not clear what you're referring to by "multi-field support", but if it's something that's only available in the 3.x branch of CCK for Drupal 6, note that that branch is not stable anyway (only has an alpha release and should not be used on production sites). The 2.x branch is the one that's stable.
Perhaps you're talking about http://drupal.org/node/695636, in which case you'll see that the new Field API allows that feature to be implemented in a much cleaner way in Drupal 7, and there's already a contributed module for Drupal 7 being worked on to do it (which only has a beta release itself, but appears on the surface to be further along than the equivalent Drupal 6 CCK code).
2. Your comment about the database API is incorrect. Simple one-line select queries do not need to use the new OO syntax (and in fact, should not). See http://drupal.org/node/310072.
As for queries that do need it, it seems like you've missed the point; it's not "added complexity with no win attached", but rather the purpose is to support things like cross-database compatibility and access control. Sure, it was simpler in Drupal 6, as long as you didn't mind that your code might not work at all on certain websites :) If you're writing custom (site-specific) code for Drupal 7 and still don't care about that, you can likely use the simpler syntax for more things than it is intended for (although it's obviously still not recommended).
For the record, Acquia was only marginally involved in getting the new database API into Drupal 7 in the first place (probably 95% or more of the work and technical direction for this particular feature was contributed by non-Acquians). Also, given that we (Acquia) own a MySQL-based Drupal website hosting business (and don't really have MSSQL or Oracle expertise on staff so it's certainly not our preference to have clients who want to run Drupal on those systems), the implication that this was some kind of Acquia-directed conspiracy really makes no sense.
Also, the primary motivation of the new database API was certainly not to support these proprietary database systems anyway. Other databases whose code is free (especially SQLite, which is now supported directly in Drupal 7 core) are much more important. And cross-database support is definitely not the only useful feature of the new database API, just one of them. I would suggest reading e.g. http://www.garfieldtech.com/blog/database-tng-lands for more information.
Regarding CCK, it's hard to understand why you are so concerned about features being "locked" in Drupal core for the next few years? For one thing, they're not locked (certain features might still be added to Drupal 7 if they don't change things in a way that will break existing code), but more important, the entire way Drupal works is that core functionality can easily be extended by contrib modules to meet additional needs. As described in the link I provided earlier, the functionality you're looking for is apparently being implemented by the Field Collection module in Drupal 7 (and due to the Field API, it's being done in a way that knowledgeable people seem to agree is much better than any implementation that would be possible with CCK in Drupal 6). So it's not really clear why are you are apparently comfortable typing "drush dl cck" to install CCK for Drupal 6, but not comfortable typing "drush dl field_collection" to install this (or other) modules you might need for Drupal 7.