PDA

Vollständige Version anzeigen : Tabelle mit dem Inhalt einer Textdatei aktualisieren


killepitsch
27.04.2011, 09:45
Hallo,

ich bekomme täglich eine Textdatei zur Verfügung gestellt. Diese importiere ich bereits jeden morgen in die Datenbank. Bei der Umstellung auf Access 2010 habe ich bemerkt, dass Access 2010 nicht mehr per VBA komprimieren kann. Könnt Ihr mir sagen wie ich den Code optimieren kann, sodass das Importieren nicht mehr so lange dauert? Vielleicht ist es möglich jedes einzelne Feld überprüfen zu lassen und sobald Unterschiede bestehen ließt man es aus der Textdatei ein? Dann würde sich die Datenbank auch nciht so sehr aufblähen und das Komprimieren so lange dauern. Die Tabelle muss in jedem Fall immer den gleichen Inhalt wie die Textdatei haben.
In der Textdatei können Datensätze wegfallen, hinzukommen oder sich nur einzelne Felder ändern.

Private Sub Form_Open(Cancel As Integer)

Const cFile As String = "\\abcdef\asdfg\Textfile.txt"
Dim dteLetzteAktualisierung As Date
Dim bReturn As Boolean

'Datum der letzten Aktualisierung ermitteln
dteLetzteAktualisierung = DMax("DatumUpdate", "tbl_Update")

If dteLetzteAktualisierung < Date And FileSize(cFile) > 135000000 Then
bReturn = IMPORTIEREN()
If bReturn Then _
CurrentDb.Execute "INSERT INTO tbl_Update (DatumUpdate) SELECT Date()", dbFailOnError

' Datenbank komprimieren
CommandBars("Menu Bar"). _
Controls("Extras"). _
Controls("Datenbank-Dienstprogramme"). _
Controls("Datenbank komprimieren und reparieren..."). _
accDoDefaultAction
End If

End Sub

Function IMPORTIEREN() As Boolean
On Error GoTo IMPORTIEREN_Err

DoCmd.SetWarnings False 'Besser: mit CurrentDB.Execute / CurrentDbC.Execute
DoCmd.OpenQuery "Textfile_Löschabfrage", acViewNormal, acEdit
DoCmd.OpenQuery "Textfile_Anfügeabfrage", acViewNormal, acEdit
DoCmd.SetWarnings True 'Besser: mit CurrentDB.Execute / CurrentDbC.Execute
IMPORTIEREN = True

IMPORTIEREN_Exit:
Exit Function

IMPORTIEREN_Err:
MsgBox Error$
Resume IMPORTIEREN_Exit

End Function


Public Function FileSize(ByRef sFile As String) As Variant
Dim oFSO As Object
Dim oFile As Object

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFile = oFSO.GetFile(sFile)
FileSize = oFile.Size
'Debug.Print oFile.DateCreated ' Erstellungsdatum
'Debug.Print oFile.DateLastModified ' Änderungsdatum
Set oFile = Nothing
Set oFSO = Nothing
End Function

Atrus2711
27.04.2011, 09:50
Hi,

eine Texdatei von mindestens ~135 MB zu verwursten kann schon einen Moment dauern.

Die Lösch- und Anfügeabfragen dürften von geeigneten Indizes in den beteiligten Tabellen profitieren.

Und wenn du sagst:
Die Tabelle muss in jedem Fall immer den gleichen Inhalt wie die Textdatei haben
dann frag ich mich, wieso du da eine Änderungsanalyse machst (Altes weg, neues hin): lösch einfach alles, und lies alles ein. Dann hast du die Textdatei. Dein Ansatz klingt eher nach Fehlersuche in einer Fotokopie....

Und VBA kann nach wie vor komprimieren. Vielleicht nicht mehr über den hanebüchenen Weg, den du da gehst, aber auf jeden Fall über dao.DBEngine.CompactDatabase("Quelldb","Zieldb")

ebs17
27.04.2011, 10:25
Ich würde mir zuerst die Frage stellen, wie diese Texttabelle im Nachgang genutzt wird.
- Gibt es Beziehungen zu anderen Tabellen, wären diese notwendig (bei vollständigem Löschen der Datensätze eher nicht)?
- Benötigt man Indizes?

