UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Adodb Subquery Error    
 
   
Sailor
post Feb 3 2017, 12:14 AM
Post#1



Posts: 38
Joined: 14-December 16



I am using ADODB and VB6 , the following query is works fine with open method
CODE
"SELECT sub.want-Count(table1.job) AS totals,Count(table1.job) as totx,sub.JobName AS jobs " & _
            "FROM table1 " & _
            "RIGHT JOIN sub ON sub.JobName=table1.job " & _
            "GROUP BY sub.JobName , sub.want; "


but when trying to use a sub query as follows the error occurs :
CODE
"SELECT sub.want-Count(countx.job) AS totals,Count(countx.job) as totx,totx/sub.want as ratios, sub.JobName AS jobs " & _
                                "FROM (SELECT DISTINCT names,job,employ_type,in_rest FROM table1 WHERE employ_type='full' and in_rest=0) AS countx " & _
                                "RIGHT JOIN sub ON sub.JobName=countx.job " & _
                                "GROUP BY sub.JobName , sub.want; "


the error message is :

run-time error '2147467259 (80004005)':
method 'open' of object '_Recordset' failed
what's the wrong frown.gif
This post has been edited by Sailor: Feb 3 2017, 12:19 AM
Go to the top of the page
 
Doug Steele
post Feb 3 2017, 08:49 AM
Post#2


UtterAccess VIP
Posts: 21,445
Joined: 8-January 07
From: St. Catharines, ON (Canada)


totx/sub.want as ratios looks invalid.

If you're trying to use the calculated totx field, try repeating the calculation in the SQL.

--------------------
Go to the top of the page
 
Sailor
post Feb 3 2017, 12:27 PM
Post#3



Posts: 38
Joined: 14-December 16



thanks Doug for the reply
I am afraid it's not the problem
even with the follows I get the same error :
CODE
"SELECT sub.want-Count(table1.job) AS totals,Count(table1.job) as totx,sub.JobName AS jobs " & _
                                "FROM (SELECT DISTINCT names,job FROM table1) AS countx " & _
                                "RIGHT JOIN sub ON sub.JobName=table1.job " & _
                                "GROUP BY sub.JobName , sub.want; "


I have forgot to explain my tables , they are as bellow :
table1 ,contains the fields (names = text , job = text)

sub , contains the fields (JobName = text , want = long number)
This post has been edited by Sailor: Feb 3 2017, 12:31 PM
Go to the top of the page
 
Doug Steele
post Feb 3 2017, 01:26 PM
Post#4


UtterAccess VIP
Posts: 21,445
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Sorry: didn't look closely enough at your SQL. blush.gif

Because you've aliased the SELECT inside your query as countx, you cannot refer to table1. The rest of the query knows nothing about Table1. Try:

CODE
"SELECT sub.want-Count(countx.job) AS totals,Count(countx.job) as totx,sub.JobName AS jobs " & _
                                "FROM (SELECT DISTINCT names,job FROM table1) AS countx " & _
                                "RIGHT JOIN sub ON sub.JobName=countx.job " & _
                                "GROUP BY sub.JobName , sub.want; "

--------------------
Go to the top of the page
 
Sailor
post Feb 3 2017, 01:57 PM
Post#5



Posts: 38
Joined: 14-December 16



CODE
"SELECT sub.want-Count(countx.job) AS totals,Count(countx.job) as totx,sub.JobName AS jobs " & _
                                "FROM (SELECT DISTINCT names,job FROM table1) AS countx " & _
                                "RIGHT JOIN sub ON sub.JobName=countx.job " & _
                                "GROUP BY sub.JobName , sub.want; "


I have tried that before it's ok in ms access , but in ADODB , it gives the error that I've described in my 1'st post
This post has been edited by Sailor: Feb 3 2017, 02:51 PM
Go to the top of the page
 
Doug Steele
post Feb 3 2017, 03:53 PM
Post#6


UtterAccess VIP
Posts: 21,445
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Are you sure it's the query that's causing the error? Might there be a problem with your code opening the recordset?

--------------------
Go to the top of the page
 
Sailor
post Feb 4 2017, 02:36 AM
Post#7



Posts: 38
Joined: 14-December 16



CODE
"SELECT sub.want-Count(table1.job) AS totals,Count(table1.job) as totx,sub.JobName AS jobs " & _
            "FROM table1 " & _
            "RIGHT JOIN sub ON sub.JobName=table1.job " & _
            "GROUP BY sub.JobName , sub.want; "


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

this is the entire project with error of course :

CODE
Private Sub Form_Load()
    Dim db As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim str As String
    Set db = New ADODB.Connection
    db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        App.Path & "\" & "yy.mdb;Mode=Read|Write"
        
    str = "SELECT sub.want-Count(countx.job) AS totals,Count(countx.job) as totx,sub.JobName AS jobs " & _
                                "FROM (SELECT DISTINCT names,job FROM table1) AS countx " & _
                                "RIGHT JOIN sub ON sub.JobName=countx.job " & _
                                "GROUP BY sub.JobName , sub.want; "
            
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open str, db, adOpenStatic, adLockReadOnly
    Set rs.ActiveConnection = Nothing
    
    Set DataGrid1.DataSource = rs
    
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

This post has been edited by Sailor: Feb 4 2017, 02:38 AM
Go to the top of the page
 
