then why this one is works fine ?as soon as removing the alias (countx) and the sub selectand it will works fine , but I need that subqueryOK , I will put in the bellow all the project hope you would notice something wrongI am using a DataGrid to view the result

Sorry, I'm stumped. Hopefully someone else will see this thread and contribute.

As to an alternative to ADODB, I remember using DAO with VB6 by setting the appropriate reference (Assuming Access 2007 or newer, it would be Microsoft Office x.0 Access database engine Object, Access 2003 or older, it would be Microsoft DAO 3.6)

And that isn't executing in an ADO recordset - as discussed in this thread. (With an error akin to: "method 'open' of object '_Recordset' failed")Sometimes, the error message is far from helpful in ADO. One thing to bear in mind under such circumstances is that even if a word isn't normally reserved in the SQL dialect through which you're querying, it might be via the provider in use in ADO.The Jet OLEDB provider differs and is definite more on the fussy side.

Although "Job" might seem potentially flag worthy, in this case it'll be Names. (I know. "Name" would be an obvious one. But this? Like I said... more fussy.)So try the above query as it is, but with the subquery very subtly changed to escape the use of Names. (Escaping achieved, as usual, by enclosing it in square brackets.)

Although "Job" might seem potentially flag worthy, in this case it'll be Names. (I know. "Name" would be an obvious one. But this? Like I said... more fussy.)So try the above query as it is, but with the subquery very subtly changed to escape the use of Names. (Escaping achieved, as usual, by enclosing it in square brackets.)

(SELECT DISTINCT [names], job FROM table1) AS countx

Cheers

I did just like you have said , but got the same errorhowever , I believe you are completely right

works.But when you copy and paste this one, it doesn't?SELECT Count(countx.job) - tblLocalSubtr1.SubtrValue AS employ_number, tblLocalSubtr1.JobName AS jobsFROM (SELECT DISTINCT [names], job FROM table1) AS countx RIGHT JOIN tblLocalSubtr1 ON countx.job = tblLocalSubtr1.JobNameGROUP BY tblLocalSubtr1.JobName, tblLocalSubtr1.SubtrValue

It's important to remember that ADO itself doesn't actually parse any SQL whatsoever. It's Thunderbird 2. You give it purpose by supplying a provider which slots right into its belly and off you go to save the world with the appropriate tool.In this case, the Jet OLEDB provider. Its parsing is causing an issue, but Jet itself absolutely supports subqueries.And so do ADO recordsets based on Jet. But there are differences where the OLEDB provider for Jet makes different demands. But subqueries in general isn't one of them.