135 MB löschen und neu anlegen erzeugt mit Sicherheit eine Menge Temp-Müll. Statt auf eine planmäßige Beseitigung von Temp-Müll würde ich einen Weg versuchen, diesen Temp-Müll von meinem Arbeits-Backend fernzuhalten - womit sich auch die Notwendigkeit des Komprimierens erübrigt.

killepitsch
27.04.2011, 13:07
Ich werde den Textfile beim Importieren auf 3 Tabelle aufteilen: Verträge, Kunden, Lieferanten
Dadurch müsste es schon ein bisschen weniger an Daten werden. Diese Tabellen haben untereinander natürlich Beziehungen. Genauso gibt es mit einigen weiteren Tabellen in der Datenbank Beziehungen.
diesen Temp-Müll von meinem Arbeits-Backend fernzuhalten
An was denkst du dabei?


@Atrus2711: Meinst du löschen + komplett importieren geht schneller als analysieren und nur die geänderten Daten zu übertragen?

Atrus2711
27.04.2011, 13:14
Meinst du löschen + komplett importieren geht schneller als analysieren und nur die geänderten Daten zu übertragen
Was fällt dir prozesstechnisch leichter:

zwei bibeldicke Bücher (z.B. Auflage 1 und Auflage 2) miteinander zu vergleichen und die geänderten Passagen zu übertragen
Auflage 1 des bibeldicken Buchs wegzuwerfen und Auflage 2 neuzukaufen?


Wenn du keine Veränderungsanalyse brauchst, sondern einfach den neuen Datenstand haben willst, brauchst du nicht zu vergleichen.

Ausnahmen existieren, wenn du Genaueres über die Änderungen weißt - z.B., dass nur und welche Kunden sich geändert haben.

ebs17
27.04.2011, 13:29
Was fällt dir prozesstechnisch leichter
Hier sind wir aber deutlich an einem Punkt, wo es nicht darum geht, was für den Entwickler am einfachsten umzusetzen geht, sondern den Prozess zu verbessern (schnellerer Import, Verzichtbarkeit des Komprimierens erreichen).

Ich werde ...
Reden wir über Gegenwart oder Zukunft? Bei "fließenden" Aufgabenstellungen können sich Tipps drastisch unterscheiden.
DoCmd.OpenQuery "Textfile_Löschabfrage", acViewNormal, acEdit
DoCmd.OpenQuery "Textfile_Anfügeabfrage", acViewNormal, acEdit
Was steckt da genau dahinter?

Übrigens würde auch eine genaue Beschreibung des Prozesses helfen (Rahmenbedingungen).

Atrus2711
27.04.2011, 13:38
Hier sind wir aber deutlich an einem Punkt, wo es nicht darum geht, was für den Entwickler am einfachsten umzusetzen geht, sondern den Prozess zu verbessern
Unnütze Prozesse verbessert man am besten durch Elimination. Wozu vergleichen, wenn eh nur der gesamte neue Zustand interessiert?

ebs17
27.04.2011, 13:45
Wozu vergleichen, wenn eh nur der gesamte neue Zustand interessiert?
Das wäre eine Frage der Kosten. Alles neu anfügen kostet (sichtbar) auch.
Wegen einer gesparten Wagenwäsche (Kosmetik) würde man nicht gleich ein neues Auto kaufen.

@killepitsch:
135 MB Textdatei: Wie viele Datensätze sind das etwa? Wie viele sind geschätzt anders bzw. neu?

killepitsch
28.04.2011, 09:51
Bei der momentan noch in der Entwicklung befindlichen Version habe ich den Import auf 3 Tabellen aufgeteilt. Bei der Tabelle Kunden und Lieferanten werden die Daten gruppiert. Verträge natürlich nicht.
Es sind ca. 65000 Datensätze in Verträge. Kunden und Lieferanten entsprechend weniger.
Die Löschabfragen löschen die Tabellen und mit den Anfügeanfragen schreibe ich die Daten neu in die Tabellen. (Habe leider den alten Code gepostet -> Nun eben mit 3x Lösch- + Anfügeabfragen)
Wie viele Datensätze sich von Tag zu Tag ändern kann ich nicht sagen. Sicherlich nur ein Bruchteil. Z. T auch nur einzelne Felder. In den Tabellen Kunden und Lieferanten ändert sich kaum etwas.
40 Felder in Kunden, 13 Felder in Lieferanten, 86 Felder in Verträge

