Need help in vba-excel and Sql query

Question

Doubt in vba ADO and Sql query...

I have 2 sheets Namely adodc1,adodc2 (In one workbook)

In adodc1 has columns "Name","Dept" and some times its have "Sect" column

In adodc2 has"Name","Dept","sect" columns

what I want is when i run Query..Vba needs to check whether adodc1 have the Sect column or not ..if it have union the two sheets has usual else

want to return as empty value ..

The below Code taken from " "altered as per my needs

What it will do is union name and Dept column from two sheets ..now i want query to check whether adodc1 have the Column "sect" or not ..if it has

union "Sect" as usual else .. union as empty value

Sub connecttoexcel()

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim s As String
Dim i As Integer, j As Integer

strFile = ActiveWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & ";Extended Properties=""Excel 12.0 XML;HDR=Yes;IMEX=1"";"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
'here i want some stuff
strSQL = "Select Name, Dept from [Adodc1$] Union Select Name, Dept from [Adodc2$];"
rs.Open strSQL, cn
Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rs
Set rs = Nothing
End Sub

onedaywhen · Accepted Answer · 2012-03-09 09:25:56Z

2

Vba needs to check whether adodc1 have the Sect column or not

I suggest you use the OpenSchema method of the Connection object to discover whether the column exists e.g. something like:

Set rs = cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, "Adodc1$")
rs.Filter = "COLUMN_NAME = 'Sect'"
If rs.RecordCount = 1 Then
   ' Column exists
   ...

when it comes to check for 50 columns it will be more difficult i think ...

rs.Filter = "COLUMN_NAME = 'Sect' OR COLUMN_NAME = 'Name' OR COLUMN_NAME = 'Dept' ...

or test each one in a loop using an array etc.

Is it possible to use NZ Function inside of the sql query

The NZ() function is not a function of Access SQL, which is what is being used here to access Excel data. Rather, it part of the MS Access object model. In short, unless you are running this from an Access VBA project then NZ() is not available. But the workaround is trivial e.g.

Nz(Dept, '{{NONE}}')

has the same effect as

IIF(Dept IS NULL, '{{NONE}}', Dept)

I've read Access MVPs (Allen Browne?) say this is preferable to Nz() anyhow.

edited Mar 9, 2012 at 9:25

answered Mar 7, 2012 at 9:02

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sathish Kothandam Over a year ago

THanks for your reply ... when it comes to check for 50 columns it will be more difficult i think ... Is it possible to use NZ Function inside of the sql query ..

Sathish Kothandam Over a year ago

strSQL = "Select Name,Dept,IIF(Sect IS NULL,'',Sect) from [Adodc1$] UNION Select Name,Dept,Sect from [Adodc2$];" is it correct i have pasted here .. but it throws no value given for 1 or more parameter ..

onedaywhen Over a year ago

@SathishKothandam: works for me when all columns are present on both worksheets. p.s. I suggest you give that expression a name using an AS clause e.g. ...IIF(Sect IS NULL,'',Sect) AS Sect, ...

Sathish Kothandam Over a year ago

Ya .. it tried that too (strSQL = "Select Name,Dept,IIF(Sect IS NULL,'',Sect) AS Sect from [Adodc1$] UNION Select Name,Dept,Sect from [Adodc2$];") But it throws error( Circular reference caused by alias 'sect' in quary definition sect list ).

Collectives™ on Stack Overflow

Need help in vba-excel and Sql query

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related