Are Multiple Joins Unavailable When Using Adodb to Query an Excel File in a Vba Procedure

Are multiple JOINs unavailable when using ADODB to query an Excel file in a VBA procedure?

Access Jet/ADODB/ACE likes brackets with multiple tables

SELECT  * FROM ( [Riesgos$] r
LEFT JOIN [EventosRiesgos$] er ON r.[Id]=er.[Id Riesgo] )
LEFT JOIN [Eventos$] e ON er.[Id Evento]=e.[Id]

The only difference is brackets around the [riesgos$] r .... er.[Id Riesggo]

Subquery using SELECT ADODB Excel Not Working (VBA)

You can't use a sub-select to determine the table name of the select, use 2 queries.

    Dim sTableName As String, SQL As String, rcdset As New ADODB.Recordset
    If OpenConnection() = True Then 'The connection function already created in my module
      
        SQL = " SELECT [SheetName] " & _
              " FROM `Breakdown structure library$` " & _
              " WHERE [Name (Name *)]='WBS';"
        sTableName = con.Execute(SQL)(0)

        SQL = " SELECT * FROM [" & sTableName & "$]"
        rcdset.Open SQL, con, adOpenStatic, adLockBatchOptimistic
        With shTemp
            .Cells.Delete
            .Range("A1").CopyFromRecordset rcdset
        End With
        If con.State Then con.Close
     
     End If

ADODB/Excel query error

You forgot the group by

The query should look like this

Select Destinataire, SUM(NbCompteurElec) AS NombreTotal FROM [Feuil1$] 
WHERE [DateMad] Between #2012/10/22# And #2012/10/26# And [Destinataire] = 'REL12'
Group By Destinataire

In your code it should be like this

MyQuery = "Select Destinataire, SUM(" & Entete & ") AS NombreTotal FROM [Feuil1$] " _
                         & "WHERE [DateMad] Between #" _
                         & Format(date_deb, "yyyy/mm/dd") _
                         & "# And #" & Format(date_fin, "yyyy/mm/dd") & "#" & Query3 & " Group By Destinataire"

Here's a tuto about the GROUP BY clause

Need help in vba-excel and Sql query

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.

Writing Query statement with a String and a Date in Excel VBA

It seems that '[Birthday] = '" & CStr(CLng(CDate(tb_bday))) &' creates an error when no value is entered in the textbox, tb_bday. I cant explain why. As a workaround, I just created an if-then statement. I still would want to avoid using if-then if it can be coded in one line instead.

Private Sub SearchRecords_Click()

    Dim conn As New ADODB.Connection 
    Dim rst As New ADODB.Recordset 
    Dim db_path As Variant

    db_path = "E:\DATABASE.xlsm"

    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & db_path & ";Extended Properties=""Excel 12.0 Macro;HDR=YES;"";"

    Dim qry As String

    if tb_bday.value = ""  then 
        qry = "Select [LastName], Format([Birthday], 'mmmm dd, yyyy') from [db_Reports$] where [LastName] = '" & tb_Lastname & "'"  
    else 
        qry = "Select [LastName], Format([Birthday], 'mmmm dd, yyyy') from [db_Reports$] where where [LastName] = '" & tb_Lastname & "' or [Birthday] = '" & CStr(CLng(CDate(tb_bday))) & "'"
    end if 

    rst.Open qry, conn, adOpenKeyset, adLockOptimistic

    rst.Close
    conn.Close
    Set rst = Nothing
    Set conn = Nothing
End Sub

---

Related Topics