Atrus2711
28.04.2011, 10:01
Hi,

nochmal:

brauchst du eine Veränderungsanalyse?
Sollen die alten Daten erhalten bleiben/nachvollziehbar sein?


Wenn beides nein, ist es vermutlich die wenigste Arbeit und die beste Performane, den Kram einfach neu zu importieren. Denn dabei fällt jegliche Vergleichsarbeit alt vs. neu weg. Es fällt nur Import an und die Gruppierung in die Kunden und Lieferanten. Dafür ist aber auch jeder Datenstand wirklich nur "Stand jetzt".

killepitsch
28.04.2011, 10:25
Nein, ich benötige weder eine Veränderungsanayse noch die alten Daten.

dao.DBEngine.CompactDatabase("Quelldb","Zieldb")Ist es korrekt das QuellDB und ZielDB nicht identisch sein dürfen? Dann würde sich der Name der DB ständig ändern?

Ich habe die Komprimierung umgestellt. Nur leider findet der Vorgang erst dann statt, wenn man die DB schließt. Gibt es einen Befehl um die Datenbankdatei neu zu starten (beenden +starten)?
' Datenbank komprimieren
Dim lngOldSize As Long
Dim lngNewSize As Long
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
lngNewSize = fso.GetFile(CurrentDb.Name).Size
lngOldSize = DLookup("Groesse", "tbl_Compact")
If ((lngNewSize - lngOldSize) / lngOldSize) * 100 >= 40 Then
SetOption "Auto Compact", True
Else
If GetOption("Auto Compact") = True Then
CurrentDb.Execute "UPDATE tbl_Compact SET Groesse = " & lngNewSize & ";"
End If
SetOption "Auto Compact", False
End If

Atrus2711
28.04.2011, 10:31
Hi,

mit deinem Code evranlasst du lediglich, dass die DB beim Schließen komprimiert wird. Das entspricht dem manuellen Setzen des entsprechenden Häkchens in den Access-Optionen. Komprimiert wird aber halt erst beim Schließen, daher der Name "Beim Schließen komprimieren". Das genügt ja vielleicht auch.

Ansonsten kannst du den CompactDatabase-Ansatz wählen. Die Namen müssen ungleich sein, das macht das automatische Komprimieren auch so, damit es bei evtl. Problemen noch eine Alt-DB gibt. Aber du kannst natürlich ggf. danach die Dateien umbenennen (Name-Anweisung).

Eine DB als aktuelle DB öffnen kannst du mit Application.OpenCurrentDatabase.

killepitsch
28.04.2011, 11:28
Ich habe den Code in einem Modul gespeichert.
Wozu dient pwrPWD? Merkt Access sich das Passwort und gibt es beim Neustart automatisch ein?
Wie kann ich das Modul aus dem VBA-Fenster aufrufen?
Ich habe es mit "Call fCompactJet" probiert, allerdings kommt immer "Variable oder Prozedur anstelle eines Moduls erwartet.
Option Compare Database
Option Explicit

Public Function fCompactJet(ByVal strDB As String, Optional ByVal strPWD As String = vbNullString) As Boolean

'Fehlerbehandlung definieren
On Error GoTo Err_fCompactJet

'Variablen deklarieren
Dim strTemp As String

'Initialisieren
fCompactJet = False
strTemp = strDB & ".bak"

'Komprimieren
If strPWD = vbNullString Then
DBEngine.CompactDatabase strDB, strTemp
Else
DBEngine.CompactDatabase strDB, strTemp, , , ";pwd=" & strPWD
End If

If Err.Number <> 0 Then Exit Function

Kill strDB
Name strTemp As strDB

'Fertig
fCompactJet = True

'Ende
Exit_fCompactJet:
On Error Resume Next
Exit Function

'Fehlerbehandlung
Err_fCompactJet:
MsgBox Err.Decripstion, vbCritical, Err.Number
Resume Exit_fCompactJet

