SQL Query in VBA

[tigor]

Hello Everybody!

I'm trying to get collect data from two excel sheet with OLEDB and sql query with subquery but couldnt.
I got error messaage:
At most one record can be returned by this subquery (Run-time error :80004005)

Please help me, I was trying to find solution on internet but couldnt.

Sub Makró1()
'
' Makró1 Makró
'
 Dim myConnection As String
    Dim RS As ADODB.Recordset
    Dim mySQL As String
    Dim strPath As String
    Dim wsMain As Worksheet
    Debug.Print Now
    Set wsMain = Worksheets("Report")
    Application.ScreenUpdating = False
    strPath = ActiveWorkbook.FullName
    myConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                     "Data Source=" & strPath & ";Extended Properties=Excel 12.0"
                    
            mySQL = "SELECT [T2$].[A], " & _
                    "[T2$].[B1], " & _
                    "[T1$]., " & _
                    "(select SUM([T2$].[B1]) FROM [T2$] GROUP BY [T2$].[A], [T2$].[B1]) " & _
                    "FROM [T2$] inner JOIN [T1$] ON [T2$].[A] = [T1$].[A]  where [t1$]. <> 0"
              
        Set RS = New ADODB.Recordset
        RS.Open mySQL, myConnection, adOpenForwardOnly, adLockOptimistic
        
        wsMain.Range("A2").CopyFromRecordset RS
    RS.Close
    Set RS = Nothing
    Application.ScreenUpdating = True
    Debug.Print Now
'
End Sub

 

[RoryA]

You appear to be missing a field name from the last part of your WHERE criterion

 

[tigor]

You appear to be missing a field name from the last part of your WHERE criterion

Sorry, not only there but in my excel macro it is good


mySQL = "SELECT [T2$].[A], " & _
"[T2$].[B1], " & _
"[T1$]., " & _
"(select SUM([T2$].[B1]) FROM [T2$] GROUP BY [T2$].[A], [T2$].[B1]) " & _
"FROM [T2$] inner JOIN [T1$] ON [T2$].[A] = [T1$].[A] where [t1$]. <> 0"

 

[tigor]

Sorry, not only there but in my excel macro it is good


mySQL = "SELECT [T2$].[A], " & _
"[T2$].[B1], " & _
"[T1$]., " & _
"(select SUM([T2$].[B1]) FROM [T2$] GROUP BY [T2$].[A], [T2$].[B1]) " & _
"FROM [T2$] inner JOIN [T1$] ON [T2$].[A] = [T1$].[A] where [t1$]. <> 0"

STRANGE after paste it goes wrong

 

[Joe4]

You are still missing your field name here:
where [t1$]. <> 0"

t1$ is your table reference, and then you usually have a period and then the field name.
You have the period, but are still missing the field name.

 

[tigor]

STRANGE after paste it goes wrong

in original macro in Select and In Where

 

[tigor]

You are still missing your field name here:
where [t1$]. <> 0"

t1$ is your table reference, and then you usually have a period and then the field name.
You have the period, but are still missing the field name.

 

[Joe4]

I think I see what is going on with your posting issue. I think that the "B" between the right and left brackets is being mistaken for the BOLD editing tags on this board.
We can see it on your screen print.

 

[Joe4]

I don't think you have the subquery in the correct place or structured properly.
I do not have the resources I need to set it up and test it out right now, but think I should be able to at some point later in the day, if no one else replies before then.

 

[tigor]

I don't think you have the subquery in the correct place or structured properly.
I do not have the resources I need to set it up and test it out right now, but think I should be able to at some point later in the day, if no one else replies before then.

Thank you in advance !