Option Compare Database
Option Explicit
'-------------------------------------------------------------------------------------'
' Funktion: QSelTableType
'
' Auswahlabfrage: Erstellt eine UNION Abfrage zur Ermittlung der IBM DB2 Umgebungen
' (CREATOR), Tabellen (NAME), der Tabellentypen (TYPE) und
' Feststellung, ob es sich bei der Tabelle um eine Protokolltabelle
' (PROTOCOL) handelt.
' Es werden alle freigegebenen IBM DB2 Host Umgebungen der Tabelle
' tblDB2EnvSQLCfg durchlaufen.
'
' Parameter: Ohne
'
' Rückgabe: QSelTableType
'-------------------------------------------------------------------------------------'
Public Function QSelTableType() As String
On Error GoTo QSelTableType_Err
Dim dbDAO As DAO.Database
Dim rstDAO As DAO.Recordset
Dim strSQL As String
Dim intCnt As Integer
Dim boolErr As Boolean
Dim avntSQLEval() As Variant
avntSQLEval = Array("Between", "In", "Like")
strSQL = QSelDelTable("tblDB2EnvSQLCfg", _
False)
Set dbDAO = CurrentDb()
Set rstDAO = dbDAO.OpenRecordset(strSQL, _
dbOpenSnapshot)
With rstDAO
.MoveFirst
Do Until .EOF
If !DB2UsageOK = True Then
QSelTableType = QSelTableType & _
IIf(intCnt > 0, "UNION " & vbCrLf, vbNullString) & _
"SELECT CREATOR, NAME, '" & !DB2Type & "' AS TYPE, '" & _
!DB2Protocol & "' AS PROTOCOL " & _
"FROM " & modPublDecl.gconAccIBMSysTable & modPublDecl.gconSpace & _
vbCrLf
QSelTableType = QSelTableType & _
"WHERE CREATOR " & _
IIf(IsNull(!DB2EnvTo) = False, _
avntSQLEval(0) & " '" & !DB2EnvFrom & "' AND '" & !DB2EnvTo, _
avntSQLEval(2) & " '" & !DB2EnvFrom)
QSelTableType = QSelTableType & "' AND NAME "
If !DB2SqlEval = avntSQLEval(0) Then
QSelTableType = QSelTableType & _
IIf(IsNull(!DB2TablesTo) = False, _
avntSQLEval(0) & " '" & !DB2TablesFrom & "' AND '" & !DB2TablesTo, _
avntSQLEval(2) & " '" & !DB2TablesFrom) & "' " & vbCrLf
ElseIf !DB2SqlEval = avntSQLEval(1) Then
QSelTableType = QSelTableType & _
avntSQLEval(1) & " ('" & !DB2TablesFrom & "', '"
QSelTableType = QSelTableType & _
IIf(IsNull(!DB2TablesTo) = False, _
!DB2TablesTo, vbNullString) & "') " & vbCrLf
ElseIf !DB2SqlEval = avntSQLEval(2) Then
QSelTableType = QSelTableType & _
avntSQLEval(2) & " '" & !DB2TablesFrom & "' " & vbCrLf
End If
intCnt = intCnt + 1
End If
.MoveNext
Loop
.Close
QSelTableType = QSelTableType & "ORDER BY CREATOR, NAME, TYPE"
End With
boolErr = IIf(intCnt = 0, True, False)
QSelTableType_Exit:
Set dbDAO = Nothing
Set rstDAO = Nothing
If boolErr = True Then
QSelTableType = vbNullString
Call modPublic.ShowMsgBox(IIf(intCnt = 0, "Keine " & modPublDecl.gconDB2 & _
" Umgebung(en)/Tabelle(n) festgelegt.", Err.Description), _
modPublDecl.gconOkCritStyle, _
modPublDecl.gconDB2 & " Tabellentyp ermitteln", _
True)
End If
Exit Function
QSelTableType_Err:
boolErr = True
intCnt = 1
Resume QSelTableType_Exit
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelWGDC
'
' Auswahlabfrage: SQL Anweisung zur Auswahl der Data Collections der aktuellen
' Arbeitsgruppe.
'
' Parameter: Ohne
'
' Rückgabe: QSelWGDC
'-------------------------------------------------------------------------------------'
Public Function QSelWGDC() As String
QSelWGDC = _
"SELECT DISTINCT tblDB2DMCMFiles.*, " & vbCrLf & _
"tblDB2DMUserProfile.ProfileGroup " & vbCrLf & _
"FROM ((tblDB2DMUserProfile " & vbCrLf & _
"INNER JOIN tblDB2DMCMFiles " & vbCrLf & _
"ON tblDB2DMUserProfile.NTUserID = tblDB2DMCMFiles.NTUserID) " & vbCrLf & _
"LEFT JOIN tblDB2DMCMDCTables " & vbCrLf & _
"ON tblDB2DMCMFiles.DCID = tblDB2DMCMDCTables.DCID) " & vbCrLf & _
"INNER JOIN tblDB2DMLogonInfo " & vbCrLf & _
"ON tblDB2DMUserProfile.NTUserID = tblDB2DMLogonInfo.NTUserID " & vbCrLf & _
"WHERE tblDB2DMUserProfile.ProfileGroup Like " & _
"'" & modPublDecl.gstrWorkGroup & "' " & vbCrLf & _
"ORDER BY tblDB2DMCMFiles.DCID"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDistUserDC
'
' Auswahlabfrage: SQL Anweisung zur Auswahl der Data Collections der angemeldeten
' Bearbeiterin/des angemeldeten Bearbeiters (Windows User ID).
'
' Parameter: strWinUserID
'
' Rückgabe: QSelDistUserDC
'-------------------------------------------------------------------------------------'
Public Function QSelDistUserDC(ByVal strWinUserID As String) As String
QSelDistUserDC = _
"SELECT DISTINCT tblDB2DMCMFiles.* " & vbCrLf & _
"FROM tblDB2DMCMFiles " & vbCrLf & _
"LEFT JOIN tblDB2DMCMDCTables " & vbCrLf & _
"ON tblDB2DMCMFiles.DCID = tblDB2DMCMDCTables.DCID " & vbCrLf & _
NTUserIDEQstrWinUserID(strWinUserID, _
True, _
False, _
True, _
True, _
"tblDB2DMCMFiles") & _
"ORDER BY tblDB2DMCMFiles.DCID"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDelTable
'
' Auswahl- oder
' Löschabfrage: Alle Sätze der Tabelle strTable, gesteuert durch boolDelete,
' auswählen oder löschen.
'
' Parameter: strTable
' boolDelete
'
' Rückgabe: QSelDelTable
'-------------------------------------------------------------------------------------'
Public Function QSelDelTable(ByVal strTable As String, _
ByVal boolDelete As Boolean) As String
QSelDelTable = _
DelOrSel(boolDelete) & "* " & vbCrLf & _
"FROM " & strTable
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDistEnvCreator
'
' Auswahlabfrage: Eindeutigen CREATOR der Tabelle tblDB2Tables bei
' übereinstimmender/nicht übereinstimmender DB2 Umgebung (strDB2Env),
' gesteuert durch boolNot, auswählen.
'
' Parameter: strDB2Env
' boolNot
'
' Rückgabe: QSelDistEnvCreator
'-------------------------------------------------------------------------------------'
Public Function QSelDistEnvCreator(ByVal strDB2Env As String, _
ByVal boolNot As Boolean) As String
QSelDistEnvCreator = _
"SELECT DISTINCT CREATOR " & vbCrLf & _
"FROM tblDB2Tables " & vbCrLf & _
"WHERE Trim(CREATOR) " & NotOrNull(boolNot) & "Like '" & strDB2Env & "'"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelCorrDCTables
'
' Auswahlabfrage: Alle Datensätze der Tabelle tblDB2DMCMDCTablesKey mit
' übereinstimmender Tabelle und DCID auswählen.
'
' Parameter: lngDCID
' strTable
' strKey
'
' Rückgabe: QSelCorrDCTables
'-------------------------------------------------------------------------------------'
Public Function QSelCorrDCTables(ByVal lngDCID As Long, _
ByVal strTable As String, _
ByVal strKey As String) As String
QSelCorrDCTables = _
QSelDelTable("tblDB2DMCMDCTablesKey", _
False) & modPublDecl.gconSpace & vbCrLf & _
DCIDEQlngDCID(lngDCID, True) & _
"AND Tabelle Like '" & strTable & "' " & vbCrLf & _
"AND DB2KeyVal Like '" & strKey & "'"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDistDCKeyTables
'
' Auswahlabfrage: Eindeutige Tabellennamen der Tabelle tblDB2DMCMDCTablesKey mit
' übereinstimmender DCID und, falls strDelDCID <> vbNullString,
' DelDCID auswählen.
'
' Parameter: strTable
' lngDCID
' strDelDCID
'
' Rückgabe: QSelDistDCKeyTables
'-------------------------------------------------------------------------------------'
Public Function QSelDistDCKeyTables(ByVal strTable As String, _
ByVal lngDCID As Long, _
ByVal strDelDCID As String) As String
QSelDistDCKeyTables = _
"SELECT DISTINCT Tabelle AS TabellenAuswahl " & vbCrLf & _
"FROM " & strTable & modPublDecl.gconSpace & vbCrLf & _
DCIDEQlngDCID(lngDCID, True)
If Not (strDelDCID = vbNullString) Then
QSelDistDCKeyTables = QSelDistDCKeyTables & _
"AND Format(DelDCID,'" & modPublDecl.gconlngDCIDFmt & "') Like '" & _
strDelDCID & "'" & vbCrLf
End If
QSelDistDCKeyTables = QSelDistDCKeyTables & _
UnionSelAll(True, _
"TabellenAuswahl", _
"tblDB2DMCMDCTablesKeyDelete", _
"TabellenAuswahl")
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDistDCTablesKey
'
' Auswahlabfrage: Eindeutigen Key der Tabelle tblDB2DMCMDCTablesKey mit
' übereinstimmender DCID und Tabelle auswählen.
'
' Parameter: strTable
' lngDCID
' strDB2Table
' strDB2Key
' strOrderByField
'
' Rückgabe: QSelDistTCTablesKey
'-------------------------------------------------------------------------------------'
Public Function QSelDistDCTablesKey(ByVal strTable As String, _
ByVal lngDCID As Long, _
ByVal strDB2Table As String, _
ByVal strDB2Key As String, _
ByVal strOrderByField As String) As String
QSelDistDCTablesKey = _
"SELECT DISTINCT DCID, DB2KeyVal " & vbCrLf & _
"FROM " & strTable & modPublDecl.gconSpace & vbCrLf & _
DCIDEQlngDCID(lngDCID, True) & _
"AND Tabelle Like '" & strDB2Table & "' " & vbCrLf & _
"AND DB2KeyVal Like '" & strDB2Key & "' " & vbCrLf & _
"ORDER BY " & strOrderByField
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDCTablesKeyDel
'
' Auswahlabfrage: Alle Sätze der Tabelle tblDB2DMCMDCTablesKeyDelete mit
' übereinstimmender DelDCID, Tabelle und übereinstimmendem Key
' auswählen.
'
' Parameter: lngDCID
' strDCIDColl
' strTable
' strKey
'
' Rückgabe: QSelDCTablesKeyDel
'-------------------------------------------------------------------------------------'
Public Function QSelDCTablesKeyDel(ByVal lngDCID As String, _
ByVal strDCIDColl As String, _
ByVal strTable As String, _
ByVal strKey As String) As String
QSelDCTablesKeyDel = _
QSelDelTable("tblDB2DMCMDCTablesKeyDelete", _
False) & modPublDecl.gconSpace & vbCrLf & _
DCIDEQlngDCID(lngDCID, True) & _
"AND DelDCID In (" & strDCIDColl & ") " & vbCrLf & _
"AND Tabelle Like '" & strTable & "' " & vbCrLf & _
"AND DB2KeyVal Like '" & strKey & "' " & vbCrLf & _
"ORDER BY DelDCID, Tabelle, DB2KeyVal"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDistTablesKeyDel
'
' Auswahlabfrage: Eindeutige Sätze der Tabelle tblDB2DMCMDCTablesKeyDelete mit
' übereinstimmender DCID auswählen.
'
' Parameter: lngDCID
'
' Rückgabe: QSelDistTablesKeyDel
'-------------------------------------------------------------------------------------'
Public Function QSelDistTablesKeyDel(ByVal lngDCID As Long) As String
QSelDistTablesKeyDel = _
"SELECT DISTINCT DCID, DelDCID " & vbCrLf & _
"FROM tblDB2DMCMDCTablesKeyDelete " & vbCrLf & _
DCIDEQlngDCID(lngDCID, True)
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelLoggedOn
'
' Auswahlabfrage: Datensätze der Tabelle tblDB2DMLogonInfo (LoggedOn = True)
' auswählen.
'
' Parameter: boolAllUsers
' strWinUserID
'
' Rückgabe: QSelLoggedOn
'-------------------------------------------------------------------------------------'
Public Function QSelLoggedOn(ByVal boolAllUsers As Boolean, _
ByVal strWinUserID As String) As String
QSelLoggedOn = _
"SELECT * " & vbCrLf & _
"FROM tblDB2DMLogonInfo " & vbCrLf & _
NTUserIDEQstrWinUserID(strWinUserID, _
True, _
IIf(strWinUserID = modPublDecl.gstrWinUserID, True, False), _
True, _
True) & _
"AND LoggedOn=True " & vbCrLf & _
"ORDER BY DateTimeLoggedOn, NTUserID"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelLoggedOnExcCurUser
'
' Auswahlabfrage: Alle angemeldeten Bearbeiter/innen, mit Ausnahme der angemeldeten
' Administratorin/des angemeldeten Administrators, auswählen.
'
' Parameter: Ohne
'
' Rückgabe: QSelLoggedOnExcCurUser
'-------------------------------------------------------------------------------------'
Public Function QSelLoggedOnExcCurUser() As String
QSelLoggedOnExcCurUser = _
"SELECT tblDB2DMUserProfile.EMail, tblDB2DMLogonInfo.LoggedOn, " & _
"tblDB2DMLogonInfo.IBMHostID" & vbCrLf & _
"FROM tblDB2DMUserProfile " & vbCrLf & _
"INNER JOIN tblDB2DMLogonInfo " & vbCrLf & _
"ON tblDB2DMUserProfile.NTUserID = tblDB2DMLogonInfo.NTUserID " & vbCrLf & _
"WHERE tblDB2DMUserProfile.EMail Is Not Null " & vbCrLf & _
"AND tblDB2DMLogonInfo.LoggedOn=True " & vbCrLf & _
"AND tblDB2DMLogonInfo.NTUserID Not Like '" & modPublDecl.gstrWinUserID & "'"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelNumRecs
'
' Auswahlabfrage: Datensätze des Feldes strField der Tabelle auswählen.
' Bedingungen werden durch bytCondition gesteuert.
'
' Parameter: strTable
' strField
' bytCondition
'
' Rückgabe: QSelNumRecs
'-------------------------------------------------------------------------------------'
Public Function QSelNumRecs(ByVal strTable As String, _
ByVal strField As String, _
ByVal bytCondition As Byte) As String
Dim strCondition As String
QSelNumRecs = _
"SELECT Count(" & strField & ") AS NumRecs " & vbCrLf & _
"FROM " & strTable
If bytCondition = 0 Then
strCondition = vbNullString
ElseIf bytCondition = 1 Then
strCondition = "WHERE JobTimeOut > Now()"
ElseIf bytCondition = 2 Then
strCondition = "WHERE IsAdmin=True"
ElseIf bytCondition = 3 Then
strCondition = "HAVING LoggedOn=True "
End If
If Not (strCondition = vbNullString) Then
QSelNumRecs = QSelNumRecs & modPublDecl.gconSpace & vbCrLf & _
strCondition
End If
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelUserProfileGrp
'
' Auswahlabfrage: Windows User ID, Arbeitsgruppe und AllowJclClear aus den Tabellen
' tblDB2DMUserProfile und tblDB2DMUserGroup bei übereinstimmender
' Windows User ID auswählen.
'
' Parameter: strWinUserID
'
' Rückgabe: QSelUserProfileGrp
'-------------------------------------------------------------------------------------'
Public Function QSelUserProfileGrp(ByVal strWinUserID As String) As String
QSelUserProfileGrp = _
"SELECT tblDB2DMUserProfile.NTUserID, " & vbCrLf & _
"tblDB2DMUserProfile.ProfileGroup, " & vbCrLf & _
"tblDB2DMUserGroup.AllowJclClear " & vbCrLf & _
"FROM tblDB2DMUserGroup " & vbCrLf & _
"INNER JOIN tblDB2DMUserProfile " & vbCrLf & _
"ON tblDB2DMUserGroup.ProfileGroup = tblDB2DMUserProfile.ProfileGroup " & vbCrLf & _
NTUserIDEQstrWinUserID(strWinUserID, _
True, _
False, _
False, _
False, _
"tblDB2DMUserProfile")
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDistAllExcCurrUser
'
' Auswahlabfrage: NTUserID der Tabelle tblDB2DMUserProfile bei nicht übereinstimmender
' strWinUserID und übereinstimmender Arbeitsgruppe auswählen.
'
' Parameter: strWinUserID
' strWorkGroup
'
' Rückgabe: QSelDistAllExcCurrUser
'-------------------------------------------------------------------------------------'
Public Function QSelDistAllExcCurrUser(ByVal strWinUserID As String, _
ByVal strWorkGroup As String) As String
QSelDistAllExcCurrUser = _
"SELECT DISTINCT Trim(tblDB2DMUserProfile.NTUserID) AS WinUserID " & vbCrLf & _
"FROM (tblDB2DMUserProfile " & vbCrLf & _
"INNER JOIN tblDB2DMLogonInfo " & vbCrLf & _
"ON tblDB2DMUserProfile.NTUserID = tblDB2DMLogonInfo.NTUserID) " & vbCrLf & _
"INNER JOIN tblDB2DMCMFiles " & vbCrLf & _
"ON tblDB2DMLogonInfo.NTUserID = tblDB2DMCMFiles.NTUserID " & vbCrLf & _
"WHERE Trim(tblDB2DMUserProfile.NTUserID) " & _
"Not Like '" & strWinUserID & "' " & vbCrLf & _
"AND Trim(tblDB2DMUserProfile.ProfileGroup) " & _
"Like '" & strWorkGroup & "' " & vbCrLf & _
"ORDER BY Trim(tblDB2DMUserProfile.NTUserID)"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelCorDCIDHostID
'
' Auswahlabfrage: Datensätze der Tabelle tblDB2DMCMDCTables bei übereinstimmender
' DCID und Host ID auswählen.
'
' Parameter: lngDCID
' strHostID
'
' Rückgabe: QSelCorDCIDHostID
'-------------------------------------------------------------------------------------'
Public Function QSelCorDCIDHostID(ByVal lngDCID As Long, _
ByVal strHostID As String) As String
QSelCorDCIDHostID = _
"SELECT tblDB2DMCMDCTables.DCID, " & vbCrLf & _
"tblDB2DMCMDCTables.CreatorName, " & vbCrLf & _
"tblDB2DMCMFiles.HostID " & vbCrLf & _
"FROM tblDB2DMCMFiles " & vbCrLf & _
"INNER JOIN tblDB2DMCMDCTables " & vbCrLf & _
"ON tblDB2DMCMFiles.DCID = tblDB2DMCMDCTables.DCID " & vbCrLf & _
DCIDEQlngDCID(lngDCID, True, "tblDB2DMCMDCTables") & _
"AND tblDB2DMCMFiles.HostID Like '" & strHostID & "'"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDB2Type
'
' Auswahlabfrage: UNION Abfrage auf TYPE der Tabelle tblDB2Tables mit
' übereinstimmendem CREATOR durch Lesen eines DAO Recordsets
' (Tabelle tblDB2Type) erstellen.
'
' Parameter: strDB2Env
'
' Rückgabe: QSelDB2Type
'-------------------------------------------------------------------------------------'
Public Function QSelDB2Type(ByVal strDB2Env As String) As String
Dim dbDAO As DAO.Database
Dim rstDAO As DAO.Recordset
Dim strSQL As String
strSQL = QSelDelTable("tblDB2Type", _
False)
Set dbDAO = CurrentDb()
Set rstDAO = dbDAO.OpenRecordset(strSQL, _
dbOpenSnapshot)
With rstDAO
.MoveFirst
If .EOF = False Then
QSelDB2Type = _
"SELECT TOP 1 '" & modPublDecl.gconSelectAll & "' " & _
"AS Tabellentyp, tblDB2Tables.TYPE " & vbCrLf & _
"FROM tblDB2Tables " & vbCrLf & _
"WHERE Trim(CREATOR) Like '" & strDB2Env & "' " & vbCrLf & _
"AND tblDB2Tables.TYPE Like '*' "
Do Until .EOF
QSelDB2Type = QSelDB2Type & vbCrLf & _
"UNION " & vbCrLf & _
"SELECT DISTINCT '" & !Description & "' " & _
"AS Tabellentyp, tblDB2Tables.TYPE " & vbCrLf & _
"FROM tblDB2Tables " & vbCrLf & _
"WHERE Trim(CREATOR) Like '" & strDB2Env & "' " & vbCrLf & _
"AND tblDB2Tables.TYPE Like '" & !DB2Type & "'"
.MoveNext
Loop
.Close
QSelDB2Type = QSelDB2Type & vbCrLf & _
"ORDER BY Tabellentyp"
ElseIf .EOF = True Then
Call modPublic.ShowMsgBox("Keine " & modPublDecl.gconDB2 & _
" Tabellentypen festgelegt.", _
modPublDecl.gconOkCritStyle, _
"Tabellentyp auswählen", _
True)
QSelDB2Type = vbNullString
End If
End With
Set dbDAO = Nothing
Set rstDAO = Nothing
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelNameTypeCorrCreator
'
' Auswahlabfrage: NAME der Tabelle tblDB2Tables bei übereinstimmendem CREATOR
' und übereinstimmendem TYPE auswählen.
'
' Parameter: strCreator
' strTableType
'
' Rückgabe: QSelNameTypeCorrCreator
'-------------------------------------------------------------------------------------'
Public Function QSelNameTypeCorrCreator(ByVal strCreator As String, _
ByVal strTableType As String) As String
QSelNameTypeCorrCreator = _
"SELECT NAME " & vbCrLf & _
"FROM tblDB2Tables " & vbCrLf & _
"WHERE Trim(CREATOR) Like '" & strCreator & "' " & vbCrLf & _
"AND TYPE Like '" & strTableType & "' " & vbCrLf & _
"ORDER BY NAME"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelTblFldOrderByFld
'
' Auswahlabfrage: Alle Datensätze der Tabelle strTable mit übereinstimmendem Feld
' strField, sortiert nach strOrderByField auswählen.
'
' Parameter: strTable
' strField
' strOrderByField
'
' Rückgabe: QSelTblFldOrderByFld
'-------------------------------------------------------------------------------------'
Public Function QSelTblFldOrderByFld(ByVal strTable As String, _
ByVal strField As String, _
ByVal strOrderByField As String) As String
QSelTblFldOrderByFld = _
"SELECT " & strField & modPublDecl.gconSpace & vbCrLf & _
"FROM " & strTable
If Not (strOrderByField = vbNullString) Then
QSelTblFldOrderByFld = QSelTblFldOrderByFld & modPublDecl.gconSpace & vbCrLf & _
"ORDER BY " & strOrderByField
End If
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDB2DMDCMDeleteLog
'
' Auswahlabfrage: Alle Datensätze der Tabelle tblDB2DMCMDeleteLog mit übereinstimmender
' strWorkGroup und strWinUserID auswählen.
'
' Parameter: strWorkGroup
' strWinUserID
'
' Rückgabe: QSelDB2DMDCMDeleteLog
'-------------------------------------------------------------------------------------'
Public Function QSelDB2DMDCMDeleteLog(ByVal strWorkGroup As String, _
ByVal strWinUserID As String) As String
QSelDB2DMDCMDeleteLog = _
QSelDelTable("tblDB2DMCMDeleteLog", _
False) & modPublDecl.gconSpace & vbCrLf & _
"WHERE NTUserID Like '" & strWinUserID & "' " & vbCrLf & _
"AND NTUserID IN (SELECT NTUserID FROM tblDB2DMUserProfile " & vbCrLf & _
"WHERE ProfileGroup Like '" & strWorkGroup & "') " & vbCrLf & _
"ORDER BY NTUserID, DelDCID"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelUserDCs
'
' Auswahlabfrage: Alle Datensätze der Tabelle tblDB2DMCMFiles mit übereinstimmender
' strWinUserID auswählen.
'
' Parameter: strWinUserID
'
' Rückgabe: QSelUserDCs
'-------------------------------------------------------------------------------------'
Public Function QSelUserDCs(ByVal strWinUserID As String) As String
QSelUserDCs = _
QSelDelTable("tblDB2DMCMFiles", _
False) & modPublDecl.gconSpace & vbCrLf & _
NTUserIDEQstrWinUserID(strWinUserID, _
True, _
False, _
False, _
True) & _
"ORDER BY DCID"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QDelAllKeys
'
' Löschabfrage: Alle Datensätze der Tabelle tblDB2DMCMDCTablesKeyDelete mit
' übereinstimmender Tabelle und DCID löschen.
'
' Parameter: lngDCID
' strDelDCID
' strDelTable
' strDelKey
'
' Rückgabe: QDelAllKeys
'-------------------------------------------------------------------------------------'
Public Function QDelAllKeys(ByVal lngDCID As Long, _
ByVal strDelDCID As String, _
ByVal strDelTable As String, _
ByVal strDelKey As String) As String
QDelAllKeys = _
QSelDelTable("tblDB2DMCMDCTablesKeyDelete", _
True) & modPublDecl.gconSpace & vbCrLf & _
"WHERE DelDCID Like '" & strDelDCID & "' " & vbCrLf & _
DCIDEQlngDCID(lngDCID, False) & _
"AND Tabelle Like '" & strDelTable & "' " & vbCrLf & _
"AND DB2KeyVal Like '" & strDelKey & "'"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelSearchDC
'
' Auswahlabfrage: Suche nach Data Collection(s).
'
' Parameter: strSuchenNach
' strSQLOrderBy
'
' Rückgabe: QSelSearchDC
'-------------------------------------------------------------------------------------'
Public Function QSelSearchDC(ByVal strSuchenNach As String, _
ByVal strSQLOrderBy As String) As String
QSelSearchDC = _
"SELECT DCID, DCNr, Description AS Beschreibung, DCUmgebung, " & vbCrLf & _
"Format([EntladeDateTime],'" & modPublDecl.gconEurDateTime & "') " & _
"AS UnloadDateTime, " & vbCrLf & _
"Format([LadeDateTime],'" & modPublDecl.gconEurDateTime & "') " & _
"AS LoadDateTime, " & vbCrLf & _
"ProfileGroup " & vbCrLf & _
"FROM tblDB2DMUserProfile " & vbCrLf & _
"INNER JOIN tblDB2DMCMFiles " & vbCrLf & _
"ON tblDB2DMUserProfile.NTUserID = tblDB2DMCMFiles.NTUserID " & vbCrLf & _
"WHERE tblDB2DMUserProfile.ProfileGroup Like '" & modPublDecl.gstrWorkGroup & "' " & vbCrLf & _
"AND (DCID & DCNr & Description & DCUmgebung & EntladeDateTime) " & vbCrLf & _
"Like '" & strSuchenNach & "' " & vbCrLf & _
strSQLOrderBy
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDelCorrTableField
'
' Auswahl- oder
' Löschabfrage: Datensätze der Tabelle strTable mit übereinstimmendem strField
' und dem Wert strFieldVal (falls boolIsLong = True, dann handelt
' es sich um den Datentyp Long), gesteuert durch boolDelete,
' auswählen oder löschen.
'
' Parameter: strTable
' strField
' strFieldVal
' boolIsLong
' boolDelete
'
' Rückgabe: QSelDelCorrTableField
'-------------------------------------------------------------------------------------'
Public Function QSelDelCorrTableField(ByVal strTable As String, _
ByVal strField As String, _
ByVal strFieldVal As String, _
ByVal boolIsLong As Boolean, _
ByVal boolDelete As Boolean) As String
QSelDelCorrTableField = _
DelOrSel(boolDelete) & "* " & vbCrLf & _
"FROM " & strTable & modPublDecl.gconSpace & vbCrLf & _
"WHERE Trim(" & strField & ") "
If boolIsLong = False Then
QSelDelCorrTableField = QSelDelCorrTableField & _
"Like '" & strFieldVal & "'"
ElseIf boolIsLong = True Then
QSelDelCorrTableField = QSelDelCorrTableField & _
"= " & CLng(strFieldVal)
End If
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDelDB2DMFilesDCTables
'
' Auswahl- oder
' Löschabfrage: Alle Datensätze der Tabelle tblDB2DMCMDCTables mit übereinstimmender
' DCID und übereinstimmendem Creator, gesteuert durch boolDelete,
' auswählen oder löschen.
'
' Parameter: lngDCID
' strCreator
' boolDelete
'
' Rückgabe: QSelDelDB2DMFilesDCTables
'-------------------------------------------------------------------------------------'
Function QSelDelDB2DMFilesDCTables(ByVal lngDCID As Long, _
ByVal strCreator As String, _
ByVal boolDelete As Boolean) As String
QSelDelDB2DMFilesDCTables = _
DelOrSel(boolDelete) & "tblDB2DMCMDCTables.* " & vbCrLf & _
"FROM tblDB2DMCMFiles " & vbCrLf & _
"INNER JOIN tblDB2DMCMDCTables " & vbCrLf & _
"ON tblDB2DMCMFiles.DCID = tblDB2DMCMDCTables.DCID " & vbCrLf & _
DCIDEQlngDCID(lngDCID, True, "tblDB2DMCMDCTables") & modPublDecl.gconSpace
If boolDelete = True Then
QSelDelDB2DMFilesDCTables = QSelDelDB2DMFilesDCTables & vbCrLf & _
"AND tblDB2DMCMDCTables.CreatorName Like '" & strCreator & "'"
ElseIf boolDelete = False Then
QSelDelDB2DMFilesDCTables = QSelDelDB2DMFilesDCTables & vbCrLf & _
"AND tblDB2DMCMDCTables.DCID Like tblDB2DMCMFiles.DCID " & vbCrLf & _
"ORDER BY tblDB2DMCMDCTables.CreatorName"
End If
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDelCorrDCID
'
' Auswahl- oder
' Löschabfrage: Alle Datensätze der Tabelle tblDB2DMCMFiles mit übereinstimmender
' DCID, gesteuert durch boolDelete, auswählen oder löschen.
'
' Parameter: lngDCID
' boolDelete
'
' Rückgabe: QSelDelCorrDCID
'-------------------------------------------------------------------------------------'
Public Function QSelDelCorrDCID(ByVal lngDCID As Long, _
ByVal boolDelete As Boolean) As String
QSelDelCorrDCID = _
DelOrSel(boolDelete) & "* " & vbCrLf & _
"FROM tblDB2DMCMFiles " & vbCrLf & _
DCIDEQlngDCID(lngDCID, True)
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QInsDCTablesDeleted
'
' Anfügeabfrage: Datensätze der Tabelle tblDB2DMCMDCTables mit
' übereinstimmender DCID an die Tabelle tblDB2DMCMDCTables
' anfügen.
'
' Parameter: lngDCID
'
' Rückgabe: QInsDCTablesDeleted
'-------------------------------------------------------------------------------------'
Public Function QInsDCTablesDeleted(ByVal lngDCID As Long) As String
QInsDCTablesDeleted = _
"INSERT INTO " & vbCrLf & _
"tblDB2DMCMDCTablesDeleted ( DelDCID, CreatorName ) " & vbCrLf & _
"SELECT DCID, CreatorName " & vbCrLf & _
"FROM tblDB2DMCMDCTables " & vbCrLf & _
DCIDEQlngDCID(lngDCID, True)
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDeletedDCID
'
' Auswahlabfrage: Alle Datensätze der Tabelle tblDB2DMCMDeleteLog mit
' übereinstimmender lngDCID auswählen.
'
' Parameter: lngDCID
'
' Rückgabe: QSelDeletedDCID
'-------------------------------------------------------------------------------------'
Public Function QSelDeletedDCID(ByVal lngDCID As Long) As String
QSelDeletedDCID = _
"SELECT DelDCID " & vbCrLf & _
"FROM tblDB2DMCMDeleteLog " & vbCrLf & _
"WHERE DelDCID=" & lngDCID
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelSaveKey
'
' Auswahlabfrage: Alle Datensätze der Tabelle tblDB2Tables mit übereinstimmendem
' CREATOR und übereinstimmender DCID auswählen.
'
' Parameter: lngDCID
' strDB2Env
'
' Rückgabe: QSelSaveKey
'-------------------------------------------------------------------------------------'
Public Function QSelSaveKey(ByVal lngDCID As Long, _
ByVal strDB2Env As String) As String
QSelSaveKey = _
"SELECT tblDB2DMCMFiles.DCID, " & vbCrLf & _
"Mid(CreatorName,InStr(CreatorName,'.')+1," & _
"Len(CreatorName)) AS Tabelle, " & vbCrLf & _
"tblDB2Tables.CREATOR, tblDB2Type.Default, tblDB2Tables.TYPE " & vbCrLf & _
"FROM tblDB2Type " & vbCrLf & _
"INNER JOIN tblDB2Tables " & vbCrLf & _
"ON tblDB2Type.DB2Type = tblDB2Tables.TYPE, tblDB2DMCMFiles " & vbCrLf & _
"INNER JOIN tblDB2DMCMDCTables " & vbCrLf & _
"ON tblDB2DMCMFiles.DCID = tblDB2DMCMDCTables.DCID " & vbCrLf & _
"WHERE (((tblDB2DMCMFiles.DCID)=" & lngDCID & ") " & vbCrLf & _
"AND ((tblDB2Tables.CREATOR) Like '" & strDB2Env & "') " & vbCrLf & _
"AND ((tblDB2Type.Default)=True) " & vbCrLf & _
"AND ((tblDB2Tables.TYPE) Like ([tblDB2Type].[DB2Type])) " & vbCrLf & _
"AND ((Mid([CreatorName],InStr([CreatorName],'.')+1," & _
"Len([CreatorName]))) Like [tblDB2Tables].[NAME]))"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDistTableField
'
' Auswahlabfrage: Eindeutige Datensätze der Tabelle strTable mit übereinstimmendem
' strField auswählen.
'
' Parameter: strTable
' strField
'
' Rückgabe: QSelDistTableField
'-------------------------------------------------------------------------------------'
Public Function QSelDistTableField(ByVal strTable As String, _
ByVal strField As String) As String
QSelDistTableField = _
"SELECT DISTINCT " & strField & modPublDecl.gconSpace & vbCrLf & _
"FROM " & strTable
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelCntKeyDelete
'
' Auswahlabfrage: Alle Datensätze der Tabelle tblDB2DMCMFiles mit übereinstimmender
' DCID auswählen.
'
' Parameter: lngDCID
'
' Rückgabe: QSelCntKeyDelete
'-------------------------------------------------------------------------------------'
Public Function QSelCntKeyDelete(ByVal lngDCID As Long) As String
QSelCntKeyDelete = _
"SELECT Count(DelDCID) AS NumRecs " & vbCrLf & _
"FROM tblDB2DMCMFiles " & vbCrLf & _
"INNER JOIN tblDB2DMCMDCTablesKeyDelete " & vbCrLf & _
"ON tblDB2DMCMFiles.DCID = tblDB2DMCMDCTablesKeyDelete.DCID " & vbCrLf & _
DCIDEQlngDCID(lngDCID, True, "tblDB2DMCMDCTablesKeyDelete")
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelCntDCIDKey
'
' Auswahlabfrage: Anzahl Datensätze der Tabelle tblDB2DMCMDCTablesKey mit
' übereinstimmender DCID ermitteln.
'
' Parameter: lngDCID
'
' Rückgabe: QSelCntDCIDKey
'-------------------------------------------------------------------------------------'
Public Function QSelCntDCIDKey(ByVal lngDCID As Long) As String
QSelCntDCIDKey = _
"SELECT Count(DCID) AS NumRecs " & vbCrLf & _
"FROM tblDB2DMCMDCTablesKey " & vbCrLf & _
DCIDEQlngDCID(lngDCID, True)
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelFindUser
'
' Auswahlabfrage: UNION Abfrage der NTUserID in den Tabellen tblDB2DMCMFiles und
' tblDB2DMCMDeleteLog mit übereinstimmender Windows User ID.
'
' Parameter: strWinUserID
'
' Rückgabe: QSelFindUser
'-------------------------------------------------------------------------------------'
Public Function QSelFindUser(ByVal strWinUserID As String) As String
QSelFindUser = _
QSelTblFldOrderByFld("tblDB2DMCMFiles", _
"NTUserID", _
vbNullString) & vbCrLf & _
NTUserIDEQstrWinUserID(strWinUserID, _
True, _
False, _
True, _
True)
QSelFindUser = QSelFindUser & _
"UNION " & vbCrLf & _
QSelTblFldOrderByFld("tblDB2DMCMDeleteLog", _
"NTUserID", _
vbNullString) & vbCrLf & _
NTUserIDEQstrWinUserID(strWinUserID, _
True, _
False, _
False, _
False)
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QInsJCLTables
'
' Anfügeabfrage: Umgebung.Tabelle in die Tabelle tblJCLTables mit übereinstimmender
' DB2 Umgebung (strDB2Env) anfügen.
'
' Parameter: strDB2Env
'
' Rückgabe: QInsJCLTables
'-------------------------------------------------------------------------------------'
Public Function QInsJCLTables(ByVal strDB2Env As String) As String
QInsJCLTables = _
"INSERT INTO tblJCLTables ( UmgebTabelle ) " & vbCrLf & _
"SELECT DISTINCT '" & strDB2Env & "' & " & "'.' & " & vbCrLf & _
"Mid(UmgebTabelle,InStr(1,UmgebTabelle,'.')+1, " & vbCrLf & _
"Len(UmgebTabelle)) AS UmgebTabelleConcat " & vbCrLf & _
"FROM tblJCLLoadFileTableAttribs " & vbCrLf & _
"ORDER BY '" & strDB2Env & "' & '.' & Mid(UmgebTabelle, " & _
"InStr(1,UmgebTabelle,'.')+1,Len(UmgebTabelle))"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelJCLDelKey
'
' Auswahlabfrage: Sätze der Tabelle tblDB2DMCMFiles mit übereinstimmender
' DCID auswählen.
'
' Parameter: lngDCID
'
' Rückgabe: QSelJCLDelKey
'-------------------------------------------------------------------------------------'
Public Function QSelJCLDelKey(ByVal lngDCID As Long) As String
QSelJCLDelKey = _
"SELECT tblDB2DMCMFiles.DCUmgebung, " & vbCrLf & _
"tblDB2DMCMDCTablesKeyDelete.Tabelle, " & vbCrLf & _
"tblDB2DMCMDCTablesKeyDelete.DB2KeyVal, " & vbCrLf & _
"tblDB2DMCMDCTablesKeyDelete.DCID " & vbCrLf & _
"FROM tblDB2DMCMFiles " & vbCrLf & _
"INNER JOIN tblDB2DMCMDCTablesKeyDelete " & vbCrLf & _
"ON tblDB2DMCMFiles.DCID = tblDB2DMCMDCTablesKeyDelete.DCID " & vbCrLf & _
DCIDEQlngDCID(lngDCID, True, "tblDB2DMCMDCTablesKeyDelete")
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelCreatorName
'
' Auswahlabfrage: CREATOR und NAME der Tabelle strTable, bei welchen strField den
' Feldinhalt strFieldVal besitzt, auswählen.
' Falls boolOrderBy = True ist, wird nach dem Feld NAME aufsteigend
' sortiert.
'
' Parameter: strTable
' strField
' strFieldVal
' boolOrderBy
'
' Rückgabe: QSelCreatorName
'-------------------------------------------------------------------------------------'
Public Function QSelCreatorName(ByVal strTable As String, _
ByVal strField As String, _
ByVal strFieldVal As String, _
ByVal boolOrderBy As Boolean) As String
QSelCreatorName = _
"SELECT CREATOR, NAME " & vbCrLf & _
"FROM " & strTable & modPublDecl.gconSpace & vbCrLf & _
"WHERE " & strField & " Like '" & strFieldVal & "'"
If boolOrderBy = True Then
QSelCreatorName = QSelCreatorName & modPublDecl.gconSpace & vbCrLf & _
"ORDER BY NAME"
End If
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelTableAttribCompare
'
' Auswahlabfrage: Ermittelt mittels UNION Abfrage der Tabellen tblSysIBMTableAttribs
' und tblJCLLoadFileTableAttribs die Anzahl der Nichtübereinstimmenden
' Attribute.
'
' Parameter: strUmgebTabelle
'
' Rückgabe: QSelTableAttribCompare
'-------------------------------------------------------------------------------------'
Public Function QSelTableAttribCompare(ByVal strUmgebTabelle As String) As String
QSelTableAttribCompare = _
"SELECT tblSysIBMTableAttribs.Attribut " & vbCrLf & _
"FROM tblSysIBMTableAttribs " & vbCrLf & _
"WHERE tblSysIBMTableAttribs.Attribut " & vbCrLf & _
"Not In (SELECT tblJCLLoadFileTableAttribs.Attribut " & vbCrLf & _
"FROM tblJCLLoadFileTableAttribs) " & vbCrLf
QSelTableAttribCompare = QSelTableAttribCompare & _
"UNION " & vbCrLf & _
"SELECT tblJCLLoadFileTableAttribs.Attribut " & vbCrLf & _
"FROM tblJCLLoadFileTableAttribs " & vbCrLf & _
"WHERE tblJCLLoadFileTableAttribs.Attribut " & vbCrLf & _
"Not In (SELECT tblSysIBMTableAttribs.Attribut " & vbCrLf & _
"FROM tblSysIBMTableAttribs) " & vbCrLf & _
"AND tblJCLLoadFileTableAttribs.UmgebTabelle Like '" & strUmgebTabelle & "'"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelEnvTablesAttribs
'
' Auswahlabfrage: Alle Sätze der Tabelle modPublDecl.gconAccIBMSysCols mit
' übereinstimmendem TBCREATOR und TBNAME auswählen.
'
' Parameter: strDB2Env
' strTable
'
' Rückgabe: QSelEnvTablesAttribs
'-------------------------------------------------------------------------------------'
Public Function QSelEnvTablesAttribs(ByVal strDB2Env As String, _
ByVal strTable As String) As String
QSelEnvTablesAttribs = _
"SELECT NAME AS Attribut, TBNAME " & vbCrLf & _
"FROM " & modPublDecl.gconAccIBMSysCols & modPublDecl.gconSpace & vbCrLf & _
"WHERE Trim(TBCREATOR) Like '" & strDB2Env & "' " & vbCrLf & _
"AND Trim(TBNAME) Like '" & strTable & "' " & vbCrLf & _
"ORDER BY COLNO"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDistTBNAME
'
' Auswahlabfrage: Eindeutigen Tabellennamen der Tabelle modPublDecl.gconAccIBMSysCols
' mit übereinstimmender Umgebung (strDB2EnvFrom) oder optional
' übereinstimmender Umgebung (strDB2EnvTo), gesteuert durch boolOr,
' auswählen.
'
' Parameter: boolOr
' strDB2EnvFrom
' strDB2EnvTo (Optional)
'
' Rückgabe: QSelDistTBNAME
'-------------------------------------------------------------------------------------'
Public Function QSelDistTBNAME(ByVal boolOr As Boolean, _
ByVal strDB2EnvFrom As String, _
Optional ByVal strDB2EnvTo As String) As String
QSelDistTBNAME = _
"SELECT DISTINCT TBNAME " & vbCrLf & _
"FROM " & modPublDecl.gconAccIBMSysCols & modPublDecl.gconSpace & vbCrLf & _
"WHERE Trim(TBCREATOR) Like '" & strDB2EnvFrom & "' "
If boolOr = True Then
QSelDistTBNAME = QSelDistTBNAME & vbCrLf & _
"OR Trim(TBCREATOR) Like '" & strDB2EnvTo & "'"
End If
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelCntSysIBMAttribs
'
' Auswahlabfrage: Anzahl Sätze der Tabelle modPublDecl.gconAccIBMSysCols mit
' übereinstimmendem TBCREATOR und TBNAME ermitteln.
'
' Parameter: strDB2Env
' strTable
'
' Rückgabe: QSelCntSysIBMAttribs
'-------------------------------------------------------------------------------------'
Public Function QSelCntSysIBMAttribs(ByVal strDB2Env As String, _
ByVal strTable As String) As String
QSelCntSysIBMAttribs = _
"SELECT Count(NAME) AS NumRecs " & vbCrLf & _
"FROM " & modPublDecl.gconAccIBMSysCols & modPublDecl.gconSpace & vbCrLf & _
"HAVING Trim(TBCREATOR) Like '" & strDB2Env & "' " & vbCrLf & _
"AND Trim(TBNAME) Like '" & strTable & "'"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelSysCreatsColsNames
'
' Auswahlabfrage: Alle Sätze der Tabelle modPublDecl.gconAccIBMSysCols mit überein-
' stimmendem TBCREATOR und TBNAME auswählen.
'
' Parameter: strDB2Env
' strTable
'
' Rückgabe: QSelSysCreatsColsNames
'-------------------------------------------------------------------------------------'
Public Function QSelSysCreatsColsNames(ByVal strDB2Env As String, _
ByVal strTable As String) As String
QSelSysCreatsColsNames = _
"SELECT NAME, TBNAME " & vbCrLf & _
"FROM " & modPublDecl.gconAccIBMSysCols & modPublDecl.gconSpace & vbCrLf & _
"WHERE Trim(TBCREATOR) Like '" & strDB2Env & "' " & vbCrLf & _
"AND Trim(TBNAME) Like '" & strTable & "' " & vbCrLf & _
"ORDER BY NAME"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelHostIdDB2DMFiles
'
' Auswahlabfrage: Ermittelt die Daten für den Bericht modPublDecl.gconRepDC.
'
' Parameter: strWorkGroup
' strNTUserID
'
' Rückgabe: QSelHostIdDB2DMFiles
'-------------------------------------------------------------------------------------'
Public Function QSelHostIdDB2DMFiles(ByVal strWorkGroup As String, _
ByVal strNTUserID As String) As String
QSelHostIdDB2DMFiles = _
"SELECT tblDB2DMCMFiles.*, tblDB2DMCMUnloadLog.EntladenOK " & vbCrLf & _
"FROM tblDB2DMUserGroup " & vbCrLf & _
"INNER JOIN (tblDB2DMUserProfile " & vbCrLf & _
"INNER JOIN (tblDB2DMCMFiles " & vbCrLf & _
"LEFT JOIN tblDB2DMCMUnloadLog " & vbCrLf & _
"ON tblDB2DMCMFiles.EntladeDateTime = tblDB2DMCMUnloadLog.EntladeDateTime) " & vbCrLf & _
"ON tblDB2DMUserProfile.NTUserID = tblDB2DMCMFiles.NTUserID) " & vbCrLf & _
"ON tblDB2DMUserGroup.ProfileGroup = tblDB2DMUserProfile.ProfileGroup " & vbCrLf & _
"WHERE tblDB2DMCMFiles.NTUserID Like '" & strNTUserID & "' " & vbCrLf & _
"AND tblDB2DMUserProfile.ProfileGroup Like '" & strWorkGroup & "'"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDB2DMFilesTablesDCID
'
' Auswahlabfrage: Alle Tabellen der Tabelle tblDB2DMCMFiles mit übereinstimmender
' DCID auswählen.
'
' Parameter: lngDCID
'
' Rückgabe: QSelDB2DMFilesTablesDCID
'-------------------------------------------------------------------------------------'
Public Function QSelDB2DMFilesTablesDCID(ByVal lngDCID As Long) As String
QSelDB2DMFilesTablesDCID = _
"SELECT DCID, " & vbCrLf & _
"Mid(CreatorName,InStr(1,CreatorName,'.')+1," & vbCrLf & _
"Len(CreatorName)) AS Name, " & vbCrLf & _
"Mid(CreatorName,1,InStr(1,CreatorName,'.')-1) AS Creator " & vbCrLf & _
"FROM tblDB2DMCMDCTables " & vbCrLf & _
DCIDEQlngDCID(lngDCID, True) & _
"ORDER BY Mid(CreatorName,InStr(1,CreatorName,'.')+1,Len(CreatorName))"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelCorrTblFldNotDefault
'
' Auswahlabfrage: Sätze der Tabelle strTable bei übereinstimmendem/nicht
' übereinstimmendem strField und strFieldVal, gesteuert durch
' boolDefault, boolNot und boolOrderBy, auswählen.
'
' Parameter: strTable
' strField
' strFieldVal
' boolNot
' boolDefault
' boolOrderBy
'
' Rückgabe: QSelCorrTblFldNotDefault
'-------------------------------------------------------------------------------------'
Public Function QSelCorrTblFldNotDefault(ByVal strTable As String, _
ByVal strField As String, _
ByVal strFieldVal As String, _
ByVal boolNot As Boolean, _
ByVal boolDefault As Boolean, _
ByVal boolOrderBy As Boolean) As String
QSelCorrTblFldNotDefault = _
QSelDelTable(strTable, _
False) & modPublDecl.gconSpace
If boolDefault = True Then
QSelCorrTblFldNotDefault = QSelCorrTblFldNotDefault & vbCrLf & _
"WHERE Default=True " & vbCrLf & _
"AND " & strField & modPublDecl.gconSpace & _
NotOrNull(boolNot) & "Like '" & strFieldVal & "' "
ElseIf boolDefault = False Then
QSelCorrTblFldNotDefault = QSelCorrTblFldNotDefault & vbCrLf & _
"WHERE " & strField & modPublDecl.gconSpace & _
NotOrNull(boolNot) & "Like '" & strFieldVal & "' "
End If
If boolOrderBy = True Then
QSelCorrTblFldNotDefault = QSelCorrTblFldNotDefault & vbCrLf & _
"ORDER BY " & strField
End If
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QUpdDB2EnvCfg
'
' Aktualisierungs-
' abfrage: Falls DB2Env der Tabelle tblDB2EnvCfg nicht als CREATOR der Tabelle
' tblDB2Tables vorhanden ist, das Feld EnvAvailable der Tabelle
' tblDB2EnvCfg auf False setzen.
'
' Parameter: boolNot
' boolEnvAvailable
' boolWhere
'
' Rückgabe: QUpdDB2EnvCfg
'-------------------------------------------------------------------------------------'
Public Function QUpdDB2EnvCfg(ByVal boolNot As Boolean, _
ByVal boolEnvAvailable As Boolean, _
ByVal boolWhere As Boolean) As String
Dim strAvailable As String
strAvailable = IIf(boolEnvAvailable = True, "True", "False")
QUpdDB2EnvCfg = _
"UPDATE tblDB2EnvCfg " & vbCrLf & _
"SET tblDB2EnvCfg.EnvAvailable = " & strAvailable
If boolWhere = True Then
QUpdDB2EnvCfg = QUpdDB2EnvCfg & vbCrLf & _
" WHERE tblDB2EnvCfg.DB2Env " & _
NotOrNull(boolNot) & "In (SELECT DISTINCT CREATOR FROM tblDB2Tables)"
End If
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDB2EnvCfg
'
' Auswahlabfrage: Sätze der Tabelle tblDB2EnvCfg bei übereinstimmender DB2 Umgebung
' (DB2Env), gesteuert durch boolEnvUsageOK, auswählen.
'
' Parameter: strDB2Env
' boolEnvUsageOK
'
' Rückgabe: QSelDB2EnvCfg
'-------------------------------------------------------------------------------------'
Public Function QSelDB2EnvCfg(ByVal strDB2Env As String, _
ByVal boolEnvUsageOK As Boolean) As String
QSelDB2EnvCfg = _
"SELECT DB2Env, EnvAvailable, EnvUsageOK, EnvUsed " & vbCrLf & _
"FROM tblDB2EnvCfg " & vbCrLf & _
"WHERE Trim(DB2Env) Like '" & strDB2Env & "' "
If boolEnvUsageOK = True Then
QSelDB2EnvCfg = QSelDB2EnvCfg & vbCrLf & _
"AND EnvUsageOK=True"
End If
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelTblFldDefault
'
' Auswahlabfrage: Aus der Tabelle strTable das Feld strField und das Feld Default
' (Parameter: boolDefault) auslesen.
'
' Parameter: strTable
' strField
' boolDefault
'
' Rückgabe: QSelTblFldDefault
'-------------------------------------------------------------------------------------'
Public Function QSelTblFldDefault(ByVal strTable As String, _
ByVal strField As String, _
ByVal boolDefault As Boolean) As String
QSelTblFldDefault = _
"SELECT " & strField & modPublDecl.gconSpace & vbCrLf & _
"FROM " & strTable & " WHERE Default=" & CInt(boolDefault)
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDistDB2TableName
'
' Auswahlabfrage: Eindeutigen DB2 Tabellennamen mit übereinstimmendem TYPE und
' CREATOR auslesen.
'
' Parameter: strDB2Env
'
' Rückgabe: QSelDistDB2TableName
'-------------------------------------------------------------------------------------'
Public Function QSelDistDB2TableName(ByVal strDB2Env As String) As String
QSelDistDB2TableName = _
"SELECT DISTINCT tblDB2Tables.NAME " & vbCrLf & _
"FROM tblDB2Type " & vbCrLf & _
"INNER JOIN tblDB2Tables " & vbCrLf & _
"ON tblDB2Type.DB2Type = tblDB2Tables.TYPE " & vbCrLf & _
"WHERE Trim(tblDB2Tables.CREATOR) Like '" & Trim(strDB2Env) & "' " & vbCrLf & _
"AND tblDB2Type.Default=True"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelKeyField
'
' Auswahlabfrage: Schlüsselfeld der Tabelle strTable der DB2 Umgebung strDB2Env
' auswählen.
'
' Parameter: strDB2Env
' strTable
'
' Rückgabe: QSelKeyField
'-------------------------------------------------------------------------------------'
Public Function QSelKeyField(ByVal strDB2Env As String, _
ByVal strTable As String) As String
QSelKeyField = _
"SELECT NAME AS Attribut, COLTYPE AS Datentyp, LENGTH AS Feldgroesse " & vbCrLf & _
"FROM " & modPublDecl.gconAccIBMSysCols & modPublDecl.gconSpace & vbCrLf & _
"WHERE Trim(TBCREATOR) Like '" & Trim(strDB2Env) & "' " & vbCrLf & _
"AND Trim(TBNAME) Like '" & Trim(strTable) & "' " & vbCrLf & _
"AND COLTYPE In ('CHAR','DECIMAL','FLOAT','INTEGER','SMALLINT') " & vbCrLf & _
"AND LENGTH<=20 " & vbCrLf & _
"ORDER BY NAME"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDistDelDCID
'
' Auswahlabfrage: Eindeutige DelDCID mit übereinstimmender DCID der Tabelle
' tblDB2DMCMDCTablesKeyDelete und UNION '(Alle)' auswählen.
'
' Parameter: strDelDCID
'
' Rückgabe: QSelDistDelDCID
'-------------------------------------------------------------------------------------'
Public Function QSelDistDelDCID(ByVal strDelDCID As String) As String
QSelDistDelDCID = _
"SELECT DISTINCT Format(DelDCID, '" & modPublDecl.gconlngDCIDFmt & _
"') AS DCID " & vbCrLf & _
"FROM tblDB2DMCMDCTablesKeyDelete " & vbCrLf & _
"WHERE DCID Like '" & strDelDCID & "' " & vbCrLf
QSelDistDelDCID = QSelDistDelDCID & _
UnionSelAll(True, _
"DCID", _
"tblDB2DMCMDCTablesKeyDelete", _
"DCID")
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDistDelKeyDB2KeyVal
'
' Auswahlabfrage: Eindeutigen Schlüsselwert und UNION '(Alle)' auswählen.
'
' Parameter: lngDCID
' strDelDCID
' strDelTabelle
'
' Rückgabe: QSelDistDelKeyDB2KeyVal
'-------------------------------------------------------------------------------------'
Public Function QSelDistDelKeyDB2KeyVal(ByVal lngDCID As Long, _
ByVal strDelDCID As String, _
ByVal strDelTabelle As String) As String
QSelDistDelKeyDB2KeyVal = _
"SELECT DISTINCT DB2KeyVal AS KeyVal " & vbCrLf & _
"FROM tblDB2DMCMDCTablesKeyDelete " & vbCrLf & _
DCIDEQlngDCID(lngDCID, True) & _
"AND Format(DelDCID, '" & modPublDecl.gconlngDCIDFmt & "') Like '" & _
strDelDCID & "' " & vbCrLf & _
"AND Tabelle Like '" & strDelTabelle & "' " & vbCrLf
QSelDistDelKeyDB2KeyVal = QSelDistDelKeyDB2KeyVal & _
UnionSelAll(True, _
"KeyVal", _
"tblDB2DMCMDCTablesKeyDelete", _
"KeyVal")
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDCIDDelDCID
'
' Auswahlabfrage: Alle Datensätze der Tabelle strDetailTable mit übereinstimmendem
' strField in der strMasterTable auswählen.
'
' Parameter: strMasterTable
' strDetailTable
' strField
' lngDCID
'
' Rückgabe: QSelDCIDDelDCID
'-------------------------------------------------------------------------------------'
Public Function QSelDCIDDelDCID(ByVal strMasterTable As String, _
ByVal strDetailTable As String, _
ByVal strField As String, _
ByVal lngDCID As Long) As String
QSelDCIDDelDCID = _
"SELECT " & strDetailTable & modPublDecl.gconFullStop & strField & ", " & _
strDetailTable & ".CreatorName " & vbCrLf & _
"FROM " & strDetailTable & modPublDecl.gconSpace & vbCrLf & _
"INNER JOIN " & strMasterTable & modPublDecl.gconSpace & vbCrLf & _
"ON " & strMasterTable & modPublDecl.gconFullStop & strField & "=" & _
strDetailTable & modPublDecl.gconFullStop & strField & modPublDecl.gconSpace & vbCrLf & _
"WHERE " & strMasterTable & modPublDecl.gconFullStop & strField & "=" & lngDCID
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QInsDB2Env
'
' Anfügeabfrage: Fehlende DB2 Umgebung aus Tabelle tblDB2Tables an die Tabelle
' tblDB2EnvCfg anfügen.
'
' Parameter: Ohne
'
' Rückgabe: QInsDB2Env
'-------------------------------------------------------------------------------------'
Public Function QInsDB2Env() As String
QInsDB2Env = _
"INSERT INTO tblDB2EnvCfg ( DB2Env ) " & vbCrLf & _
"SELECT DISTINCT tblDB2Tables.CREATOR " & vbCrLf & _
"FROM tblDB2Tables " & vbCrLf & _
"WHERE tblDB2Tables.CREATOR " & vbCrLf & _
"Not In (SELECT DISTINCT DB2Env FROM tblDB2EnvCfg)"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelCorrUmgebTabelle
'
' Auswahlabfrage: Übereinstimmende/nicht übereinstimmende Umgebung.Tabelle(n),
' gesteuert durch boolNot, ermitteln.
'
' Parameter: boolNot
'
' Rückgabe: QSelCorrUmgebTabelle
'-------------------------------------------------------------------------------------'
Public Function QSelCorrUmgebTabelle(ByVal boolNot As Boolean)
QSelCorrUmgebTabelle = _
"SELECT DISTINCT UmgebTabelle " & vbCrLf & _
"FROM tblJCLLoadFileTableAttribs " & vbCrLf & _
"WHERE UmgebTabelle " & vbCrLf & _
IIf(boolNot = True, "Not ", vbNullString) & _
"In (SELECT DISTINCT UmgebTabelle From tblSYSIBMTableAttribs)"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelTableAttribs
'
' Auswahlabfrage: Fehlende Übereinstimmung(en) der Attribute Umgebung.Tabelle des/der
' Load Files (Tabelle tblJCLLoadFileTableAttribs) und der DB2 Umgebung
' und DB2 Tabelle (Tabelle tblSysIBMTableAttribs) ermitteln.
'
' Parameter: strDB2EnvTable
'
' Rückgabe: QSelTableAttribs
'-------------------------------------------------------------------------------------'
Public Function QSelTableAttribs(ByVal strDB2EnvTable As String) As String
QSelTableAttribs = _
"SELECT UmgebTabelle, Attribut " & vbCrLf & _
"FROM tblSysIBMTableAttribs " & vbCrLf & _
"WHERE UmgebTabelle Like '" & strDB2EnvTable & "' " & vbCrLf & _
"AND Attribut " & vbCrLf & _
"Not In (SELECT Attribut From tblJCLLoadFileTableAttribs " & vbCrLf & _
"WHERE UmgebTabelle Like '" & strDB2EnvTable & "') " & vbCrLf
QSelTableAttribs = QSelTableAttribs & _
"UNION " & vbCrLf & _
"SELECT UmgebTabelle, Attribut " & vbCrLf & _
"FROM tblJCLLoadFileTableAttribs " & vbCrLf & _
"WHERE UmgebTabelle Like '" & strDB2EnvTable & "' " & vbCrLf & _
"AND Attribut " & vbCrLf & _
"Not In (SELECT Attribut From tblSysIBMTableAttribs " & vbCrLf & _
"WHERE UmgebTabelle Like '" & strDB2EnvTable & "')"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDistTeam
'
' Auswahlabfrage: UNION Abfrage zur Auswahl der Arbeitsgruppe.
'
' Parameter: Ohne
'
' Rückgabe: QSelDistTeam
'-------------------------------------------------------------------------------------'
Public Function QSelDistTeam() As String
QSelDistTeam = _
"SELECT DISTINCT ProfileGroup " & vbCrLf & _
"FROM tblDB2DMUserProfile " & vbCrLf & _
"INNER JOIN tblDB2DMLogonInfo " & vbCrLf & _
"ON tblDB2DMUserProfile.NTUserID = tblDB2DMLogonInfo.NTUserID " & vbCrLf
QSelDistTeam = QSelDistTeam & _
UnionSelAll(False, _
vbNullString, _
"tblDB2DMUserProfile", _
"ProfileGroup")
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelHistory
'
' Auswahlabfrage: UNION Abfrage zur Auswahl der Historie der erfolgreichen Entlade-,
' Lade- und Löschvorgänge aktuell verwendeter Data Collections und
' DB2 Umgebungen.
'
' Parameter: strWorkGroup
' strWinUserID
' strDB2Env
' dateFrom
' dateTo
'
' Rückgabe: QSelHistory
'-------------------------------------------------------------------------------------'
Public Function QSelHistory(ByVal strWorkGroup As String, _
ByVal strWinUserID As String, _
ByVal strDB2Env As String, _
ByVal dateFrom As Date, _
ByVal dateTo As Date) As String
QSelHistory = _
"SELECT tblDB2DMCMFiles.DCUmgebung AS DB2Env, tblDB2DMCMUnloadLog.NTUserID, " & vbCrLf & _
"tblDB2DMUserProfile.ProfileGroup, " & vbCrLf & _
"tblDB2DMCMUnloadLog.EntladeDateTime AS JobRunDateTime, JCLTime, " & vbCrLf & _
"'Unload' AS JobType, " & vbCrLf & _
"Format(tblDB2DMCMFiles.DCID,'" & modPublDecl.gconlngDCIDFmt & _
"') AS LoadUnloadDCID " & vbCrLf & _
"FROM tblDB2DMUserProfile " & vbCrLf & _
"INNER JOIN (tblDB2DMCMFiles " & vbCrLf & _
"INNER JOIN tblDB2DMCMUnloadLog " & vbCrLf & _
"ON tblDB2DMCMFiles.DCID = tblDB2DMCMUnloadLog.DCID) " & vbCrLf & _
"ON tblDB2DMUserProfile.NTUserID = tblDB2DMCMFiles.NTUserID " & vbCrLf & _
"WHERE Format([tblDB2DMCMUnloadLog.EntladeDateTime],'" & _
modPublDecl.gconEurDate & "')>=#" & Format(dateFrom, "Short Date") & "# " & vbCrLf & _
"AND Format([tblDB2DMCMUnloadLog.EntladeDateTime],'" & _
modPublDecl.gconEurDate & "')<=#" & Format(dateTo, "Short Date") & "# " & vbCrLf & _
"AND tblDB2DMCMUnloadLog.EntladenOK=True " & vbCrLf & _
"AND Trim([DCUmgebung]) Like '" & strDB2Env & "' " & vbCrLf & _
"AND tblDB2DMUserProfile.ProfileGroup Like '" & strWorkGroup & "' " & vbCrLf & _
NTUserIDEQstrWinUserID(strWinUserID, _
False, _
False, _
True, _
True, _
"tblDB2DMUserProfile")
QSelHistory = QSelHistory & QAppendSelHistory(strWorkGroup, _
strWinUserID, _
strDB2Env, _
dateFrom, _
dateTo, _
"tblDB2DMCMLoadLog", _
"LadeUmgebung", _
"LadeDateTime", _
"LadenOK", _
"Load", _
"tblDB2DMCMLoadLog.DCID")
QSelHistory = QSelHistory & QAppendSelHistory(strWorkGroup, _
strWinUserID, _
strDB2Env, _
dateFrom, _
dateTo, _
"tblDB2EnvClearLog", _
"Umgebung", _
"LoeschDateTime", _
"GeloeschtOK", _
"Clear", _
"''") & _
"ORDER BY DB2Env, JobRunDateTime DESC"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QAppendSelHistory
'
' Auswahlabfrage: Erweitert die UNION Abfrage QSelHistory um die übergebene Tabelle
' und die übergebenen Attribute.
'
' Parameter: strWorkGroup
' strWinUserID
' strDB2Env
' dateFrom
' dateTo
' strTable1
' strField1
' strField2
' strField3
' strJobType
' strFormatTableField
'
' Rückgabe: QAppendSelHistory
'-------------------------------------------------------------------------------------'
Private Function QAppendSelHistory(ByVal strWorkGroup As String, _
ByVal strWinUserID As String, _
ByVal strDB2Env As String, _
ByVal dateFrom As Date, _
ByVal dateTo As Date, _
ByVal strTable1 As String, _
ByVal strField1 As String, _
ByVal strField2 As String, _
ByVal strField3 As String, _
ByVal strJobType As String, _
ByVal strFormatTableField As String) As String
QAppendSelHistory = _
"UNION " & vbCrLf & _
"SELECT " & strTable1 & "." & strField1 & " AS DB2Env, " & _
strTable1 & ".NTUserID, " & vbCrLf & _
"tblDB2DMUserProfile.ProfileGroup, " & vbCrLf & _
strTable1 & "." & strField2 & " AS JobRunDateTime, JCLTime, " & vbCrLf & _
"'" & strJobType & "' AS JobType, " & vbCrLf & _
"Format(" & strFormatTableField & ",'" & modPublDecl.gconlngDCIDFmt & _
"') AS LoadUnloadDCID " & vbCrLf & _
"FROM " & strTable1 & " " & vbCrLf & _
"INNER JOIN tblDB2DMUserProfile " & vbCrLf & _
"ON " & strTable1 & ".NTUserID = tblDB2DMUserProfile.NTUserID " & vbCrLf & _
"WHERE Format([" & strTable1 & "." & strField2 & "],'" & _
modPublDecl.gconEurDate & "')>=#" & Format(dateFrom, "Short Date") & "# " & vbCrLf & _
"AND " & strTable1 & "." & strField3 & "=True " & vbCrLf & _
"AND Format([" & strTable1 & "." & strField2 & "],'" & _
modPublDecl.gconEurDate & "')<=#" & Format(dateTo, "Short Date") & "# " & vbCrLf & _
"AND Trim([" & strField1 & "]) Like '" & strDB2Env & "' " & vbCrLf & _
"AND tblDB2DMUserProfile.ProfileGroup Like '" & strWorkGroup & "' " & vbCrLf & _
NTUserIDEQstrWinUserID(strWinUserID, _
False, _
False, _
True, _
True, _
"tblDB2DMUserProfile")
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelWGDB2EnvUsed
'
' Auswahlabfrage: Abfrage zur Auswahl der von der Arbeitsgruppe strWorkGroup
' verwendeten DB2 Umgebungen und UNION '(Alle)'.
'
' Parameter: strWorkGroup
' strWinUserID
' strDB2Env
'
' Rückgabe: QSelWGDB2EnvUsed
'-------------------------------------------------------------------------------------'
Public Function QSelWGDB2EnvUsed(ByVal strWorkGroup As String, _
ByVal strWinUserID As String, _
ByVal strDB2Env As String) As String
QSelWGDB2EnvUsed = _
"SELECT DISTINCT tblDB2DMCMFiles.DCUmgebung AS DB2Env " & vbCrLf & _
"FROM tblDB2DMUserProfile " & vbCrLf & _
"INNER JOIN (tblDB2DMCMFiles " & vbCrLf & _
"INNER JOIN tblDB2DMCMUnloadLog " & vbCrLf & _
"ON tblDB2DMCMFiles.DCID = tblDB2DMCMUnloadLog.DCID) " & vbCrLf & _
"ON tblDB2DMUserProfile.NTUserID = tblDB2DMCMFiles.NTUserID " & vbCrLf & _
"WHERE tblDB2DMUserProfile.ProfileGroup Like '" & strWorkGroup & "' " & vbCrLf & _
"AND tblDB2DMCMUnloadLog.EntladenOK=True " & vbCrLf & _
"AND Trim([DCUmgebung]) Like '" & strDB2Env & "' " & vbCrLf & _
"AND Trim([tblDB2DMCMUnloadLog.NTUserID]) Like '" & strWinUserID & "' " & vbCrLf
QSelWGDB2EnvUsed = QSelWGDB2EnvUsed & QAppendSelWGDB2EnvUsed(strWorkGroup, _
strWinUserID, _
strDB2Env, _
"tblDB2DMCMLoadLog", _
"LadeUmgebung", _
"LadenOK")
QSelWGDB2EnvUsed = QSelWGDB2EnvUsed & QAppendSelWGDB2EnvUsed(strWorkGroup, _
strWinUserID, _
strDB2Env, _
"tblDB2EnvClearLog", _
"Umgebung", _
"GeloeschtOK") & _
UnionSelAll(False, _
vbNullString, _
"tblDB2DMUserProfile", _
"DB2Env")
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QAppendSelWGDB2EnvUsed
'
' Auswahlabfrage: Erweiter die Abfrage QSelWGDB2EnvUsed um die übergebene Tabelle
' und die übergebenen Attribute.
'
' Parameter: strWorkGroup
' strWinUserID
' strDB2Env
' strTable
' strField1
' strField2
'
' Rückgabe: QAppendSelWGDB2EnvUsed
'-------------------------------------------------------------------------------------'
Private Function QAppendSelWGDB2EnvUsed(ByVal strWorkGroup As String, _
ByVal strWinUserID As String, _
ByVal strDB2Env As String, _
ByVal strTable As String, _
ByVal strField1 As String, _
ByVal strField2 As String) As String
QAppendSelWGDB2EnvUsed = _
"UNION " & vbCrLf & _
"SELECT DISTINCT " & strTable & "." & strField1 & " AS DB2Env " & vbCrLf & _
"FROM " & strTable & " " & vbCrLf & _
"INNER JOIN tblDB2DMUserProfile " & vbCrLf & _
"ON " & strTable & ".NTUserID = tblDB2DMUserProfile.NTUserID " & vbCrLf & _
"WHERE tblDB2DMUserProfile.ProfileGroup Like '" & strWorkGroup & "' " & vbCrLf & _
"AND " & strTable & "." & strField2 & "=True " & vbCrLf & _
"AND Trim([" & strField1 & "]) Like '" & strDB2Env & "' " & vbCrLf & _
"AND Trim([" & strTable & ".NTUserID]) Like '" & strWinUserID & "' " & vbCrLf
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelMinMaxDate
'
' Auswahlabfrage: Abfrage des kleinsten/größten Joblaufdatums der ausgewählten DB2
' Umgebung(en), gesteuert durch boolMin.
'
' Parameter: strDB2Env
' strWinUserID
' strWorkGroup
' boolMin
'
' Rückgabe: QSelMinMaxDate
'-------------------------------------------------------------------------------------'
Public Function QSelMinMaxDate(ByVal strDB2Env As String, _
ByVal strWinUserID As String, _
ByVal strWorkGroup As String, _
ByVal boolMin As Boolean) As String
Dim strMinMax As String
Dim strDescAsc As String
strMinMax = IIf(boolMin = True, "Min", "Max")
strDescAsc = IIf(boolMin = True, "ASC", "DESC")
QSelMinMaxDate = _
"SELECT TOP 1 " & vbCrLf & _
"Format(" & strMinMax & "(tblDB2DMCMUnloadLog.EntladeDateTime),'" & _
modPublDecl.gconEurDate & "') AS JobDate " & vbCrLf & _
"FROM tblDB2DMUserProfile " & vbCrLf & _
"INNER JOIN (tblDB2DMCMFiles " & vbCrLf & _
"INNER JOIN tblDB2DMCMUnloadLog " & vbCrLf & _
"ON tblDB2DMCMFiles.DCID = tblDB2DMCMUnloadLog.DCID) " & vbCrLf & _
"ON (tblDB2DMCMUnloadLog.NTUserID = tblDB2DMUserProfile.NTUserID) " & vbCrLf & _
"AND (tblDB2DMUserProfile.NTUserID = tblDB2DMCMFiles.NTUserID) " & vbCrLf & _
"GROUP BY Trim([DCUmgebung]), 'Unload', tblDB2DMCMUnloadLog.EntladenOK, " & vbCrLf & _
"tblDB2DMUserProfile.NTUserID , tblDB2DMUserProfile.ProfileGroup " & vbCrLf & _
"HAVING Trim([DCUmgebung]) Like '" & strDB2Env & "' " & vbCrLf & _
"AND tblDB2DMCMUnloadLog.EntladenOK=True " & vbCrLf & _
NTUserIDEQstrWinUserID(strWinUserID, _
False, _
False, _
True, _
True, _
"tblDB2DMUserProfile") & _
"AND tblDB2DMUserProfile.ProfileGroup Like '" & strWorkGroup & "' " & vbCrLf & _
"ORDER BY " & strMinMax & "(tblDB2DMCMUnloadLog.EntladeDateTime) " & strDescAsc & vbCrLf
QSelMinMaxDate = QSelMinMaxDate & QAppendSelMinMaxDate(strDB2Env, _
strWinUserID, _
strWorkGroup, _
strMinMax, _
strDescAsc, _
"tblDB2DMCMLoadLog", _
"LadeUmgebung", _
"LadenOK", _
"LadeDateTime", _
"Load")
QSelMinMaxDate = QSelMinMaxDate & QAppendSelMinMaxDate(strDB2Env, _
strWinUserID, _
strWorkGroup, _
strMinMax, _
strDescAsc, _
"tblDB2EnvClearLog", _
"Umgebung", _
"GeloeschtOK", _
"LoeschDateTime", _
"Clear") & _
"ORDER BY JobDate " & strDescAsc
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QAppendSelMinMaxDate
'
' Auswahlabfrage: Erweitert die Abfrage QSelMinMaxDate um die übergebene Tabelle
' und die übergebenen Attribute.
'
' Parameter: strDB2Env
' strWinUserID
' strWorkGroup
' strMinMax
' strDescAsc
' strTable
' strField1
' strField2
' strField3
' strJobType
'
' Rückgabe: QAppendSelMinMaxDate
'-------------------------------------------------------------------------------------'
Private Function QAppendSelMinMaxDate(ByVal strDB2Env As String, _
ByVal strWinUserID As String, _
ByVal strWorkGroup As String, _
ByVal strMinMax As String, _
ByVal strDescAsc As String, _
ByVal strTable As String, _
ByVal strField1 As String, _
ByVal strField2 As String, _
ByVal strField3 As String, _
ByVal strJobType As String) As String
QAppendSelMinMaxDate = _
"UNION " & vbCrLf & _
"(SELECT TOP 1 " & vbCrLf & _
"Format(" & strMinMax & "(" & strTable & "." & strField3 & "),'" & _
modPublDecl.gconEurDate & "') AS JobDate " & vbCrLf & _
"FROM " & strTable & " " & vbCrLf & _
"INNER JOIN tblDB2DMUserProfile " & vbCrLf & _
"ON " & strTable & ".NTUserID = tblDB2DMUserProfile.NTUserID " & vbCrLf & _
"GROUP BY Trim([" & strField1 & "]), '" & strJobType & "', " & _
strTable & "." & strField2 & ", " & vbCrLf & _
"tblDB2DMUserProfile.NTUserID , tblDB2DMUserProfile.ProfileGroup " & vbCrLf & _
"HAVING Trim([" & strField1 & "]) Like '" & strDB2Env & "' " & vbCrLf & _
"AND " & strTable & "." & strField2 & "=True " & vbCrLf & _
NTUserIDEQstrWinUserID(strWinUserID, _
False, _
False, _
True, _
True, _
"tblDB2DMUserProfile") & _
"AND tblDB2DMUserProfile.ProfileGroup Like '" & strWorkGroup & "' " & vbCrLf & _
"ORDER BY " & strMinMax & "(" & strTable & "." & strField3 & ") " & strDescAsc & ")" & vbCrLf
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelWorkGroupUsers
'
' Auswahlabfrage: Ermittelt alle Bearbeiter/innen der Arbeitsgruppe strWorkGroup,
' welche mindestens einen erfolgreichen Job ausgeführt haben.
'
' Parameter: strDB2Env
' strWorkGroup
' dateFrom
' dateTo
'
' Rückgabe: QSelWorkGroupUsers
'-------------------------------------------------------------------------------------'
Public Function QSelWorkGroupUsers(ByVal strDB2Env As String, _
ByVal strWorkGroup As String, _
ByVal dateFrom As Date, _
ByVal dateTo As Date) As String
QSelWorkGroupUsers = QSelWorkGroupUsers & _
"SELECT DISTINCT tblDB2DMCMUnloadLog.NTUserID " & vbCrLf & _
"FROM tblDB2DMUserGroup " & vbCrLf & _
"INNER JOIN (tblDB2DMCMFiles " & vbCrLf & _
"INNER JOIN (tblDB2DMUserProfile " & vbCrLf & _
"INNER JOIN tblDB2DMCMUnloadLog " & vbCrLf & _
"ON tblDB2DMUserProfile.NTUserID = tblDB2DMCMUnloadLog.NTUserID) " & vbCrLf & _
"ON tblDB2DMCMFiles.DCID = tblDB2DMCMUnloadLog.DCID) " & vbCrLf & _
"ON tblDB2DMUserGroup.ProfileGroup = tblDB2DMUserProfile.ProfileGroup " & vbCrLf & _
"WHERE (((tblDB2DMCMUnloadLog.NTUserID) " & _
"In (SELECT NTUserID FROM tblDB2DMUserProfile " & _
"WHERE ProfileGroup Like '" & strWorkGroup & "')) " & vbCrLf & _
"AND ((tblDB2DMCMUnloadLog.EntladeDateTime)>=#" & Format(dateFrom, "Short Date") & "#)) " & _
"OR (((tblDB2DMCMUnloadLog.EntladeDateTime)<=#" & Format(dateTo, "Short Date") & "#) " & vbCrLf & _
"AND ((tblDB2DMUserProfile.ProfileGroup) Like '" & strWorkGroup & "') " & vbCrLf & _
"AND ((Trim([DCUmgebung])) Like '" & strDB2Env & "')) " & vbCrLf
QSelWorkGroupUsers = QSelWorkGroupUsers & QAppendSelWorkGroupUsers(strDB2Env, _
strWorkGroup, _
dateFrom, _
dateTo, _
"tblDB2DMCMLoadLog", _
"LadeDateTime", _
"LadeUmgebung")
QSelWorkGroupUsers = QSelWorkGroupUsers & QAppendSelWorkGroupUsers(strDB2Env, _
strWorkGroup, _
dateFrom, _
dateTo, _
"tblDB2EnvClearLog", _
"LoeschDateTime", _
"Umgebung") & _
UnionSelAll(False, _
vbNullString, _
"tblDB2DMCMFiles", _
"NTUserID")
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelWorkGroupUsers
'
' Auswahlabfrage: Erweitert die UNION Abfrage QSelWorkGroupUsers um die übergebene
' Tabelle und die übergebenen Attribute.
'
' Parameter: strDB2Env
' strWorkGroup
' dateFrom
' dateTo
' strTable
' strField1
' strField2
'
' Rückgabe: QSelWorkGroupUsers
'-------------------------------------------------------------------------------------'
Private Function QAppendSelWorkGroupUsers(ByVal strDB2Env As String, _
ByVal strWorkGroup As String, _
ByVal dateFrom As Date, _
ByVal dateTo As Date, _
ByVal strTable As String, _
ByVal strField1 As String, _
ByVal strField2 As String) As String
QAppendSelWorkGroupUsers = _
"UNION " & vbCrLf & _
"SELECT DISTINCT " & strTable & ".NTUserID " & vbCrLf & _
"FROM tblDB2DMUserGroup " & vbCrLf & _
"INNER JOIN (tblDB2DMUserProfile " & vbCrLf & _
"INNER JOIN " & strTable & " " & vbCrLf & _
"ON tblDB2DMUserProfile.NTUserID = " & strTable & ".NTUserID) " & vbCrLf & _
"ON tblDB2DMUserGroup.ProfileGroup = tblDB2DMUserProfile.ProfileGroup " & vbCrLf & _
"WHERE (((tblDB2DMUserProfile.ProfileGroup) Like '" & strWorkGroup & "') " & vbCrLf & _
"AND ((" & strTable & "." & strField1 & ")>=#" & Format(dateFrom, "Short Date") & "#)) " & _
"OR (" & strTable & "." & strField1 & ")<=#" & Format(dateTo, "Short Date") & "# " & vbCrLf & _
"AND " & strTable & "." & strField2 & " Like '" & strDB2Env & "' " & vbCrLf
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelWorkGroups
'
' Auswahlabfrage: Ermittelt alle Arbeitsgruppen, welche mindestens einen erfolgreichen
' Job ausgeführt haben.
'
' Parameter: dateFrom
' dateTo
'
' Rückgabe: QSelWorkGroups
'-------------------------------------------------------------------------------------'
Public Function QSelWorkGroups(ByVal dateFrom As Date, _
ByVal dateTo As Date) As String
QSelWorkGroups = _
"SELECT DISTINCT tblDB2DMUserProfile.ProfileGroup AS WorkGroup " & vbCrLf & _
"FROM tblDB2DMCMFiles " & vbCrLf & _
"INNER JOIN (tblDB2DMUserProfile " & vbCrLf & _
"INNER JOIN tblDB2DMCMUnloadLog " & vbCrLf & _
"ON tblDB2DMUserProfile.NTUserID = tblDB2DMCMUnloadLog.NTUserID) " & vbCrLf & _
"ON (tblDB2DMUserProfile.NTUserID = tblDB2DMCMFiles.NTUserID) " & vbCrLf & _
"AND (tblDB2DMCMFiles.DCID = tblDB2DMCMUnloadLog.DCID) " & vbCrLf & _
"WHERE Format([tblDB2DMCMUnloadLog.EntladeDateTime],'" & _
modPublDecl.gconEurDate & "')>=#" & Format(dateFrom, "Short Date") & "# " & vbCrLf & _
"AND Format([tblDB2DMCMUnloadLog.EntladeDateTime],'" & _
modPublDecl.gconEurDate & "')<=#" & Format(dateTo, "Short Date") & "# " & vbCrLf
QSelWorkGroups = QSelWorkGroups & QAppendSelWorkGroups(dateFrom, _
dateTo, _
"tblDB2DMCMLoadLog", _
"LadeDateTime")
QSelWorkGroups = QSelWorkGroups & QAppendSelWorkGroups(dateFrom, _
dateTo, _
"tblDB2EnvClearLog", _
"LoeschDateTime") & _
UnionSelAll(True, _
"WorkGroup", _
"tblDB2DMUserProfile", _
"WorkGroup")
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QAppendSelWorkGroups
'
' Auswahlabfrage: Erweitert die Abfrage QSelWorkGroups um die übergebene Tabelle und
' das übergebene Attribut.
'
' Parameter: dateFrom
' dateTo
' strTable
' strField
'
' Rückgabe: QAppendSelWorkGroups
'-------------------------------------------------------------------------------------'
Private Function QAppendSelWorkGroups(ByVal dateFrom As Date, _
ByVal dateTo As Date, _
ByVal strTable As String, _
ByVal strField As String) As String
QAppendSelWorkGroups = _
"UNION " & vbCrLf & _
"SELECT DISTINCT tblDB2DMUserProfile.ProfileGroup AS WorkGroup " & vbCrLf & _
"FROM " & strTable & " " & vbCrLf & _
"INNER JOIN tblDB2DMUserProfile " & vbCrLf & _
"ON " & strTable & ".NTUserID = tblDB2DMUserProfile.NTUserID " & vbCrLf & _
"WHERE Format([" & strTable & "." & strField & "],'" & _
modPublDecl.gconEurDate & "')>=#" & Format(dateFrom, "Short Date") & "# " & vbCrLf & _
"AND Format([" & strTable & "." & strField & "],'" & _
modPublDecl.gconEurDate & "')<=#" & Format(dateTo, "Short Date") & "# " & vbCrLf
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelLoadUnloadLog
'
' Auswahlabfrage: Ermittelt die Daten für den Bericht modPublDecl.gconRepUL (UNLOAD)
' bzw. modPublDecl.gconRepLL (LOAD).
'
' Parameter: strParentTable
' strDetailTable
' strOrderByField1
' strOrderByField2
' boolOrderByField2IsParent
'
'
' Rückgabe: QSelLoadUnloadLog
'-------------------------------------------------------------------------------------'
Public Function QSelLoadUnloadLog(ByVal strParentTable As String, _
ByVal strDetailTable As String, _
ByVal strOrderByField1 As String, _
ByVal strOrderByField2 As String, _
ByVal boolOrderByField2IsParent As Boolean) As String
QSelLoadUnloadLog = _
"SELECT tblDB2DMCMFiles.NTUserID AS ParentNTUserID, " & _
"tblDB2DMCMFiles.DCID AS ParentDCID, " & vbCrLf & _
"tblDB2DMCMFiles.DCUmgebung, tblDB2DMCMFiles.KeySaved, " & vbCrLf & _
strDetailTable & ".* " & vbCrLf & _
"FROM " & strParentTable & modPublDecl.gconSpace & vbCrLf & _
"INNER JOIN " & strDetailTable & modPublDecl.gconSpace & vbCrLf & _
"ON " & strParentTable & ".DCID = " & strDetailTable & ".DCID " & vbCrLf & _
"ORDER BY " & strParentTable & modPublDecl.gconFullStop & strOrderByField1 & ", " & _
IIf(boolOrderByField2IsParent = True, strParentTable & modPublDecl.gconFullStop & _
strOrderByField2, _
strDetailTable & modPublDecl.gconFullStop & _
strOrderByField2)
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDCDLWorkGroup
'
' Auswahlabfrage: Datenherkunft des Steuerelements cboDCWorkGroup bzw. cboDLWorkGroup
' des Formulars modPublDecl.gconFrmRP festlegen.
'
' Parameter: strTable1
' strTable2
'
' Rückgabe: QSelDCDLWorkGroup
'-------------------------------------------------------------------------------------'
Public Function QSelDCDLWorkGroup(ByVal strTable1 As String, _
ByVal strTable2 As String) As String
QSelDCDLWorkGroup = _
"SELECT DISTINCT tblDB2DMUserGroup.ProfileGroup " & vbCrLf & _
"FROM tblDB2DMUserGroup " & vbCrLf & _
"INNER JOIN (" & strTable1 & modPublDecl.gconSpace & vbCrLf & _
"INNER JOIN " & strTable2 & modPublDecl.gconSpace & vbCrLf & _
"ON " & strTable1 & ".NTUserID = " & strTable2 & ".NTUserID) " & vbCrLf & _
"ON tblDB2DMUserGroup.ProfileGroup = tblDB2DMUserProfile.ProfileGroup " & vbCrLf & _
UnionSelAll(False, _
"WorkGroup", _
"tblDB2DMUserGroup", _
"ProfileGroup")
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDCDLUser
'
' Auswahlabfrage: Datenherkunft des Steuerelements cboDCUser bzw. cboDLUser des
' Formulars modPublDecl.gconFrmRP festlegen.
'
' Parameter: strWorkGroup
' strTable1
' strTable2
' strTable3
'
' Rückgabe: QSelDCDLUser
'-------------------------------------------------------------------------------------'
Public Function QSelDCDLUser(ByVal strWorkGroup As String, _
ByVal strTable1 As String, _
ByVal strTable2 As String, _
ByVal strTable3 As String) As String
QSelDCDLUser = _
"SELECT DISTINCT " & strTable1 & ".NTUserID " & vbCrLf & _
"FROM tblDB2DMUserGroup " & vbCrLf & _
"INNER JOIN (" & strTable2 & modPublDecl.gconSpace & vbCrLf & _
"INNER JOIN " & strTable3 & modPublDecl.gconSpace & vbCrLf & _
"ON " & strTable2 & ".NTUserID = " & strTable3 & ".NTUserID) " & vbCrLf & _
"ON tblDB2DMUserGroup.ProfileGroup = tblDB2DMUserProfile.ProfileGroup " & vbCrLf & _
"WHERE tblDB2DMUserProfile.ProfileGroup Like '" & strWorkGroup & "' " & vbCrLf & _
UnionSelAll(False, _
"NTUserID", _
"tblDB2DMCMFiles", _
"NTUserID")
End Function
'-------------------------------------------------------------------------------------'
' Funktion: UnionSelAll
'
' Beschreibung: Erweitert eine SQL Abfrage um den UNION Befehl zur Auswahl von '(Alle)'.
'
' Parameter: boolAs
' strAsRetVal
' strTable
' strOrderByAttrib
'
' Rückgabe: UnionSelAll
'-------------------------------------------------------------------------------------'
Private Function UnionSelAll(ByVal boolAs As Boolean, _
ByVal strAsRetVal As String, _
ByVal strTable As String, _
ByVal strOrderByAttrib As String) As String
UnionSelAll = _
"UNION " & vbCrLf & _
"SELECT '" & modPublDecl.gconSelectAll & "'" & _
IIf(boolAs = True, " AS " & strAsRetVal, vbNullString) & _
modPublDecl.gconSpace & vbCrLf & _
"FROM " & strTable & modPublDecl.gconSpace & vbCrLf & _
"ORDER BY " & strOrderByAttrib
End Function
'-------------------------------------------------------------------------------------'
' Funktion: DelOrSel
'
' Beschreibung: Abhängig vom Inhalt der Variablen boolDelete, wird die Zeichenfolge
' 'DELETE ' oder 'SELECT ' zurückgegeben.
'
' Parameter: boolDelete
'
' Rückgabe: DelOrSel
'-------------------------------------------------------------------------------------'
Private Function DelOrSel(ByVal boolDelete As Boolean) As String
DelOrSel = IIf(boolDelete = True, "DELETE", "SELECT") & modPublDecl.gconSpace
End Function
'-------------------------------------------------------------------------------------'
' Funktion: NotOrNull
'
' Beschreibung: Abhängig vom Inhalt der Variablen boolNot, wird die Zeichenfolge 'Not '
' oder ein vbNullString zurückgegeben.
'
' Parameter: boolNot
'
' Rückgabe: NotOrNull
'-------------------------------------------------------------------------------------'
Private Function NotOrNull(ByVal boolNot As Boolean) As String
NotOrNull = IIf(boolNot = True, "Not ", vbNullString)
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelUserJobsRunning
'
' Auswahlabfrage: Wählt den aktiven Job der angemeldeten Bearbeiterin/des angemeldeten
' Bearbeiters. Der Time Out ist noch nicht erreicht.
'
' Parameter: Ohne
'
' Rückgabe: QSelUserJobsRunning
'-------------------------------------------------------------------------------------'
Public Function QSelUserJobsRunning() As String
QSelUserJobsRunning = _
QSelDelTable("tblJCLJobsRunning", _
False) & modPublDecl.gconSpace & vbCrLf & _
"WHERE JobInitiated<=Now() " & vbCrLf & _
"AND JobTimeOut>=Now() " & vbCrLf & _
"AND NTUserID='" & modPublDecl.gstrWinUserID & "'"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelAllJCLTablesExistingInSysIBM
'
' Auswahlabfrage: Gibt keinen Datensatz zurück, falls alle Quelltabellen nicht in der
' Tabelle tblSYSIBMTableAttribs vorhanden sind.
'
' Parameter: Ohne
'
' Rückgabe: QSelAllJCLTablesExistingInSysIBM
'-------------------------------------------------------------------------------------'
Public Function QSelAllJCLTablesExistingInSysIBM() As String
QSelAllJCLTablesExistingInSysIBM = _
"SELECT DISTINCT UmgebTabelle " & vbCrLf & _
"FROM tblJCLLoadFileTableAttribs " & vbCrLf & _
"WHERE UmgebTabelle In " & vbCrLf & _
"(SELECT DISTINCT UmgebTabelle From tblSYSIBMTableAttribs) " & vbCrLf & _
"ORDER BY UmgebTabelle"
End Function
'-------------------------------------------------------------------------------------'
' Funktion: DCIDEQlngDCID
'
' Beschreibung: Erweitert Abfragen um die übergebene DCID.
'
' Parameter: lngDCID
' boolWhere
' strTable (Optional)
'
' Rückgabe: DCIDEQlngDCID
'-------------------------------------------------------------------------------------'
Private Function DCIDEQlngDCID(ByVal lngDCID As Long, _
ByVal boolWhere As Boolean, _
Optional ByVal strTable As String) As String
DCIDEQlngDCID = _
WhereTable(boolWhere, _
strTable) & "DCID=" & lngDCID & modPublDecl.gconSpace & vbCrLf
End Function
'-------------------------------------------------------------------------------------'
' Funktion: QSelDB2ClientIniPath
'
' Beschreibung: Gibt den Pfad zur DB2 Client Ini Datei zurück.
'
' Parameter: strWinUserID
'
' Rückgabe: QSelDB2ClientIniPath
'-------------------------------------------------------------------------------------'
Public Function QSelDB2ClientIniPath(ByVal strWinUserID As String) As String
QSelDB2ClientIniPath = _
QSelTblFldOrderByFld("tblDB2DMUserProfile", _
"DB2ClientIniPath", _
vbNullString) & vbCrLf & _
NTUserIDEQstrWinUserID(modPublDecl.gstrWinUserID, _
True, _
False, _
False, _
False)
End Function
'-------------------------------------------------------------------------------------'
' Funktion: NTUserIDEQstrWinUserID
'
' Beschreibung: Erweitert Abfragen um die übergebene Windows User ID.
'
' Parameter: strWinUserID
' boolWhere
' boolNot
' boolDelimiter
' boolCrLf
' strTable (Optional)
'
' Rückgabe: NTUserIDEQstrWinUserID
'-------------------------------------------------------------------------------------'
Private Function NTUserIDEQstrWinUserID(ByVal strWinUserID As String, _
ByVal boolWhere As Boolean, _
ByVal boolNot As Boolean, _
ByVal boolDelimiter As Boolean, _
ByVal boolCrLf As Boolean, _
Optional ByVal strTable As String) As String
NTUserIDEQstrWinUserID = _
WhereTable(boolWhere, _
strTable) & "NTUserID" & _
IIf(boolNot = True, " Not", vbNullString) & " Like '" & strWinUserID & "'" & _
IIf(boolDelimiter = True, modPublDecl.gconSpace, vbNullString) & _
IIf(boolCrLf = True, vbCrLf, vbNullString)
End Function
'-------------------------------------------------------------------------------------'
' Funktion: WhereTable
'
' Beschreibung: Erweitert Abfragen um 'WHERE ' oder 'AND ' und, falls strTable ungleich
' vbNullString ist, die übergebene Tabelle.
'
' Parameter: boolWhere
' strTable
'
' Rückgabe: WhereTable
'-------------------------------------------------------------------------------------'
Private Function WhereTable(ByVal boolWhere As Boolean, _
ByVal strTable As String) As String
WhereTable = _
IIf(boolWhere = True, "WHERE", "AND") & modPublDecl.gconSpace & _
IIf(Not (strTable = vbNullString), strTable & modPublDecl.gconFullStop, vbNullString)
End Function