End Function

Atrus2711
28.04.2011, 11:43
strPwd brauchst du nur, wenn die DB kennwortgeschützt ist; dann ist das das Kennwort, das die Db benötigt.

Das Modul kannst du gar nicht aufrufen, allenfalls eine Routine daraus. Für Testzwecke kannst du die Routine im Direktfenster (Strg+G) ausführen.

killepitsch
28.04.2011, 11:52
Wie rufe ich denn eine Routine auf?

Atrus2711
28.04.2011, 11:58
Grundsätzlich durch Eingabe des Namens. Wenn die Routine Argumente erwartet, kommen die dahinter.

In deinem FAll also
fCompactJet "c:\pfad\zur\datenbank.mdb"

In deinem ursprünglichen Code hast du doch auch schon Routinen ausgeführt?!

killepitsch
28.04.2011, 12:23
Ja, allerdings ist mir VBA in einigen Dingen noch ein Rätsel.

Im Modul habe ich es nicht hinbekommen, daher im gleichen VBA Fenster.
Es kommt Laufzeitfehler 438: Objekt unterstützt diese Eigenschaft oder Methode nicht. Der Error entsteht hier: MsgBox Err.Decripstion, vbCritical, Err.Number (vorletzte Zeile)
Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)

Const cFile As String = "\\abcde\asdfg\Textfile.txt"
Dim dteLetzteAktualisierung As Date
Dim bReturn As Boolean

'Datum der letzten Aktualisierung ermitteln
dteLetzteAktualisierung = DMax("DatumUpdate", "tbl_Update")

If dteLetzteAktualisierung < Date And FileSize(cFile) > 40000000 Then
bReturn = IMPORTIEREN()
If bReturn Then _
CurrentDb.Execute "INSERT INTO tbl_Update (DatumUpdate) SELECT Date()", dbFailOnError

' Datenbank komprimieren
fCompactJet "C:\Documents and Settings\Benutzer\Desktop\Datenbank.mdb"

End If

End Sub


Function IMPORTIEREN() As Boolean
On Error GoTo IMPORTIEREN_Err

DoCmd.SetWarnings False
DoCmd.OpenQuery "Textfile_Löschabfrage", acViewNormal, acEdit
DoCmd.OpenQuery "Textfile_Anfügeabfrage", acViewNormal, acEdit
DoCmd.SetWarnings True
IMPORTIEREN = True

IMPORTIEREN_Exit:
Exit Function

IMPORTIEREN_Err:
MsgBox Error$
Resume IMPORTIEREN_Exit

End Function


Public Function FileSize(ByRef sFile As String) As Variant
Dim oFSO As Object
Dim oFile As Object

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFile = oFSO.GetFile(sFile)
FileSize = oFile.Size
Set oFile = Nothing
Set oFSO = Nothing
End Function


Public Function fCompactJet(ByVal strDB As String, Optional ByVal strPWD As String = vbNullString) As Boolean

'Fehlerbehandlung definieren
On Error GoTo Err_fCompactJet

'Variablen deklarieren
Dim strTemp As String

'Initialisieren
fCompactJet = False
strTemp = strDB & ".bak"

'Komprimieren
If strPWD = vbNullString Then
DBEngine.CompactDatabase strDB, strTemp
Else
DBEngine.CompactDatabase strDB, strTemp, , , ";pwd=" & strPWD
End If

If Err.Number <> 0 Then Exit Function

Kill strDB
Name strTemp As strDB

'Fertig
fCompactJet = True

'Ende
Exit_fCompactJet:
On Error Resume Next
Exit Function

'Fehlerbehandlung
Err_fCompactJet:
MsgBox Err.Decripstion, vbCritical, Err.Number
Resume Exit_fCompactJet

End Function

Atrus2711
28.04.2011, 12:33
Hi,

MsgBox Err.Description, vbCritical, Err.Number

Intellisense (= automatisches Vorschlagen der möglichen Befehle hinter einem Punkt), VBA-Hilfe und Vereinfachung (Ausschlussverfahren) helfen, sowas auch selbst zu finden. Reines Copy und Paste ohne Verständnis hilft nur bei Doktorarbeiten.

