Access-o-Mania

Guten Tag in die Runde,

ich wäre sehr dankbar für Anregungen zum Thema Rechnungsnummern.

Ich habe dazu im Wesentlichen 2 Anforderungen:

1. die Rechnungsnummer soll eindeutig sein
2. die Rechnungsnummer soll lückenfrei fortlaufend sein

Derzeitige Lösung:

Eine Tabelle mit Nummernkreise, die per Code hogezählt werden.

Funktioniert eigentlich ganz gut, bis dann, wenn zwei Nutzer gleichzeitig Rechnung schreiben, was bei einem erfolgreichen Unternehmen ja durchaus der Fall sein kann. Dann werden Positionen von Aufträgen, die nichts miteineander zu tun haben zusammen gewürfelt.

Ich suche eine elegante Möglichkeit für Punkt 2.

Dabei habe ich schon an eine Sequenz bzw. Autowertfeld gedacht, das ich im Rahmen einer Transaktion mit INSERT anstoße, den Autowertwert abfange und für die Rechnungsnummer verwende.

Punkt 1 ist damit abgefackelt aber bei Abbrüchen entstehen Lücken!

Hallo!

Was hältst du davon, die Rechnungsnummer für die Buchhaltung erst beim Abschluss/Freigabe der Rechnung zu vergeben?

mfg
Josef

Hallo,

ZitatPunkt 1 ist damit abgefackelt aber bei Abbrüchen entstehen Lücken!

Was stört Dich an den Lücken ?

Zitat von: MzKlMu am Januar 28, 2019, 23:07:32
Hallo,

ZitatPunkt 1 ist damit abgefackelt aber bei Abbrüchen entstehen Lücken!

Was stört Dich an den Lücken ?

Die Frage ist doch ob sich der Auftraggeber an Lücken stört.

Auch wenn es kein absolutes gesetzliches Erfordernis dazu gibt - jeder Finanzprüfer wird bei Lücken stutzig.
Da es also problemlos möglich ist, keine Lücken zu haben, würde ich auch darauf bestehen.  ;)

Zitat von: markus888 am Januar 29, 2019, 10:19:38
Auch wenn es kein absolutes gesetzliches Erfordernis dazu gibt - jeder Finanzprüfer wird bei Lücken stutzig.
Da es also problemlos möglich ist, keine Lücken zu haben, würde ich auch darauf bestehen.  ;)

Nein, es ist nicht problemlos möglich bei einer Rechnungsdatenbank in MS Access. Ein Finanzprüfer wird bei einer selbergestrickten Access-Rechnungsdatenbank sowieso stutzig .

Die Nummernvergabe ist ein Standardproblem mit best practices, etwa mit einer Sperrtabelle, da bräuchte es eine Garantie für 100%ige serielle Verarbeitung von Schreibvorgängen.

Hallo!

Es ist in Access problemlos möglich fortlaufende Nummern zu vergeben, sobald die Rechnung freigeschalten wurde.

Prinzip:
Tabellenaufbau:
idRechnung PK, Autowert, dient zum Verknüpfen
Rechnungsnummer Text (unique key)
....


Mögliche Vorgehensweise:
Wenn eine Rechnung erstellt wird - also im Entwurfsmodus ist - bleibt die Rechnungsnummer leer oder erhält erhält eine eindeutige "Draft"-Nummer. Es wird nur ein Platzhalter angezeigt, der z. B. über die ID gebildet wird (RE-DRAFT-000123).
Wenn die Rechnung freigegeben wird (gilt dann als gebucht und nicht mehr löschbar), wird die fortlaufende Rechnungsnummer vergeben.
Durch den eindeutigen Index ist sichergestellt, dass es keine doppelten Nummern gibt.
Wenn man die Nummer direkt per SQL erstellt, ist die Wahrscheinlichkeit, dass es zu einem Index-Konflikt kommt, sehr gering.
Nachdem die Nummer vergeben wurde, kann die Rechnung im Original gedruckt werden.

[OT]

ZitatEin Finanzprüfer wird bei einer selbergestrickten Access-Rechnungsdatenbank sowieso stutzig

