joesesal odbc kunden 02

' Option Explicit


'''''   This is lame.  Be ashamed of yourself
Sub test()


    ' On Error Resume Next


Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

' Provider=Microsoft.Jet.OLEDB.4.0
' Provider=Microsoft.ACE.OLEDB.12.0
objConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=\\POEL5-DATEN\Altona-Büro-Dateien\11 Büroteam\Joe Koenig\Excel Übungen\Joe  auftragsreport.xlsm;" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"";"

Dim sqlString As String

' sqlString = "SELECT  * from [Auftragseingang$] WHERE Datum IS NOT NULL ORDER BY Datum ASC "

' sqlString = "SELECT  Kunde, count(Kunde) from [Auftragseingang$] group BY Kunde"

sqlString = "SELECT  distinct(Kundenname) from [Kunden$] WHERE Kundenname is not null AND trim(Kundenname) <> '' "
objRecordset.Open sqlString & " ", objConnection, adOpenStatic, adLockOptimistic, adCmdText

Dim i As Integer
i = 0
Dim j As Integer
j = 0

Dim Kunden() As String
ReDim Kunden(0)

' Debug.Print UBound(Kunden)

Do While Not objRecordset.EOF
    Kunden(UBound(Kunden)) = objRecordset.Fields(0)
    ReDim Preserve Kunden(UBound(Kunden) + 1)
    ' Debug.Print "Record: " & objRecordset.Fields(0)
    objRecordset.MoveNext
Loop
objRecordset.Close


Dim Mitarbeiter() As String
ReDim Mitarbeiter(0)

sqlString = "SELECT  distinct(Mitarbeiter) from [Mitarbeiter$] WHERE Mitarbeiter is not null AND trim(Mitarbeiter) <> '' "
objRecordset.Open sqlString & " ", objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do While Not objRecordset.EOF
    Mitarbeiter(UBound(Mitarbeiter)) = objRecordset.Fields(0)
    ReDim Preserve Mitarbeiter(UBound(Mitarbeiter) + 1)
    ' Debug.Print "Record: " & objRecordset.Fields(0)
    objRecordset.MoveNext
Loop
objRecordset.Close







'Debug.Print "----------------------------------------------------------------------"
'
'' Debug.Print "UBound(Kunden): " & UBound(Kunden)
'For i = 0 To UBound(Kunden) - 1
'    Debug.Print "Kunde: " & Kunden(i)
'Next i
'Debug.Print "--------------- " & Now() & " -------------------------"

' sqlString = "SELECT count(*) from [Auftragseingang$] WHERE Kunde='" & Kunden(0) & "' AND Datum is not null"

'''''   This is lame.  Be ashamed of yourself

For i = 0 To UBound(Kunden) - 1
    Debug.Print Kunden(i)
    For j = 0 To UBound(Mitarbeiter) - 1
        sqlString = "SELECT count(Datum) from [Auftragseingang$] WHERE Kunde='" & Kunden(i) & "' AND ([1# Mitarbeiter] = '" & Mitarbeiter(j) & "' OR [2# Mitarbeiter] = '" & Mitarbeiter(j) & "') AND Datum is not null"
         objRecordset.Open sqlString & " ", objConnection, adOpenStatic, adLockOptimistic, adCmdText
         ' Debug.Print Kunden(i) & " : " & Mitarbeiter(j) & " : " & objRecordset.Fields(0)
         objRecordset.Close
    Next j
Next i

Debug.Print "--------------- " & Now() & " -------------------------"



objConnection.Close

End Sub