CptChaos
28.04.2011, 12:56
SCNRReines Copy und Paste ohne Verständnis hilft nur bei Doktorarbeiten:lachen: :yelrotfl: :happy::top:

killepitsch
28.04.2011, 13:05
Oh danke. Das hätte ich wirklich selbst herausfinden können.

Leider bekomme ich nun den Fehler 3045:
'C:\Documents.......\Datenbank.mdb' konnte nicht erstellt werden. Datei wird bereits verwendet.

Ich habe auch schon probiert meine Datenbank in FE und BE aufzuteilen. Leider derselbe Fehler.
Mich wundert das, da ich doch als ZielDB (strTemp = strDB & ".bak") angegeben habe und er eigentlich eine neue Datei erstellen müsste.

Atrus2711
28.04.2011, 13:10
Mögliche Ursachen:
Die derzeit offene DB kann sich nicht selbst komprimieren.
Die Zieldatei darf noch nicht existieren.

Hinter der Komprimiergeschichte verblasst übrigens die eigentliche Aufgabenstellung. Wenn du es richtig aufziehst, wird ein Komprimieren "jetzt" gar nicht mehr nötig sein.

ebs17
28.04.2011, 13:44
Es scheint nun wohl etwas OT zu sein, obwohl das Thema "Tabelle mit dem Inhalt einer Textdatei aktualisieren" heißt, aber das Interesse hat sich auf Komprimieren verlagert. Trotzdem würde ich einige Gedanken niederlegen:
Könnt Ihr mir sagen wie ich den Code optimieren kann, sodass das Importieren nicht mehr so lange dauert?Dieses Problem ist wohl nun kein Problem mehr?
Ursprünglicher Zustand war wohl alles löschen und alles neu anlegen mit der Wirkung, dass Bedarf zum Komprimieren besteht. Aktueller Zustand ist wohl der Gleiche, egal, ob man nun mit einer oder mit drei Tabellen arbeitet.

Der Aussage, dass Löschen und Neuanfügen die beste Performance bietet, widerspreche ich in dieser Pauschalität nach wie vor. Ein gezieltes Aktualisieren kann (nicht muss) durchaus Vorteile auch in der benötigten Zeit bieten, die Notwendigkeit, die DB komprimieren zu müssen, würde in jedem Fall drastisch sinken.
Wie eine gezielte Aktualisierung ablaufen könnte und ob sie tatsächlich auch Zeitvorteile bringt, kann man an der Stelle natürlich nicht sagen. Dazu sind hier vorhandene Informationen zu gering vorhanden.
Statt auf eine planmäßige Beseitigung von Temp-Müll würde ich einen Weg versuchen, diesen Temp-Müll von meinem Arbeits-Backend fernzuhalten - womit sich auch die Notwendigkeit des Komprimierens erübrigt.
Meine Frage nach Beziehungen der einen Tabelle (oder jetzt drei) zu den weiteren Tabellen wurde zwar bejaht, diese dürften aber überflüssig sein. Welche Funktion hat eine Beziehung ohne referentielle Integrität? Referentielle Integrität mit Daten, die täglich gelöscht werden, ist aber nun nicht wirklich herstellbar. Verknüpfungen zwischen Tabellen in Abfragen kann man auch ohne Beziehungen herstellen.
Mit dem Wissen, dass keine Beziehung an der Stelle notwendig ist, kann man bewusste Tabelle(n) auch in einem gesonderten Backend führen. Das hätte den Vorteil, dass man einen Import (wie auch immer) in eine Kopie dieses Zusatzbackends vornehmen könnte und dann einfach dieses Backend ersetzen könnte (Verknüpfungen in den Frontends blieben erhalten).
- Das Löschen des "alten" Backends erübrigt ein Komprimieren, bei Unterstellung, dass es keine weiteren drastischen aufblähenden Vorgänge in der DB gibt.
- Solche "Datenwartungsarbeiten" sind besonders kritisch im Mehrnutzerbetrieb, sprich Zugriffe auf diese Daten in jeglicher Form während der Wartung sollten ausgeschlossen sein. Der Wechsel des Backends würde nur einen Moment dauern und damit deutlich kürzer sein als Abfragen mit größeren Datenmengen und ließe sich ggf. sogar im laufenden Betrieb vornehmen.