Die Anwendung, mit der die Rechnung erstellt wird, muss nicht zwangsweise das Buchhaltungsprogramm sein. ;)

mfg
Josef

Zitat von: Wurliwurm am Januar 29, 2019, 10:44:22
Nein, es ist nicht problemlos möglich bei einer Rechnungsdatenbank in MS Access.

Mit Leichtigkeit sogar.  ;)
Bist wohl schon lange weg von Access.  ;D

Zitat von: markus888 am Januar 29, 2019, 11:58:27

Zitat von: Wurliwurm am Januar 29, 2019, 10:44:22
Nein, es ist nicht problemlos möglich bei einer Rechnungsdatenbank in MS Access.

Mit Leichtigkeit sogar.  ;)
Bist wohl schon lange weg von Access.  ;D

Geschäfts- und revisionskritische Anwendungen hab ich in Access nie gemacht, ich benutze es aber regelmäßig als Werkzeug.

In Access kann man immer (leicht) Szenarien sich ausdenken und auch bauen, wo im Mehrbenutzerzugriff auf ein filebackend Datenschiefstände entstehen. Einfach das Backend von ein paar PCs aus mit Dauerschleifen befeuern und beobachten.

Zitat von: Wurliwurm am Januar 29, 2019, 12:13:48In Access kann man immer (leicht) Szenarien sich ausdenken und auch bauen, wo im Mehrbenutzerzugriff auf ein filebackend Datenschiefstände entstehen. Einfach das Backend von ein paar PCs aus mit Dauerschleifen befeuern und beobachten.

Mit einer Axt geht das auch. ;D ;D

Hi Josef,

danke Dir, Du hast den Nagel auf den Kopf getroffen und mir einen Denkanstoß verpasst :)

Ich könnte folgendes machen:

eine reine Nummerntabelle mit Autowerten bauen und diese Autowerte für die Rechnung verwenden. Das Neue an der Sache: Ich hole mir die Nummer erst ab, wenn die Rechnung schon existiert. Das minimiert die Möglichkeit eines Abbruchs und damit das Entstehen von Lücken.

Ich denke aber gerade noch weiter: Beschrieben war ja das Problem, dass Positionen unterschiedlicher Aufträge in "gleiche" Rechungsnummern gemixt werden. Die Ursache liegt hier in der Verwendung der Rechnungsnnummer als Schlüssel. Wenn ich das auch noch angehe und Datenbankschlüssel verwende, wäre das Mixen nicht mehr möglich, auch wenn ich das andere Problem gar nicht angehe!

Hallo!

Die reine Nummerntabelle brauchs du meiner Meinung nach nicht. Außerdem darfst du dich bei einem Autowert nicht darauf verlassen, dass keine Lücken entstehen. Ein Autowert wird vergeben sobald der Datensatz auf "edit" steht. Wird danach die Eingabe abgebrochen, ist der Autowert verbraucht.
Ein Max(Rechnungsnummer) from Rechnungstabelle reicht aus um den höchsten Wert zu ermitteln.
Zum höchsten Wert 1 dazuzählen sollte nicht das Problem sein. (Format der Rechnungsnummer ist natürlich zu berücksichtigen, falls nicht nur Zahlen darin enthalten sind.)

ZitatBeschrieben war ja das Problem, dass Positionen unterschiedlicher Aufträge in "gleiche" Rechungsnummern gemixt werden. Die Ursache liegt hier in der Verwendung der Rechnungsnnummer als Schlüssel.

Was ist das Problem?
Ob du über eine ID oder über die Rechungsnummer den Bezug herstellst ist doch egal. Über die ID hast du nur den Vorteil, dass der Bezug schon gesetzt sein kann, wenn es noch gar keine Rechnungsnummer gibt.

In die n:m-Tabelle "Auftragsposition zu Rechnungsposition" würde ich die ID vom Auftrag und die ID der Rechnung ablegen.

mfg
Josef

Zitat von: Josef P. am Januar 30, 2019, 14:52:56
Ein Max(Rechnungsnummer) from Rechnungstabelle reicht aus um den höchsten Wert zu ermitteln.

