Access (VBA, SQL) Suchfunktion für DB

[klecksii]

Tag zusammen!

Ich komme in Access nicht weiter.

Ich habe eine DB mit Firmendaten aus verschiedenen Branchen (z.B. Gastronomie, Handwerker)

Nun möchte ich eine Suchfunktion, mit der ich z.B. nach einer bestimmten Branche in einem bestimmten Ort suchen kann.

Z.B. habe ich 5 Firmen im Bereich „Gastronomie“ in Berlin.
Wenn ich nun in der Combobox „Gastronomie“ auswähle, und in dem Feld für die Stadt dann „Berlin“ eintrage, sollen alle Gastronomischen Betriebe in Berlin angezeigt werden.



In meinem Fall werden alle Ergebnisse in einem Unterformular (Listenansicht) angezeigt. Der Code sieht bis jetzt so aus:

Me!Ufrm.Form.RecordSource = "SELECT Firmen.* FROM Firmen WHERE " & Me.cboStandort & " = '" & Me.txtStandort & "' AND Branche ='" & Me.CBOBranche & "'"

cboStandort ist die Combobox für die Auswahl der Stadt oder des Bundeslandes. Wählt man z.B. „Stadt“ aus, kann man eine Stadt in nebenliegende Textfeld eingeben. Wählt man „Bundesland“, dann gibt man natürlich das Bundesland an in dem man suchen will.

In der zweiten Combobox kann man die Branche auswählen.



So weit bin bereits gekommen und es funktioniert auch.

Nun ergibt sich aber ein Problem:

In der Combobox für die Branchen gibt es einen Eintrag „alle Branchen“. Wenn ich diese Auswahl wähle, und dann z.B. Berlin eingebe, sollen alle Firmen aus ALLEN Branchen in Berlin angezeigt werden. Leider weiß ich nicht wie ich das genau umsetzen soll.

Das gleiche auch mit der Combobox für die Stadt: wenn ich hier den Eintrag „alle“ auswähle, sollen die entsprechenden Firmen aus ganz Deutschland angezeigt werden, ohne Einschränkung auf eine Stadt oder Bundesland.



Quelle der Combobox-Einträge: Alle Einträge in den Comboboxen sind händisch eingetragen worden.

Wie kann ich mein Problem elegant lösen?

 

[floq0r]

SELECT Firmen.*
FROM Firmen
WHERE
    " & Me.cboStandort & " = '" & Me.txtStandort & "' AND
    (
        Branche ='" & Me.CBOBranche & "'" OR
        '" & Me.CBOBranche & "' = 'alle Branchen'
    )

edit: Für den Standort dann analog erweitern und die Klammern nicht vergessen

 

[klecksii]

Wow, das ging ja flott!

Funktioniert nur leider bei mir nicht. Sieht auf den ersten Blick aus wie eine SQL-Anweisung, nur das VBA-Code mit dabei ist. Soweit ich weiß ist im SQL-Fenster kein VBA zulässig. Und im VBA-Editor lässt sich das auch nicht anwenden, zumindest nicht der SQL-Teil.

Oder sehe ich das falsch?

 

[floq0r]

Ich hab nur den SQL Schnippsel gepostet und für bessere Lesbarkeit formatiert, den musst du schon selbst in deine Zuweisung oben (Me!Ufrm.Form.RecordSource = "...") einfügen.

 

[klecksii]

Ah, okay, das lässt sich so natürlich besser lesen, danke dafür!

Habe es getestet, mit den Branchen funktioniert es.

Ich komme mit der Erweiterung für den Standort leider nicht weiter. Vielleicht magst Du nochmal helfen, habe gerade voll den Knoten im Kopf!

Soweit bin ich gekommen:

Me!Ufrm.Form.RecordSource = "SELECT Kunden.* FROM Kunden WHERE (Standort = '" & Me.cboStandort & " = '" & Me.txtStandort & "' OR '" & Me.cboStandort & "' = 'alle') AND (Branche ='" & Me.CBOBranche & "' OR '" & Me.CBOBranche & "' = 'alle')"

Jedoch stürzt das Programm ab: Syntaxfehler.

 

[floq0r]