Zur aktuellen Diskussion: Ein Komprimieren sollte nur stattfinden, wenn jegliche Zugriffe auf die Daten (Tabellen) ausgeschlossen sind, sonst drohen Beschädigungen an der DB. Bei einem Komprimieren bei Schließen wird das wohl organisiert, diese Form des Komprimierens ist aber leicht buggy, sprich könnte schon mal schief gehen.

Atrus2711
28.04.2011, 13:53
Das Löschen des "alten" Backends
verunmöglicht allerdings auch ein Aktualisieren der verändernden Daten...

:)

Also, Threaderöffner, entscheide:

tägliches "Differentialvorgehen" mit Komprimierungsbedarf
tägliches "Neuaufsetzen" eines initial leeren (!) Backends mit den täglichen Daten


Ein Import/Verknüpfung auf die Daten wird in beiden Fällen nötig sein. Eine Differentialanalyse aber - soll sie hinreichend differenzieren - müsste die Unterschiede erstmal rauskriegen. Und bis dahin sind die neuen Zustände möglicherweise auch neu normalisiert. Bei den Zeilenmengen ist mir nicht bange. Bei den Feldmengen, die zu vergleichen wären, allerdings schon.

Es käme auf den Versuch an.

ebs17
28.04.2011, 14:17
Das Löschen des "alten" Backends verunmöglicht allerdings auch ein Aktualisieren der verändernden Daten ...
Nun, in der derzeitigen Situation werden Daten einmal am Tag erneuert. Da würde ich doch mit einiger Berechtigung unterstellen, dass zwischendurch keine schreibenden Vorgänge stattfinden. Von einer statischen Datei kann man dann auch eine Kopie anlegen und mit dieser Kopie arbeiten, ehe man sie zum Original macht.
Gar so viel Tiefgründigkeit passt aber hier noch nicht, wenn man sich nicht in die Rahmenbedingungen hineindenken kann:
40 Felder in Kunden, 13 Felder in Lieferanten, 86 Felder in Verträge
... oder 139 Felder in einer Gesamttabelle stellen vermutlich auch noch nicht ein Optimum dar. Datenbankarbeit ist doch nicht in erster Linie Datenimport, sondern eher Datenverarbeitung.

ebs17
28.04.2011, 18:47
Bei den Zeilenmengen ist mir nicht bange. Bei den Feldmengen, die zu vergleichen wären, allerdings schon.
Mengen sind ein relativer Begriff. Möglicherweise kommen die täglichen 65.000 Datensätze aus einem System mit einem Hauch an professioneller Gestaltung. Dann könnte es einen Timestamp auf Datensatzänderung geben. Dann müsste man nicht jedes Feld vergleichen, sondern könnte Datensätze mit gleichem Schlüssel, aber unterschiedlichen Timestamp aktualisieren, und dann gleich alle Felder.

Daher: Man könnte doch mal fragen, ob so etwas existiert (oder einzurichten geht).

Zum Neuanfügen bzw. Löschen eignen sich Inkonsistenzabfragen als Basis.

Eine Frage wäre auch, ob sich in der Quelltabelle Schlüssel oder Felder, die als Schlüssel verwendbar sind, befinden. Daran schließt sich auch die Frage an, wie man durch Trennung der Felder in drei Tabellen auf funktionierende(?) Beziehungen zwischen diesen Tabellen kommt. Die letzte Frage interessiert mich besonders.

Und zum Schluss: Warum muss ich an das (hier klicken (http://www.ms-office-forum.net/forum/showthread.php?t=270009)) denken, wenn ich über den Gesamtprozess nachdenke?

ebs17
29.04.2011, 08:33
Ich habe gerade noch einmal nachgelesen: So neu sind die oben gegebenen Hinweise bezüglich
- Trennung der DB in Frontend und Backend (hier klicken (http://www.donkarl.com?FAQ1.35)),
- Komprimieren des Backends,
- Auslagern umfangreicher temporärer Dinge in ein eigenes Backend,
- differenziertes Aktualisieren als Alternative zum kompletten Ersetzen
nicht, siehe Acc2003 - TXT-Datei automatisiert importieren und ff.