Wie schon beschrieben, ist ein Konflikt nicht 100%ig auszuschließen, wenn zwei User "gleichzeitig" Select-max abfragen. Einbau der Abfrage sehr spät im SQL-Insert-Statement ist da wohl am sichersten, wie gesagt sind Konflikte nicht 100ig auszuschließen.

Zitat
In die n:m-Tabelle "Auftragsposition zu Rechnungsposition" würde ich die ID vom Auftrag und die ID der Rechnung ablegen.

n:m ist mit Sicherheit ein Designfehler. Eine Rechnungsposition könnte dann in mehreren Rechnungsköpfen vorkommen. Es muß entweder ein Fremdschlüssel von der Rechnungsposition zum Rechnungskopf verwendet werden oder ein kombinierter Primärschlüssel in der Rechnungspositionstabelle mit Rechnungsnummer&Positionsnummer.

Wie man es dreht und wendet, es bleibt nicht ganz trivial. Der Insert in den Rechnungskopf und die Rechnungsposition(en) muß in einer Transaktion gekapselt werden (alles-oder-nichts-Prinzip). Um die Verknüpfung über die Rechnungsnummer zu machen, muß man beim Insert in die Rechnungspositionen die neue Rechnungsnummer schon kennen, was bei Autowerten nicht wirklich geht.

Eine Lösung wäre ein Sperrtabelle, in der vor dem Beginn der Transaktion die neue (Max+1) Nummer eingetragen (reserviert) wird. In der Rechnungsbuchungstransaktion wird die neue Nummer am Schluß dann wieder aus der Sperrtabelle rausgekickt. (Sauber ist es, wenn auch bei Buchungsabbrüchen aus irgendwelchen Gründen die reservierte Nummer nicht in der Sperrtabelle stehenbeibt). Genaugenommen kann es aber auch beim Insert in die Sperrtabelle die gleichen Konflikte geben, so daß sich das Problem nur verlagert.

Vielleicht mache ich es auch zu kompliziert, jedenfalls kann ich bei ernsthaften Interesse etwas Code posten, wie  ich das einmal (relativ aufwendig) gelöst habe.

Hallo!

ZitatWie schon beschrieben, ist ein Konflikt nicht 100%ig auszuschließen, wenn zwei User "gleichzeitig" Select-max abfragen. Einbau der Abfrage sehr spät im SQL-Insert-Statement ist da wohl am sichersten, wie gesagt sind Konflikte nicht 100ig auszuschließen.

Da hast du vollkommen Recht. Man kann Konflikte nicht ausschließen, die stören aber nicht, da dann einfach nicht gespeichert werden kann. Über eine Fehlerbehandlung muss man dann die SQL-Anweisung wiederholen.

Ich dachte an folgende SQL-Anweisung :
update Rechnungstabelle
set Rechnungnummer = (select Max(Rechnungnummer) from Rechnungstabelle) + 1
where RechnungsID = 123
Diese wird aber eventuell in Access nicht laufen, weil Access möglicherweise wegen der Aggregatfunktion der Meinung ist, dass das keine aktualisierbare Abfrage ist.
(Abhilfe: Dmax verwenden. Ich arbeite schon zu lange nicht mehr mit Access-Backends, daher hab ich keine aktuelle Erfahrung damit.)

Zitat
n:m ist mit Sicherheit ein Designfehler. Eine Rechnungsposition könnte dann in mehreren Rechnungsköpfen vorkommen

Warum soll eine n:m-Beziehung zw. Rechnungsposition zu Auftragsposition ein Designfehler sein? Das hast doch nichts damit zu tun, dass eine Rechnungsposition nur einmal zu einer Rechnung zuzuordnen ist.
Ich unterscheide in meinen Anwendungen immer zw. Auftrag und Rechnung und verwende keine Auftragspositionsdatensatz direkt in einer Rechnung. Allerdings verwende ich in meinen Anwendungn nur 1:n zw. Auftragsposition und Rechnungsposition, da es bei meinen Kunden nicht üblich ist, in einer Rechnungsposition mehrere Auftragspositionen abzurechnen. Eine Auftragspositionen auf mehrere Rechnungspositionen kommt schon vor.

