CODEWITHSUNDEEP
sqlexceladovba
Sathish Kothandam
Sathish Kothandam

Reputation: 1520

Issue in ADO-excel, SQL query

SQL query in ado work fine only if the Sect column present in ADODC1 sheet.
If the ADODC1 sheet not have Sect column it throws error.
Pls correct my below query... 

strSQL = "Select Name,Dept,IIF(ISNULL(Sect),'',Sect) AS Sect from [Adodc1$] UNION Select Name,Dept,Sect from [Adodc2$];"

If the Sect column not present in ADODC1 Sheet, need the results as empty.

Upvotes: 0

Views: 1202

Answers (1)

onedaywhen
onedaywhen

Reputation: 57023

What you mean by "need the results as empty". If you fail over the error you will get a closed recordset. Is that 'empty'?

I suspect this is what you need to do (pseudo code):

On Error Resume Next
strSQL = "Select Name,Dept,IIF(ISNULL(Sect),'',Sect) AS Sect from [Adodc1$]" & _
             " UNION Select Name,Dept,Sect from [Adodc2$];"

Set rs = cn.Execute(strSQL)

If rs.State <> adStateOpen Then

  strSQL = "Select Name,Dept,NULL AS Sect from [Adodc1$]" & _
             " UNION Select Name,Dept,Sect from [Adodc2$];"

  Set rs = cn.Execute(strSQL)

    If rs.State <> adStateOpen Then

        ' Deeper problems...

Upvotes: 2

Related Questions