Need help with vba-excel and Sql queries

Doubt in ADO and Sql vba request ...

I have 2 sheets. Namely adodc1, adodc2 (in one book)

Adodc1 has columns "Name", "Department" and several times has column "Sect"

Adodc2 has columns "Name", "Department", "Section"

what i want when i run Query..Vba needs to check if adodc1 has Sect column or not. If it has a union, then the two leaves have the usual other

want to return as null.

Below is the code modified to suit my needs

What it will do is the name of the union and the Dept column of the two sheets. Now I want the query to check if adodc1 has a "sect" column or not. If he has

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

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



source to share

1 answer

Vba need to check if adodc1 has Sect column or not

I suggest you use the OpenSchema method of the Connection object to see if a column exists, eg. 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 checking for 50 columns it gets trickier i think ...

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


or check each of them in a loop using an array, etc.

Is it possible to use the NZ function inside a SQL query

The function is NZ()

not an Access SQL function that is used here to access Excel data. Rather, it is part of the MS Access object model. In short, if you are not using this from a VBA Access project, then NZ()

not available. But the workaround is trivial for example.

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


has the same effect as

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


I read Access MVPs (Allen Browne?), Said this is preferable NZ()




All Articles