Bezüglich Transaktionen:
Für das Anfügen der Rechnungspositionen zur Rechung (im Entwurf) ist meiner Meinung nach keine Transaktion erforderlich. Da würde ich dem User einfach über eine Unterformular schreiben lassen.
Die Rechnungsmaske kann zwischenzeitlich auch gerne geschlossen werden und die Rechnung am nächsten Tag weiterbearbeitet werden. Und wenn man die "Entwurfsrechnung" wieder löschen will, ist das auch egal, da zu diesem Zeitpunkt noch keine Nummer vergeben wurde.
Erst wenn die Rechnung "aktiviert" wird, erhält der Rechnungskopf eine Rechnungsnummer und das aktuelle Datum als Rechnungsdatum.
Der Prozess für diese Datenänderung wird in Bruchteilen einer Sekunde ablaufen. Daher wird es sehr selten vorkommen, dass versucht wird eine Nummer doppelt zu vergeben. Für diesen Sonderfall muss man mittels Fehlerbehandlung die Datenaktualisierung noch einmal anstoßen. Der Ablauf ist meiner Meinung nach aber keine besondere Herausforderung.

mfg
Josef

Zitat von: Josef P. am Januar 30, 2019, 19:58:29
Ich dachte an folgende SQL-Anweisung :

Code [Auswählen] Erweitern

update Rechnungstabelle
set Rechnungnummer = (select Max(Rechnungnummer) from Rechnungstabelle) + 1
where RechnungsID = 123
Diese wird aber eventuell in Access nicht laufen, weil Access möglicherweise wegen der Aggregatfunktion der Meinung ist, dass das keine aktualisierbare Abfrage ist.
(Abhilfe: Dmax verwenden. Ich arbeite schon zu lange nicht mehr mit Access-Backends, daher hab ich keine aktuelle Erfahrung damit.)

Ich hatte exakt dieselbe Abfolge an Gedanken dazu; und ja, das reine SQL funktioniert nicht, weil Access meint die Abfrage wäre nicht aktualisierbar.
Was ich aber nicht ohne eigene Recherche wusste/weiß (und deshalb nichts dazu geschrieben habe):
Wenn man DMax verwendet, wird die Funktion dann auch noch automatisch im Kontext derselben Transaktion ausgeführt, oder müsste man dafür dann den kompletten Aufruf aus VBA nochmal explizit in einer Transaktion kapseln?

Zitat von: Josef P. am Januar 30, 2019, 19:58:29
Warum soll eine n:m-Beziehung zw. Rechnungsposition zu Auftragsposition ein Designfehler sein? Das hast doch nichts damit zu tun, dass eine Rechnungsposition nur einmal zu einer Rechnung zuzuordnen ist.
Ich unterscheide in meinen Anwendungen immer zw. Auftrag und Rechnung und verwende keine Auftragspositionsdatensatz direkt in einer Rechnung. Allerdings verwende ich in meinen Anwendungn nur 1:n zw. Auftragsposition und Rechnungsposition, da es bei meinen Kunden nicht üblich ist, in einer Rechnungsposition mehrere Auftragspositionen abzurechnen. Eine Auftragspositionen auf mehrere Rechnungspositionen kommt schon vor.

Entschuldigung, das war schlampig gelesen von mir. Nur eine Zuordnung von Rechnungskopf und -position per m:n wäre ein Designfehler. Aufträge und Rechnungen können durchaus so aufgebaut sein, daß eine Auftragsposition in mehreren (Teil)-rechnungspositionen fakturiert werden kann und natürlich eine Rechnung auch über mehrere Aufträge hinweg abrechnet. Technisch ist m.E. vor allem problematisch die 1:n Beziehung von Auftrag zu Auftragskopf oder Rechnung zu Rechnungskopf beim Insert.

Zitat
Bezüglich Transaktionen:
Für das Anfügen der Rechnungspositionen zur Rechung (im Entwurf) ist meiner Meinung nach keine Transaktion erforderlich. Da würde ich dem User einfach über eine Unterformular schreiben lassen (...) Der Ablauf ist meiner Meinung nach aber keine besondere Herausforderung.