Doug Steele
post Feb 4 2017, 08:47 AM
Post#8


UtterAccess VIP
Posts: 21,445
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Hmm. Can't say anything jumps out at me, but it's been years since I've used ADO. Is there a particular reason why you're using ADO? It is, after all, an unsupported technology.

Might it be confused by the subtraction in the SELECT clause?

Does it work with

CODE
    str = "SELECT sub.want, Count(countx.job) as totx, sub.JobName AS jobs " & _
                                "FROM (SELECT DISTINCT names,job FROM table1) AS countx " & _
                                "RIGHT JOIN sub ON sub.JobName=countx.job " & _
   
                            "GROUP BY sub.JobName , sub.want; "

If so, what about

CODE
    str = "SELECT (sub.want - Count(countx.job)) AS totals, Count(countx.job) as totx, sub.JobName AS jobs " & _
                                "FROM (SELECT DISTINCT names,job FROM table1) AS countx " & _
                                "RIGHT JOIN sub ON sub.JobName=countx.job " & _
                                "GROUP BY sub.JobName , sub.want; "

--------------------
Go to the top of the page
 
Sailor
post Feb 4 2017, 10:04 AM
Post#9



Posts: 38
Joined: 14-December 16



The both of the codes you've posted gives the same error !

QUOTE
Might it be confused by the subtraction in the SELECT clause?


no , I don't think that , because like I said before the bellow code is works fine :

CODE
"SELECT sub.want-Count(table1.job) AS totals,Count(table1.job) as totx,sub.JobName AS jobs " & _
            "FROM table1 " & _
            "RIGHT JOIN sub ON sub.JobName=table1.job " & _
            "GROUP BY sub.JobName , sub.want; "


but for sure and from my post title , the second select (subquery) is the problem

and about using ADODB , is there a better way I can use it in VB6 ?
This post has been edited by Sailor: Feb 4 2017, 10:06 AM
Go to the top of the page
 
Doug Steele
post Feb 4 2017, 11:36 AM
Post#10


UtterAccess VIP
Posts: 21,445
Joined: 8-January 07
From: St. Catharines, ON (Canada)


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)

--------------------
Go to the top of the page
 
Sailor
post Feb 4 2017, 01:19 PM
Post#11



Posts: 38
Joined: 14-December 16



Thanks Doug
no I dislike DAO
instead I have switched to ADODC (MSADODC.OCX)
by placing it on the from , and using my query string as a RecordSource for it
wow , every thing is works fine .
Go to the top of the page
 
LPurvis
post Oct 23 2017, 11:55 AM
Post#12


UtterAccess Editor
Posts: 16,084
Joined: 27-June 06
From: England (North East / South Yorks)


Hello there.

Not exactly just passing by on this old thread, but pointed here from a PM request.
I'm aware of a query which looks something like:

SELECT Count(countx.job) - tblLocalSubtr1.SubtrValue AS employ_number, tblLocalSubtr1.JobName AS jobs
FROM (SELECT DISTINCT names, job FROM table1) AS countx
RIGHT JOIN tblLocalSubtr1 ON countx.job = tblLocalSubtr1.JobName
GROUP BY tblLocalSubtr1.JobName, tblLocalSubtr1.SubtrValue

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.)

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

Cheers

--------------------
Go to the top of the page
 
Sailor
post Oct 23 2017, 01:12 PM
Post#13



Posts: 38
Joined: 14-December 16



Well , thank you Purvis for posting here

QUOTE
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 error
however , I believe you are completely right

I have change the 'names' to 'namx' in the table1 structure

and it woks !

Purvis , you are always here to finish the problem

thank you thumbup.gif
This post has been edited by Sailor: Oct 23 2017, 02:04 PM
Go to the top of the page
 
LPurvis
post Oct 23 2017, 01:58 PM
Post#14


UtterAccess Editor
Posts: 16,084
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

>> what I have done is removing the subquery (the second select statement)

So what you're saying is that this query:

SELECT Count(countx.job) - tblLocalSubtr1.SubtrValue AS employ_number, tblLocalSubtr1.JobName AS jobs
FROM table1 AS countx
RIGHT JOIN tblLocalSubtr1 ON countx.job = tblLocalSubtr1.JobName
GROUP BY tblLocalSubtr1.JobName, tblLocalSubtr1.SubtrValue

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

Cheers

--------------------
Go to the top of the page
 
LPurvis
post Oct 23 2017, 02:03 PM
Post#15


UtterAccess Editor
Posts: 16,084
Joined: 27-June 06
From: England (North East / South Yorks)


Just to follow on.

>> I don't know whether ADODB accept subselect statements or not

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.

Cheers

--------------------
Go to the top of the page
 
Sailor
post Oct 23 2017, 02:12 PM
Post#16



Posts: 38
Joined: 14-December 16



Sorry , my previous post editing comes late

after I have follow what you have said
but not by using [names]

instead , I have changed the structure of table1

now it worked fine

LPrurvis , thank you very much .
This post has been edited by Sailor: Oct 23 2017, 02:40 PM
Go to the top of the page
 
LPurvis
post Nov 2 2017, 01:51 PM
Post#17


UtterAccess Editor
Posts: 16,084
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

I think you're welcome if I follow correctly. :-)
Glad you're sorted.

Cheers

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd November 2017 - 11:24 AM