Das Standort = gehört da nicht hin Wenn Access da im Programmablauf die Variablen einsetzt würde da jetzt z.B. stehen: ...(Standort = 'Stadt' = 'Berlin' OR...

Das müsste also heißen:

Me!Ufrm.Form.RecordSource = "SELECT Kunden.* FROM Kunden WHERE ('" & Me.cboStandort & "' = '" & Me.txtStandort & "' OR '" & Me.cboStandort & "' = 'alle') AND (Branche ='" & Me.CBOBranche & "' OR '" & Me.CBOBranche & "' = 'alle')"

edit: und ein single quote hat auch gefehlt

 

[klecksii]

Danke für die Unterstützung!

Funktioniert leider nicht ganz. Wenn ich in cboStandort "Ort" auswähle und eine Stadt in das Textfeld eingebe, werden keine Datensätze gefunden.
Wähle ich allerdings in cboStandort den Eintrag "alle", werden alle Datensätze aus allen Städten ausgegeben.

 

[floq0r]

Ah, die single quotes gehören beim ersten Vergleich mit Me.cboStandort natürlich nicht hin weil wir ja hier einen Spaltennamen haben wollen. Da ergibt sich aber ein Problem: Wenn du in deiner Combobox die Werte "Stadt", "Bundesland", und "alle" auswählen kannst und diese Werte dann als Platzhalter für den Spaltennamen verwendest dann wird die query bei Auswahl von "alle" folgendermaßen lauten: ...WHERE (alle = 'Berlin' OR 'alle' = 'alle').... Die Spalte "alle" gibt es aber nicht. Ich würde die query entsprechend der ausgewählten Werte über eine Variable und if-conditions aufbauen ala:

query = "SELECT Kunden.* FROM Kunden WHERE "
If Me.cboStandort = "Ort" Then
    query = query & "(Ort = '" & Me.txtStandort & "')"
Else If Me.cboStandort = "Bundesland" Then
    ...
End If
Me!Ufrm.Form.RecordSource = query

Mehr Schreibarbeit aber übersichtlicher und schlüssiger.

 

[Raijin]

Wenn man mit SQL arbeitet, ist man gut damit beraten, SQL-Anweisungen mit Eingabeparametern nicht als String zusammenzubasteln. Das öffnet Tür und Tor für SQL-Injection. Wenn man in ein Eingabefeld sinngemäß etwas wie """; DROP ALL Tables; eingibt, ist die Datenbank hinterher leer. Selbst wenn man der einzige Nutzer ist, sollte man sowas von vornherein vermeiden, weil man es sonst bei der nächsten Datenbank wieder genauso macht, bis es dann eben doch irgendwann knallt..

Stattdessen sollte man sich grundsätzlich an parameterized queries gewöhnen. Die Eingabe wird dabei als typisierte Datenvariable und nicht als String gehandhabt, man kann also keine SQL-Anweisung als "Ort" getarnt einschleusen.

Was deinen konkreten Fall angeht, nutzt du ja auch noch den Inhalt einer Combobox als String für die Auswahl des Parameters 'alle' = 'alle'. Das ist als gebastelter String schwierig zu lesen und noch schwieriger zu warten. Da würde ich in VBA eher ein select-case mit der Combobox machen und je nach Inhalt eine klar lesbare SQL-Anweisung zuweisen. Damit könntest du auch Fälle wie "alle" einfacher handhaben und müsstest sie gar nicht in eine komplexe Anweisung mit einbauen.

Um deine SQL-Anweisung generell zu reparieren, solltest du sie erstmal in Klartext ausprobieren, also ohne den Inhalt der Comboboxen, sondern eingetippt. So kannst du zumindest prüfen ob die Anweisung überhaupt funktionieren kann, weil du am Ende an der falschen Stelle rumdokterst - zB wenn das 'alle'='alle' nicht so klappt wie du erwartest.

 

[klecksii]

Vielen Dank für die professionellen Tipps!

Ich habe da noch eine andere Frage:

Wenn ich eine Datenbank auf einem Server habe, und mehrere Nutzer greifen darauf zu, und einer ändert einen Datensatz, dann wird der Datensatz auf den anderen PCs nicht aktualisiert – spricht die Änderung wird nicht angezeigt.

Erst nachdem man die Tabelle/Formular schließt und wieder öffnet, wird die Änderung angezeigt.

Bei meinen Recherchen bin ich auf die Tastekombination Strg + F9 gestoßen. Damit lassen sich die Datensätze aktualisieren, was nach meinen Tests auch sehr gut funktioniert.

Nun frage ich mich natürlich, ob das mit der Tastenkombination auch mit VBA funktioniert - denn man kann ja niemanden anmuten, diese Tastenkombination nach jedem Datensatzwechsel zu drücken um sicher zu gehen auch wirklich die aktuellen Daten zu sehen.

Ich bin auf die Anweisung Me.Requery gestoßen, leider funktioniert das nicht.

Weiß jemand eine Lösung des Problems?

Und wenn es eine solche Aktualisierungs-Anweisung gibt, wie setze ich sie am besten ein? Grundsätzlich macht der Gedanke Sinn, bei jedem Datensatzwechsel zu aktualisieren, oder nach einem bestimmten Intervall – somit ist es gewährleistet, immer den tatsächlich aktuellen Wert zu sehen. Da besteht jedoch das Problem, das die gesamte Datenbank langsam wird, weil im Hintergrund jedes mal aktualisiert – sprich die ganze Datenbank neu eingelesen wird.

Was sagen die Profis dazu?

 

[floq0r]

Probier mal Requery direkt auf deiner Form: Me!Ufrm.Form.Requery
Eine periodische Neuabfrage wird deine einzige Möglichkeit sein. Du kannst die Sache natürlich etwas verfeinern indem du bei jedem Update eine neue Spalte "updated_at" aktualisierst und dann aus deiner App heraus überprüfst ob es neue oder aktualisierte records gibt. Halte ich aber für einen kleinen Anwendungsfall für Overkill.

 

[klecksii]

Diesmal geht es nicht um das Ufrm, sondern um das Hfrm.
Ich habe eine Einstellung in Access gefunden, die es ermöglicht in regelmäßigen Abständen zu aktualisieren Datei/Optionen/Clienteinstellungen/Erweitert/Anzeigeaktualisiserungsintervall.

Wie würdet ihr das lösen? Regelmäßige Abstände? Nach jedem Datensatzwechsel? oder anders?

 

[floq0r]

Ah, na wenn es das gibt umso besser. Wann du das triggerst musst du inhaltlich entscheiden. Wenn ein User einen DS schon länger offen hat und der zwischenzeitlich bearbeitet wurde dann hilft ein trigger bei DS-Wechsel zum Beispiel nichts.

 

[klecksii]

Da hast Du natürlich Recht. Ich sehe aber keine Möglichkeit, dies zu verhindern. Die Wahrscheinlichkeit das sowas passiert ist ja zum Glück extrem gering.