Wenn das inhaltlich nicht kritisch ist, kann man das so machen.

Ich dachte da eher an Buchhaltungsbelege, wo zu jedem Augenblick sichergestellt werden muß, daß alle Buchungszeilen aller Belege miteinander auf 0 aufgehen. Schon ein einziger Buchhaltungsbeleg, wo das nicht der Fall ist, würde dazu führen, daß die Bilanz nicht mehr aufgeht, das wäre ein GAU für eine doppelte Buchführung. Hier verbietet sich das Schreiben per Unterformular und es muß zwingend mit Transaktionen gearbeitet werden. Wenn dann außerdem die Belegnummern lückenlos sein müssen, muß man einigen Aufwand treiben. Ich habe vor ein paar Jahren so etwas gebaut, mit Sperrtabelle und Warteschleife, falls es Konflikte bei "Ziehen" der neuen Nummer und Reservieren derselben in der Sperrtabelle gibt.

Hallo!

ZitatIch dachte da eher an Buchhaltungsbelege, wo zu jedem Augenblick sichergestellt werden muß, daß alle Buchungszeilen aller Belege miteinander auf 0 aufgehen.

So etwas muss dann natürlich unbedingt in einer Transaktion laufen.
Ich würde dann aber auch überlegen, ob ein File-Datenbanksystem eine gute Basis für ein Buchungssystem ist.

ZitatWenn man DMax verwendet, wird die Funktion dann auch noch automatisch im Kontext derselben Transaktion ausgeführt, oder müsste man dafür dann den kompletten Aufruf aus VBA nochmal explizit in einer Transaktion kapseln?

Wie könnte man das herausfinden?

Ohne zu wissen, ob die Abfrage-Transaktionseinstellung ausreicht:
Ich würde zur Sicherheit eine Transaktion in VBA vor dem SQL-Aufruf starten, damit sie im DBEngine-Umfeld läuft. Das müsste dann meiner Meinung nach auch DMax erwischen, da ich davon ausgehe, dass DMax die identische DBEngine-Instanz verwendet.


Bezüglich Rechnungsnummer schreiben, ist das meiner Meinung nach aber nicht so kritisch. Das sollte ohne Transaktion richtige Daten liefern. Es muss aber unbedingt eine Fehlerbehandlung einen möglichen Konflikt abfangen.

[OT]
Bei diesen Überlegungen merkt man erst, wie komfortabel es ist, wenn man einen SQL-Server o. ä. verwenden kann. ;)

mfg
Josef

Zitat von: Josef P. am Januar 31, 2019, 09:41:11
Ich würde dann aber auch überlegen, ob ein File-Datenbanksystem eine gute Basis für ein Buchungssystem ist.

Die Antwort ist sowieso klar: Nein, ein Access-Backend kommt nicht in Frage. Man kann in seinen SQL-Statements und in seinen VBA-Schleifen die tollsten Verrenkungen machen, wenn die Datenbankengine darunter nicht für Serialisierung geeignet ist und nicht für den Multiuserbetrieb skaliert, kann man es vergessen.

Zitat von: Josef P. am Januar 30, 2019, 14:52:56
Ein Max(Rechnungsnummer) from Rechnungstabelle reicht aus um den höchsten Wert zu ermitteln.

Reicht eben nicht.

Problem: Bei gleichzeitigem Zugfriff mehrerer User wird die gleiche Nummer für mehrere Rechnungen verwendet (da der Prozess ja mehr als inifinitisimal wenig Zeit braucht).

Das wollte ich mit einem AddNew in eine Tabelle mit Autowert eben ausgleichen, somit steht dieser Wert "nur" dem Auserwählten zur Verfügung.

Mit obigem Ansatz, erst mal eine Draft zu erzeugen und dann erst im nachgelagerten Schritt die Nummer zu erzeugen wird die Gefahr eines Abbruchs (=Lücke) deutlich reduziert. Nur 2 Schritte: Erzeuge Nummer , schreibe in Re-Tabelle.

