Examples of SQL

The below code link the

     
sql = "SELECT tblProjects.*, sq.txtCurrentStatus, sq.dtUpdated "
sql = sql & "FROM tblProjects LEFT JOIN (SELECT cs.txtProjCode, cs.txtCurrentStatus, cs.dtUpdated "
sql = sql & "FROM tblCurrStatus AS cs INNER JOIN (SELECT cst.txtProjCode, Max(cst.dtUpdated) AS MaxOfdtUpdated "
sql = sql & "FROM tblCurrStatus AS cst GROUP BY cst.txtProjCode)  AS sqry "
sql = sql & "ON (cs.dtUpdated = sqry.MaxOfdtUpdated) "
sql = sql & "AND (cs.txtProjCode = sqry.txtProjCode))  AS sq ON tblProjects.txtProjCode = sq.txtProjCode "
sql = sql & "WHERE tblProjects.txtProjCode = 'abc'"

Contains 2 tables linked one to many. The table with multiple links must be deleted first before deleting the other table with single records.

  
sql = "DELETE tblCurrStatus.* FROM tblProjects INNER JOIN tblCurrStatus " & _
            "ON tblProjects.txtProjCode = tblCurrStatus.txtProjCode " & _
            "WHERE tblProjects.txtOwner <> 'Doi Makoto'"

    With rsProjects
        .CursorType = adOpenStatic
        .LockType = adLockOptimistic  'adLockReadOnly
        .Open sql, remConn, , , adCmdText
    End With
    
sql = "DELETE FROM tblProjects " & _
            "WHERE tblProjects.txtOwner <> 'Doi Makoto'"

    With rsProjects
        .CursorType = adOpenStatic
        .LockType = adLockOptimistic  'adLockReadOnly
        .Open sql, remConn, , , adCmdText
    End With

New! Comments

Have your say about what you just read! Leave me a comment in the box below.