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
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()
.
source to share