Zitat von: Wurliwurm am Januar 30, 2019, 16:08:37
Eine Lösung wäre ein Sperrtabelle, in der vor dem Beginn der Transaktion die neue (Max+1) Nummer eingetragen (reserviert) wird. In der Rechnungsbuchungstransaktion wird die neue Nummer am Schluß dann wieder aus der Sperrtabelle rausgekickt. (Sauber ist es, wenn auch bei Buchungsabbrüchen aus irgendwelchen Gründen die reservierte Nummer nicht in der Sperrtabelle stehenbeibt). Genaugenommen kann es aber auch beim Insert in die Sperrtabelle die gleichen Konflikte geben, so daß sich das Problem nur verlagert.

Negativ! Diese Lösung ist bereits etabliert und versagt (warum auch immer), übrigens nicht nur bei der Rechnungsthematik.

Daher der Ansatz, das Sperren/Reservieren der Datenbank zu überlassen.

Zitat von: Milvus am Februar 21, 2019, 09:47:31

Zitat von: Wurliwurm am Januar 30, 2019, 16:08:37
Eine Lösung wäre ein Sperrtabelle, in der vor dem Beginn der Transaktion die neue (Max+1) Nummer eingetragen (reserviert) wird. In der Rechnungsbuchungstransaktion wird die neue Nummer am Schluß dann wieder aus der Sperrtabelle rausgekickt. (Sauber ist es, wenn auch bei Buchungsabbrüchen aus irgendwelchen Gründen die reservierte Nummer nicht in der Sperrtabelle stehenbeibt). Genaugenommen kann es aber auch beim Insert in die Sperrtabelle die gleichen Konflikte geben, so daß sich das Problem nur verlagert.

Negativ! Diese Lösung ist bereits etabliert und versagt (warum auch immer), übrigens nicht nur bei der Rechnungsthematik.

Daher der Ansatz, das Sperren/Reservieren der Datenbank zu überlassen.

Wenn die Lösung nicht funktioniert, ist sie möglicherweise nicht sauber geschrieben. Bist Du sicher, daß Du da bei dem Aufspannen der Transaktionen alles sauber machst? Daß sie bei großer Last mit etlichen konkurrierenden Zugriffen bei einem Access-Backend nicht immer 100%ig funktionieren kann, habe ich ja weiter oben schon angesprochen.

Nummernvergabe der Datenbank zu überlassen, ist immer der richtige Weg. Es handelt sich ja um ein Standardproblem und deshalb gibt es in Datenbanken ja eine Möglichkeiten, Sequenzen (aufsteigende Nummern) anzulegen. Nicht zu verwechseln mit dem Autoincrement von MS-Access.

Hallo!

Zitat
Problem: Bei gleichzeitigem Zugfriff mehrerer User wird die gleiche Nummer für mehrere Rechnungen verwendet (da der Prozess ja mehr als inifinitisimal wenig Zeit braucht).

Eine Updateanweisung für einen einzigen Datensatz inkl. einer Max-Auswertung auf einen Index sollte eigentlich in Bruchteilen einer Sekunde erledigt sein.
Die Rechnungsnummer darf natürlich nicht als Standardwert über die Rechnungsmaske vergeben werden, sondern muss beim Speichern des Datensatzes bzw. sobald eine Nummer fixiert werden darf geschrieben werden.

Noch mal:
1. Anwender öffnet Rechnungsmaske und beginnt den Inhalt zu bearbeiten ... es gibt noch keinen Wert (oder nur einen Dummywert wie TMP-[Autowert] als Rechnungsnummer
2. Anwender speichert den Datensatz ... Rechnung ist noch nicht frei = es gibt immer noch keine Rechnungsnummer
3. Anwender gibt Rechnung frei => Jetzt wird per Update-Anweisung die nächste freie Rechnungsnummer eingetragen.
Sollte es zu einem Konflikt mit dem eindeutigen Schlüssel auf die RE-Nr kommen, weil zu gleichen Zeit ein anderer User auch die Rechnung frei gab, wird das per Fehlerbehandlung abgefangen und der Update-Prozess erneut ausgeführt.
Wichtig ist der eindeutige Index auf die Rechnungsnummer. Nur damit ist sichergestellt, dass es zu keiner Doppelvergabe kommen kann.

mfg
Josef