Subselect, TableAdapter and MS-Access

[Guest]

1) I generated a Subselect query in MS-Access which works fine there:
SELECT (SELECT Name FROM Players WHERE Players.PlayerID =
PlannedGames.PlayerID) AS PlayerName, (SELECT Surname FROM Players WHERE
Players.PlayerID = PlannedGames.PlayerID) AS PlayerSurname, *
FROM PlannedGames

This Subselect is recommended in Scott Mitchel's excellent MSDN Article
"Creating a data access layer".

Then I cut&paste this select statement into the TableAdapter Configuration
Wizard.
This generates SQL parsing errors.

Does the ADO.NET 2.0 provider for MS-Access not support Subselects?

2) In general is there a comparison table which SQL-Server 2005 features /
..NET 2.0 classes are not available/working when using ADO.NET 2.0 and
MS-Access 2003 ?

thank you very much
herbert

 

[Paul Clement]

¤ 1) I generated a Subselect query in MS-Access which works fine there:
¤ SELECT (SELECT Name FROM Players WHERE Players.PlayerID =
¤ PlannedGames.PlayerID) AS PlayerName, (SELECT Surname FROM Players WHERE
¤ Players.PlayerID = PlannedGames.PlayerID) AS PlayerSurname, *
¤ FROM PlannedGames
¤
¤ This Subselect is recommended in Scott Mitchel's excellent MSDN Article
¤ "Creating a data access layer".
¤
¤ Then I cut&paste this select statement into the TableAdapter Configuration
¤ Wizard.
¤ This generates SQL parsing errors.
¤
¤ Does the ADO.NET 2.0 provider for MS-Access not support Subselects?
¤
¤ 2) In general is there a comparison table which SQL-Server 2005 features /
¤ .NET 2.0 classes are not available/working when using ADO.NET 2.0 and
¤ MS-Access 2003 ?

I would recommend avoiding the configuration wizard in this instance. Just copy the SQL text to the
CommandText property for the Fill method of the TableAdapter or the SelectCommand of TableAdapter
object.


Paul
~~~~
Microsoft MVP (Visual Basic)