Office: SQL-String erweitern

latürnich sollst du die einzelnen SQL's als Query speichern !

Code:

I.d.Query kannst du dann die Funktion
getDeinVonDatSQL()
verwenden wie o.a.

slg erwin...

 

Hi,

des mit den Funktionen klappt leider nicht. Schreib ich das Datum manuell in die Query:

SELECT PK_Einsatzstelle, DerTag
FROM Einsatzstellen, AlleTage
WHERE DerTag between #2002-01-01# and #2002-01-31#;

dann geht das...

Baue ich die Funktionen ein:

SELECT Einsatzstellen.PK_Einsatzstelle, AlleTage.DerTag
FROM Einsatzstellen, AlleTage
WHERE (((AlleTage.DerTag) Between fktDatumvon() And fktdatumvon()));

Dann geht das nicht:

Hier die Funktionen (Das Datum steht zu Testzwecken drin; später kommt da das Feld ausm Form rein...):

Public Function fktDatumBis() As String

Dim EndeMonat As String
Dim Monat As String

Monat = "Januar 2002"

EndeMonat = DateAdd("m", 1, DateValue(Monat)) - 1

fktDatumBis = "#" & Format(EndeMonat, "yyyy-mm-dd") & "#"

End Function

ublic Function fktDatumVon() As String

Dim AnfangMonat As Variant
Dim Monat As String

Monat = "Januar 2002"

AnfangMonat = DateAdd("m", 0, DateValue(Monat))

fktDatumVon = "#" & Format(AnfangMonat, "yyyy-mm-dd") & "#"

End Function

Wenn ich die Funktionen über VBA aufrufe, dann liefert Sie die richtigen Daten...

Woran liegt das?

Merci

Sven

 

sorry - da ist mir in der Eile ein Fehler unterlaufen:

Wenn du Funktionen so einbindest, dann müssen sie natürlich den korrekten Datentyp liefern - also Date - zB.:

public function VonDatum() as Date
VonDatum = DateSerial(nz(forms!deinform!dasjahr,1900), nz(forms!deinform!dasmonat,1), 1)
end function

slg erwin...

 

Public Function fktDatumVon() As Date

Dim AnfangMonat As Date

Dim Monat As String

Monat = "Januar 2002"

AnfangMonat = DateAdd("m", 0, DateValue(Monat))

fktDatumVon = DateSerial(Nz(AnfangMonat, 1900), Nz(AnfangMonat, 1), 1)

Da bekomm ich einen Überlauf? (in der letzten Zeile...)

Mit Dateadd wandel ich doch in Datum um?

[ 02. Mai 2002: Beitrag editiert von: horse79 ]

 

Hi,

folgende Funktionen langen auch:
(Funktioniert zumindest jetzt bei mir oder hälst Du DateSerial für zwingend erforderlich *confused.gif* )

Public Function fktDatumBis() As Date

Dim EndeMonat As Date
Dim Monat As String

Monat = "Februar 2002"

fktDatumBis = DateAdd("m", 1, DateValue(Monat)) - 1

End Function


Public Function fktDatumVon() As Date

Dim AnfangMonat As Date
Dim Monat As String

Monat = "Februar 2002"

fktDatumVon = DateAdd("m", 0, DateValue(Monat))

End Function

Sven

[ 02. Mai 2002: Beitrag editiert von: horse79 ]

 

NEIN - Dateserial ist sicher nicht "zwingend erforderlich" aber du verwendest da die Datentypen etwas (naja) seltsam *wink.gif*

Ich bin davon ausgegangen, dass du 2 Felder am Formular hast: Jahr + Monat

Dann klappt (ohne Public Variable - allerdings programmatisch "nicht ganz sauber") die von mir gepostete Zuweisung i.d. Function "VonDatum".

Wenn du am Formular 1 Datumsfeld (zwecks Auswahl) hast, dann ca. so (auch nicht ganz sauber *g*):

public function VonDatum() as date
dim hDat as date
hDat = nz(forms!DeinForm!DasDatum, #1900-01-01#)
VonDatum = dateserial(year(hDat), month(hDat), 1)
end function

public function BisDatum() as date
dim hDat as date
hDat = nz(forms!DeinForm!DasDatum, #2299-01-01#)
hDat = dateserial(year(hDat), month(hDat), 1)
BisDatum = Dateadd("m", 1, hDat) - 1
end function

"Sauberer" wäre es mit Public Var. zu arbeiten, da man iA. fix ausprogrammierte Formularbezüge in Public Fu's vermeiden sollte.

so long erwin...

PS: Was du da mit "Feber 2002" anstellst, welchen du an DateValue übergibst, ist nicht sehr empfehlenswert, da eine korrekte Erkennung bei der Typkonvertierung von externen (Systemsteuerung) Einstellungen abhängt, was oft zu Folge hat, dass ein Programm mal funktioniert - und dann auch wieder nicht !

[ 02. Mai 2002: Beitrag editiert von: erwin ]

 

hooops

wat isse nu - hast du's jetzt geschafft - oder frustriert aufgegeben *wink.gif* ?

slg erwin...

 

Bin scho no dabei *cool.gif*

Hab no a bisserl Performance Problemchen...

I poste nachher no meinen aktuellem Sachstand.

Bis später...

 

...Freundschaft mit Desiree...

na na na, wir wollen doch mal nicht soooo übertreiben *nene.gif* , sonst rieseln bei mir i.d. Antworten demnächst auch Herzchen im Hintergrund *biggrin.gif*

so long erwin...

 

Hi,

so hier zu meinen aktuellen Problemchen:

(Es läuft ja alles so wie ich mir vorstell... aber...)

Die Datenbank bläht sich pro Klick um 16 KB auf. Im Beitrag weiter oben waren es noch über 40KB, das hat sich schon einiges verbessert. Merci Erwin. Aber ich versteh die 16KB nicht. Ich arbeite nicht mehr mit den TempTables sondern nur noch mit gespeicherten Queries. Aber schaut selber. Hier der Code der ausgeführt wird:
(Ich hab in den Code Kommentare geschrieben, wie sich die DB-Größe zur Laufzeit bei Änderun-gen auswirkt, wenn Du mir da die Hintergründe noch erläutern könntest??)

Private Sub Datum_AfterUpdate()

Dim inhalt As Date
Dim Ende1 As Date
Dim Anfang1 As Date
Dim Ende As String
Dim Anfang As String

inhalt = Me!Datum
Ende1 = DateAdd("m", 1, DateValue(inhalt)) - 1
Anfang1 = DateAdd("m", 0, DateValue(inhalt))

'Umwandlung des Datums ins Datumsformtat für SQL
Ende = "#" & Format(Ende1, "yyyy-mm-dd") & "#"
Anfang = "#" & Format(Anfang1, "yyyy-mm-dd") & "#"

SQL = "SELECT DISTINCTROW Einsatzplanung.*, Einsatzstellen.Name AS [Einsatzstellenname], [Azubidaten]![Name] & chr(32) & [Azubidaten]![Vorname] AS AzubiName, Einsatzpla-nung.DatumVon AS Von, Einsatzplanung.DatumBis AS Bis FROM (Einsatzplanung LEFT JOIN Einsatzstellen ON Einsatzplanung.PK_Einsatzstelle = Einsatzstellen.PK_Einsatzstelle) LEFT JOIN Azubidaten ON Einsatzplanung.PK_Azubi = Azubidaten.PK_Azubi WHERE (((Einsatzplanung.DatumVon) &gt= " & Anfang & ") And ((Einsatzplanung.DatumBis) &lt= " & Ende & ")) ORDER BY Einsatzstellen.Name;"
Forms!frmEinsatzplanung!subfrmEinsatzplanung.Form.RecordSource = SQL
'Würde dieser SQL als Query gespeichert werden, steigt die DB-Größe um über 40 KB statt "nur" 16 KB aktuell
'Lässt man DISTINCTROW aus dem String weg, steigT die DB Größe um 32 KB statt "nur" 16 KB aktuell

unverplanteTageEinerEinsatzstzelleEinesMonates 'gespeichert in Modul Funktionen
unverplanteTageEinesAzubisEinesMonates 'gespeichert in Modul Funktionen

Statusleiste.Panels(1).text = "Sie haben keine Auswahl getroffen..."
'Diese Zeile beeinflusst nicht die Datenbankgröße zur Laufzeit

PrimaryKey = ""
SQL = "SELECT DISTINCTROW [Einsatzplanung].[Datumvon] & ' bis ' & [Einsatzplanung].[Datumbis] & ' ' & [Einsatzstellen].[Name] AS Zeitraum FROM Einsatzstellen INNER JOIN Einsatzplanung ON Einsatzstellen.PK_Einsatzstelle = Einsatzpla-nung.PK_Einsatzstelle WHERE (((Einsatzplanung.PK_Azubi) =" & PrimaryKey & ")) ORDER BY Einsatzplanung.DatumVon;"
Me!Liste9.RowSource = SQL
'Würde hier statt zuweisung des SQL nur Me!liste9.rowsource="" steigt DBGröße um 38 KB statt "nur" um 16 KB aktuell
'Lässt man DISTINCTROW aus dem String weg, hat dies keine Auswirkung auf die DB-Größe im Gegensatz zum String oben

End Sub

Hier noch die Funktionen:

Public Function unverplanteTageEinerEinsatzstzelleEinesMonates()

DoCmd.SetWarnings False

Forms!frmEinsatzplanung!Liste4.RowSource = "AlleFreienStellen"

End Function

Public Function unverplanteTageEinesAzubisEinesMonates()

DoCmd.SetWarnings False

Forms!frmEinsatzplanung!Liste2.RowSource = "AlleFreienAzubis"

End Function

und hier noch die Queries:

AlleTage:

SELECT DateSerial(fktJahr(),[MonatX],[TagX]) AS DerTag
FROM HilfsMonate, HilfsTage
WHERE (((IsDate(fktJahr() & "-" & [MonatX] & "-" & [TagX]))&lt&gtFalse));

AlleTageUndAzubis:

SELECT Azubidaten.PK_Azubi, AlleTage.DerTag
FROM Azubidaten, AlleTage
WHERE (((AlleTage.DerTag) Between fktdatumvon() And fktdatumbis()) AND ((Azubidaten.AusbildungBeginn)&lt=fktdatumvon()) AND ((Azubidaten.AusbildungEnde)&gt=fktdatumBis()));

AlleTageUndStellen:

SELECT Einsatzstellen.PK_Einsatzstelle, AlleTage.DerTag
FROM Einsatzstellen, AlleTage
WHERE (((AlleTage.DerTag) Between fktdatumvon() And fktdatumbis()));

AlleFreienAzubis:

SELECT A.PK_Azubi, [Azubidaten]![Name] & " " & [Azubidaten]![Vorname] AS Inhalt, Count(*) AS AnzahlTageUngeplant
FROM AlleTageUndAzubis AS A INNER JOIN Azubidaten ON A.PK_Azubi = Azubida-ten.PK_Azubi
WHERE (((Exists (SELECT * FROM Einsatzplanung B
WHERE B.PK_Azubi=A.PK_Azubi
AND A.DerTag Between B.DatumVon And B.DatumBis))=False))
GROUP BY A.PK_Azubi, [Azubidaten]![Name] & " " & [Azubidaten]![Vorname];

AlleFreienStellen:

SELECT A.PK_Einsatzstelle, Einsatzstellen.Name, Count(*) AS AnzahlTageUngeplant
FROM AlleTageUndStellen AS A INNER JOIN Einsatzstellen ON A.PK_Einsatzstelle = Einsatz-stellen.PK_Einsatzstelle
WHERE (((Exists (SELECT * FROM Einsatzplanung B
WHERE B.PK_Einsatzstelle=A.PK_Einsatzstelle
AND A.DerTag Between B.DatumVon And B.DatumBis))=False))
GROUP BY A.PK_Einsatzstelle, Einsatzstellen.Name;

Ich hoffe mal jemand kann mir hier beo der Performance Steigerung helfen?

Vielen Dank

Sven

 

Hallo Sven!

Ist ja echt spannend, womit du dich so beschäftigst... Aber was erwartest du eigentlich?! Wenn du mit dynamischen Abfragen arbeitest, wie sollte sich die Datenbank da nicht verändern!? Ist nun mal eine Access-typische Eigenart und da hilft nur regelmäßiges Komprimieren.

Gruss Anne

 

Mit dynamisch meinst Du dass selbergebastelte Funktionen in den Queries mit eingebunden sind?
Eigentlich werden aber die gespeicherten Tabellen nicht angerührt, da sollte doch nicht soviel Aufblähung passieren?

Vielleicht könnte ja mann die Funktionen an sich no effektiver programmieren...

Public Function Jahresplan(Monat As Variant, Einsatzstelle As Variant) As String

'Funktion gibt je Monat und Einsatzstelle alle eingesetzten Azubis sowie das Einsatzdatum in ei-nem String aus
'Diese Funktion wird für die Erstellung der Halbjahrespläne benötigt

On Error GoTo Fehler

Dim rst As Recordset
Dim mdb As DATABASE
Dim SQL As String
Dim Startdatum As Date
Dim Endedatum As Date
Dim Azubi As Variant
Dim Ergebnis As Variant

SQL = "SELECT DISTINCTROW Einsatzplanung.PK_Einsatzstelle, Azubidaten.Name, Azubida-ten.Vorname, Einsatzplanung.DatumVon, Einsatzplanung.DatumBis FROM Azubidaten INNER JOIN Einsatzplanung ON Azubidaten.PK_Azubi = Einsatzplanung.PK_Azubi ORDER BY Einsatz-planung.PK_Einsatzstelle, Einsatzplanung.DatumVon;"
'SQL ist sortiert nach PK_Einsatzstelle und DatumVon

Startdatum = DateAdd("m", 0, DateValue(Monat)) 'Berechnung des ersten eines Monates
Endedatum = DateAdd("m", 1, DateValue(Monat)) - 1 'Berechnung des letzteb eines Monates

Set mdb = CurrentDb()
Set rst = mdb.OpenRecordset(SQL)

Do While Not rst.EOF
If rst!DatumVon &gt= Startdatum Then
If rst!DatumVon &lt= Endedatum Then
Ergebnis = Einsatzstelle - rst!PK_Einsatzstelle
If Ergebnis = 0 Then
If Azubi = "" Then
Azubi = Left(rst!DatumVon, 5) & "-" & Left(rst!DatumBis, 5) & " " & rst!name & ", " & rst!vorname
Else
Azubi = Azubi & " " & Left(rst!DatumVon, 5) & "-" & Left(rst!DatumBis, 5) & " " & rst!name & ", " & rst!vorname
End If
End If
End If
End If
rst.MoveNext
Loop

rst.Close
mdb.Close

Ende:
Set rst = Nothing
Set db = Nothing
Jahresplan = Azubi 'Funktion gibt den Wert aus Varible Azubi aus
Exit Function

Fehler:
MsgBox "Es ist ein Fehler aufgetreten..." & Chr(13) & Chr(13) & "Beschreibung des Fehlers:" & Chr(13) & Chr(13) & Err.Description & Chr(13) & Chr(13) & "Der Fehler hat die Nummer: " & Err.Number, vbCritical, "Sicherheitssystem der Azubiverwaltung"
MsgBox "Dieser Fehler trat im Modul Jahresplan auf..." & Chr(13) & "Bitte verständigen Sie den Administrator.", vbInformation, "Sicherheitssystem der Azubiverwaltung"

GoTo Ende

End Function

Ich zweifel aber immer noch daran, dass die 16 KB daran liegen...

Aber mal schauen, vielleicht fällt euch dazu noch etwas ein... *top.gif*

 

Das soll wohl der Thread des Jahres werden? *wink.gif*

Aufblähen: Also, die Zahlen, die du nennest sind doch nicht nenneswert? Was sind schon 16kb ?

Aber zur Erklärung:
Abfragen, ob per String generiert oder gespeichert, erzeugen schon Defragmentierung in der DB und die geht - wie Anne richtig bemerkt - mit Komprimieren wieder weg.
Umso komplexer die Abfrage (wie bei dir), umso mehr Zwischenschritte muss Access ausführen, um zum Ergebnis zu kommen. Wenn du - als Tüftler - genauer sehen willst, wie Access die Abfrage abarbeitet, so kannst du mal einen Queryplan ausgeben lassen.
( In Registry unter
HKEY_Local_Machine\Software\Microsoft\Jet\4.0 oder 3.5\Debug
den Schlüssel JETSHOWPLAN (String) mit "ON" eintragen)
Da wird dann eine Textdatei showplan.out erzeugt, die das Vorgehen von Jet anzeigt.
Wirst dich wundern, was alles beim Ausführen deiner Abfrage abgeht.

Außerdem werden evtl. temporäre Tabellen während der Ausführung angelegt, die man NICHT sieht, wohl aber defragmentieren.

Ciao, Sascha

 

Naja 16KB sind natürlich für sich nicht viel, jetzt stelle Dir mal ca. 10 User vor. Die 16KB entstehen bei jedem Klick auf ne Schaltfläche. Diese muß betätigt werden, wenn ein anderer Monat angezeigt werden soll. Also die Kerntätigkeit meiner Anwendung. Durchschnittlich klickt der User also bis zu 100mal pro Tag drauf. Ne einfache Rechnung am Tag. 10*100*16= 16.000 KB pro Tag...
Dazu noch Access95 bei dem man ja so tolle Autokomprimierungen starten kann *frown.gif*

Ich dachte evtl. dass ich die oben geposteten Queries und Fragmente evtl. no a bisserl entschlanken kann, vielleicht sieht da ja noch jemand Ansätze, weil die VBA-Leuchte bin ich auch nicht so...

[ 07. Mai 2002: Beitrag editiert von: horse79 ]

 

also in deiner Function Jahresplan würde ich auf jeden Fall die Datumsselektion NICHT per IF im Code machen, sondern als WHERE Teil in SQL !

Ausserdem liegt doch hoffentlich auch ein Index auf dem Datum i.d. Einsatz-Tab. ?!

Für meinen Geschmack JOIN'st du sowieso in den Abfrage ein bisschen zu viel und zu oft - da müsstest du dir doch selbst überlegen können, wie die sicherlich sowieso schon recht komplexe Abfragerei so gestaltet werden könnte, dass

a) einschränkende Selektion (per indizierter (!) Felder) möglichst "früh" i.d. geschachtelten Abfragen erfolgt.
b) Die Joins so erfolgen, dass nich innerhalb der
Schachtelung der Abfragen dieselben Daten mehrfach gelesen werden müssen.

bzgl. der MDB-Vergrösserung hat dir ja Sascha schon den Tipp gegeben, wie du nachschauen kannst, was JET da macht - bzw. ist es bei komplexen Queries normal, dass JET mit internen temp.Tab's arbeitet.

so long erwin...