CD-Datenbank auf Basis einer Excel-Datei

Dieses Tutorial soll zeigen, wie man ohne große Mühen eine CD Datenbank auf Basis einer Excel-Datei (XLS) realisieren kann.

B. Olaf Rasch 08/2003

 Anregungen oder Tipps an B. Olaf Rasch
 
Einführung voriges Thema [ Top ] nächstes Thema

Um eine Datenbank für den Hausgebrauch zu entwickeln, muss man sich nicht unbedingt in Themen wie ADO, DAO oder Access einarbeiten. Gerade für den VB-Anfänger mag es bereits eine gute Übung sein, auf eine gewohnte Sache wie eine Excel-Datei zuzugreifen.

In diesem Projekt geht es um die Verwaltung von CDs mittels einer Excel(XLS)-Datei. Wir benutzen dazu 2 Tabellen; eine für die CD-Informationen (Tabelle: CD$) und eine für die einzelnen Tracks (Tabelle: Track$). Näheres dazu finden Sie im nächsten Abschnitt.

 
Aufbau voriges Thema [ Top ] nächstes Thema

Die Applikation besteht daher im wesentlichen aus der Anzeige von zwei ListView-Controls. In der oberen Liste sind die CDs aufgeführt, in der unteren Liste befinden sich die Tracks der aktuell ausgewählten CD.

Wie man sieht, habe ich zu Demozwecken bereits einige Eintragungen vorgenommen.

Um eine neue CD anzulegen bzw. vorhandene Einträge zu bearbeiten, ist ein Doppelklick auf die CD-Liste erforderlich.

Es erscheint eine Maske, in der sich die Felder editieren lassen.

Der Inhalt dieser Felder wird übrigens automatisch an die aktuell eingestellte CD angepasst.

Die Buttons haben dabei folgende Bedeutung:
  Suchen - Aufruf eines Suchdialogs (s.u.)
  Updaten - Der aktuelle CD-Datensatz wird mit den Feldinhalten geupdatet.
  Hinzufügen - Der Inhalt der Maske wird als neuer Datensatz der CD-Liste hinzugefügt.
  Löschen - Der aktuelle CD-Datensatz wird (incl. seiner Tracks) als gelöscht gekennzeichnet und
     wird nicht mehr angezeigt.

Für die Verwaltung der Tracks existiert eine ähnliche Maske, die bei Doppelklick auf die Trackliste erscheint:

Wie bei der CD-Maske haben wir auch hier die Buttons zum Updaten, Hinzufügen und Löschen.

Im Suchdialog, der über den Button 'Suchen' der CD-Maske erreichbar ist, kann man einen Begriff eingeben und über eine Spaltenliste bestimmen, in welchen Feldern der Text gesucht werden soll (OR-Verknüpfung).

Der Button mit dem Undo-Symbol bewirkt, dass eine eventuelle Selektion rückgängig gemacht wird und wieder alle CDs angezeigt werden.

Das Start-Bas-Modul (Name: basMain),
  Hier befinden sich allgemeine Deklaration und ein paar Hilfsroutinen.
 
die Hauptform (Name: frmMain),
  Diese Form enthält die beiden ListView-Controls für CDs und Tracks.
 
die CD-Maske (Name: frmCD),
  In diesem Fenster wird die Maske für einen CD-Datensatz angezeigt. Hier können Records editiert, hinzugefügt und gelöscht werden.
 
die Track-Maske (Name: frmTrack),
  In diesem Fenster wird die Maske für einen Track-Datensatz angezeigt. Hier können Tracks editiert, hinzugefügt und gelöscht werden.
 
ein Such-Dialog (Name: frmSearch)
  Hier kann in ausgewählten Feldern ein bestimmter Text gesucht werden.
 
und eine Klasse zur Verwaltung der INI-Datei (Name: IniAccess),
  Mithilfe dieser Klasse werden Zugriffe auf die lokale INI realisiert.
Einzelheiten siehe Projekt 'IniAccess' auf dieser Site...
 
FAQ voriges Thema [ Top ] nächstes Thema

Es folgen nun einige FAQs zu diesem Projekt:

 Was wird in der (automatisch generierten) INI-Datei gespeichert?

Die sessionübergreifenden visuellen Einstellungen des Benutzers wie Position und Größe des Hauptfensters, Position des Splitterbars (horizontaler Trennbalken zwischen CD- und Track-Liste) und die Positionen von CD- und Track-Maske.

 Woher weiß ein CD-Datensatz, welche Tracks zu ihm gehören?

In jedem CD-Record existiert ein Feld namens 'CD_ID' (welches im ListView jedoch nicht angezeigt wird). Ebenso hat auch jeder Track-Datensatz ein Feld gleichen Namens (welches ebenfalls nicht in der Liste angezeigt wird). Anhand dieser CD_ID können nun bei Auswahl einer CD alle zu ihr gehörigen Tracks mittels eines SQL-Befehls gesammelt und in der unteren Liste angezeigt werden. Tatsächlich weiß somit auch jeder Track, zu welcher CD er gehört; diese Tatsache spielt in unserem Fall jedoch nur für die Suchfunktion eine Rolle - sobald z.B. bei der Suche nach einem Track-Titel ein passender Track gefunden wird, muss nämlich seine CD in der oberen Liste angezeigt werden.

 Wenn die Datenverwaltung über Excel (incl. verknüpfter Tabellen) so einfach ist, wieso wird
    dann überall zur Verwendung von DAO oder ADO geraten?

Die Benutzung des Data-Controls - wie im vorliegenden Fall - hat zwar den Vorteil, dass man keine expliziten zusätzlichen Bibliotheken mit ins Projekt aufnehmen muss, es bringt jedoch auch Nachteile mit sich, die nicht unerwähnt bleiben sollen:

1. Tabellengröße:
Solange man es mit kleinen Datenbeständen (etwa einer persönlichen CD-Sammlung) zu tun hat, leistet Excel gute Dienste. Bei umfangreicheren Tabellen (etwa die CD- oder Büchersammlung einer öffentlichen Bibliothek) kann Excel an die Grenzen seiner Kapazität stoßen. Genaue Zahlen sind mir hierzu zwar nicht bekannt, jedoch weiß ich von den Nöten diverser Entwickler, die irgendwann auf Access umstellen mussten, weil Excel Probleme mit großen Datenmengen hatte.

2. Löschen von Datensätzen:
Man kann mit der verwendeten Methode (IISAM/Jet) zwar Datensätze anlegen und updaten, leider kann man aber keine Records löschen. Hier der entsprechende Auszug aus der VB-Online-Hilfe:

 - Es ist nicht möglich, Zeilen aus Microsoft Excel-Tabellen oder -Arbeitsmappen zu löschen.
 - Sie können zwar Daten aus einzelnen Zellen eines Arbeitsblatts löschen, es ist aber nicht
   möglich, Zellen, die Formeln enthalten, zu verändern oder zu löschen.
 - Es ist nicht möglich, Indizes in Microsoft Excel-Tabellen oder -Arbeitsmappen zu erstellen.
 - Es ist nicht möglich, verschlüsselte Daten mit Microsoft Excel-IISAM zu lesen. Sie können ein
   verschlüsseltes Arbeitsblatt oder eine verschlüsselte Arbeitsmappe nicht mit dem in der
   Verbindungszeichenfolge enthaltenen PWD-Parameter öffnen, auch wenn Sie das richtige
   Kennwort angeben. Sie müssen die Verschlüsselung aller Microsoft Excel-Tabellen oder
   -Arbeitsmappen über die Microsoft Excel-Benutzeroberfläche aufheben, wenn Sie sie mit
   der Microsoft Jet-Datenbank verknüpfen oder öffnen möchten.

Um diese Einschränkung zu umgehen, enthalten sowohl die CD- als auch die Track-Tabelle eine Spalte namens 'DEL', in welcher ein Löschkennzeichen ("X") eingetragen wird, falls der Datensatz gelöscht wird. Dies muss dann bei allen SQL-Kommandos berücksichtigt werden, damit solche Zeilen nicht im Recordset auftauchen.

Eine physikalische Entfernung dieser 'gelöschten' Zeilen ist dann leider nur möglich, indem man die XLS-Datei explizit in Excel öffnet und die Datensätze dort manuell löscht.

Die Verwaltung von Excel-Daten über ein Data-Control ist also nur dann sinnvoll, wenn entweder nur Abfragen erfolgen sollen oder wenn man immer nur Daten hinzufügt (wie es in der Regel bei einer CD-Sammlung der Fall ist).

3. Suchen über SQL:
 Es ist zwar kein Problem, mit Hilfe von SQL durch Filterung von Feldinhalten einen Recordset zu generieren, entsprechende Befehle ('=' oder 'LIKE') halten sich jedoch strikt an die Groß- und Kleinschreibung, so dass z.B. der Begriff "HipHop" nicht gefunden wird, wenn man als Suchtext "hiphop" eingibt.

In dieser Beziehung ist Access toleranter; dort werden Groß- und Kleinschreibung nicht unterschieden. Eine mögliche Lösung dieses Problems wäre, alle Texte durchweg in Großbuchstaben einzugeben und den Such-String vor seiner Verwendung mittels der VB-Funktion Ucase() ebenfalls in Großbuchstaben zu wandeln. Obwohl dies in unserem Fall durchaus machbar gewesen wäre (die Track-Titel auf den meisten CD-Covern sind nämlich größtenteils in Großbuchstaben angegeben), habe ich aus Gründen der besseren Lesbarkeit darauf verzichtet. Um z.B. alle CDs zu suchen, auf denen sich Stücke von Jimi Hendrix befinden, sollte man daher "endrix" eingeben, um eine korrekte Trefferliste zu erhalten.

 Können die Einträge der CD-Liste auch sortiert angezeigt werden?

Ja, durch Anklicken eines Spaltenheaders wird die jeweilige Spalte alphabetisch sortiert; gleichzeitig wird die Reihenfolge aller Zeilen dieser Sortierung angepasst.

Durch Klicken auf eine bereits sortierte Spalte wird die Sortierung umgekehrt (aufsteigend <-> absteigend). Hierzu benötigen wir zwei Variablen, in denen zum einen der Index der zuletzt sortierten Spalte (CDcolSorted) und zum anderen die zuletzt benutzte Sortierreihenfolge (CDsortAsc) vermerkt werden.

Der entsprechende Code wird am besten im ColumnClick-Event des ListViews plaziert:

Private Sub lvCD_ColumnClick(ByVal ColumnHeader As
                             MSComctlLib.ColumnHeader)
'Spalte sortieren
Dim i As Long

  i = ColumnHeader.Index
  If (CDcolSorted > 0) Then 'Sortierung liegt vor
    If (CDcolSorted = i) Then 'selbe Spalte
      CDsortAsc = Not CDsortAsc 'Sortierung umkehren
    Else 'andere Spalte
      CDcolSorted = i
    End If
  Else
    CDsortAsc = True: CDcolSorted = i
  End If

  MousePointer = vbHourglass
  With lvCD
    .SortKey = i - 1
    .SortOrder = IIf(CDsortAsc, lvwAscending, lvwDescending)
    .Sorted = True
  End With
  MousePointer = vbDefault
End Sub

 Wie erreicht man es, daß alle Spalten optimale Breite haben?

Hierzu benutzen wir den SendMessage-Befehl der API, mit dem wir das ListView-Control veranlassen, bestimmte Spalten auf optimale Breite zu setzen.

Hier der relevante Code:

Public Declare Function SendMessage Lib "user32" Alias
       "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, _
       ByVal wParam As Long, ByRef lParam As Any) As Long

Public Const LVM_FIRST = &H1000
Public Const LVM_SETCOLUMNWIDTH = (LVM_FIRST + 30)

Public Sub SizeColumnWidth(ByRef lv As ListView, ByVal c As
       ColumnHeader)

'bestimmte Spalte eines ListViews auf optimale Breite setzen
Dim l As Long

  l = -1
  SendMessage lv.hwnd, LVM_FIRST + 30, c.Index - 1, l
End Sub

Die Sub SizeColumnWidth bezieht sich nur auf eine einzelne Spalte. Um gleich alle Spalten auszurichten, benutzen wir die Sub AdjustColWidths:

Public Sub AdjustColWidths(ByVal lv As ListView)

'alle Spalten eines ListViews auf optimale Breite setzen
Dim i As Long

  For i = 1 To lv.ColumnHeaders.Count
    SizeColumnWidth lv, lv.ColumnHeaders(i)
  Next
End Sub

 Datenbanken liefern bei leeren Text-Feldern oft den Wert NULL zurück. In diesen Fällen tritt
    bei der Zuweisung an einen String der Fehler
'falscher Typ' o.ä. auf. Wie kann dies
    vermieden werden?

Um den Wert NULL korrekt in einen Leerstring (und umgekehrt) zu wandeln, werden bei Abfragen und Zuweisungen zwei Hilfsfunktionen benutzt, die solche Fälle abfangen:

Public Function FieldValue(ByVal v As Variant) As String 'konvertiert DB-Feldinhalt NULL zu ""

  If
IsNull(v) Then
    FieldValue = "" 'leeres Feld -> "-"
  Else
    FieldValue = v
  End If
End
Function

Public
Function MaskValue(ByVal s As String) As Variant
'konvertiert Masken-Feldinhalt "" zu NULL

 
s = Trim(s)
  If
(Len(s) = 0) Then
    MaskValue = Null 'leeres Feld -> NULL
 
Else
    MaskValue = s
  End If
End
Function

 Im Fenster der Track-Maske dürfen in den Feldern für Tracknummer und Spieldauer
    (Minuten:Sekunden) nur Ziffern (0...9) eingegeben werden. Wie kann man die
    entsprechenden TextBoxen dazu bringen, ausschließlich Ziffern entgegenzunehmen?

Die naheliegendste Lösung bestünde darin, im KeyPress-Event den Parameter KeyAscii insoweit zu kontrollieren, als man nur die ASCII-Codes für Ziffern und natürlich die üblichen Steuerzeichen wie Backspace, Entf usw. zulässt. Man kann sich jedoch den Aufwand mit IF- und Select-Anweisungen sparen: mit Hilfe der API lässt sich einer TextBox mitteilen, dass sie als Eingabe nur Ziffern annehmen darf. Hier der relevante Code:

Public Declare Function GetWindowLong Lib "user32" Alias _
       "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) _
       As
Long
Public
Declare Function SetWindowLong Lib "user32" Alias _
       "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, _
       ByVal dwNewLong As Long) As Long

Public
Const GWL_STYLE = (-16)
Public
Const ES_NUMBER = &H2000&

Public
Sub OnlyNumbers(ByVal txt As TextBox)
'in TextBox nur Ziffern 0...9 zulassen

Dim curstyle As Long
Dim
newstyle As Long

  curstyle = GetWindowLong(txt.hwnd, GWL_STYLE) Or ES_NUMBER
  SetWindowLong txt.hwnd, GWL_STYLE, curstyle
  txt.Refresh
End
Sub

TIP: Man könnte die Sub OnlyNumbers noch um einen Boolean-Parameter erweitern, und diesen Nur-Ziffern-Modus durch Ausmaskieren der Variablen curstyle beliebig ein- und ausschalten.

Die entsprechende Zeile für das 'Normalisieren' der TextBox hieße dann:

curstyle = GetWindowLong(txt.hwnd, GWL_STYLE) And Not ES_NUMBER

 Wieso kann ich in den Textfeldern der Masken und im Suchstring kein Apostroph (')
    eingeben?

In einem SQL-String existieren diverse Zeichen, die syntaktisch - gerade bei der Selektion - eine bestimmte Bedeutung haben. Darunter fallen Zeichen wie Hochkommas (" und '), Wildcard (*) oder eckige Klammer ([). Werden diese nun unkontrolliert als Teil eines Suchausdrucks benutzt, kann es zu Fehlermeldungen des datenbankinternen SQL-Parsers kommen. Deshalb lasse ich sie der Einfachheit halber von der Sub CheckSqlAscii aussortieren:

Public Sub CheckSqlAscii(ByRef KeyAscii As Integer)
'Unerlaubte Zeichen abfangen (wegen SQL): ",',*,[

  Select Case (KeyAscii)
    Case
34, 39, 42, 91: KeyAscii = 0
  End Select
End
Sub

TIP: Als Ersatz für das Apostroph kann ein Accent (´ oder `) genommen werden. 

 Die Maskenfenster sowie der Suchdialog besitzen keine Titelleiste und müssen zum
    Verschieben direkt angeklickt und gezogen werden. Wie funktioniert das?

Hierzu werden Form_Mouse-Ereignisse benutzt - der verallgemeinerte Code:

Private Dragging As Boolean 'Flag: Fenster wird bewegt
Private DownX As Long, DownY As Long

Private
Sub Form_MouseDown(Button As Integer, Shift As Integer, _
        X As Single, Y As Single)

  If
(<X,Y in Titlebar-Bereich>) Then '---- Titelleiste
    DownX = X: DownY = Y
    Dragging = True
  End If
End
Sub

Private
Sub Form_MouseMove(Button As Integer, Shift As Integer, _
        X As Single, Y As Single)

  If
(Dragging) Then '---- Fenster nachziehen
    Move Left + (X - DownX) * Screen.TwipsPerPixelX, _
    Top + (Y - DownY) * Screen.TwipsPerPixelY
  End If
End
Sub

Private
Sub Form_MouseUp(Button As Integer, Shift As Integer, _
        X As Single, Y As Single)

  Dragging = False
End
Sub
 
Schlusswort voriges Thema [ Top ] nächstes Thema

Um die CD-Datenbank unter ACCESS laufen zu lassen bzw. sie auf ADO oder DAO umzurüsten, bedarf es nur weniger Änderungen die SQL-Kommandos können 1:1 übernommen werden.

Als weitere Features wären übrigens denkbar:

  Anzeige des (eingescannten) CD-Covers: zusätzliches Feld (in Tabelle 'CD') mit Verweis auf eine
     Bilddatei.
  Möglichkeit zum 'Reinhören' in einen Track: zusätzliches Feld (in Tabelle 'Track') mit Verweis auf
     eine Audiodatei (z.B. 10 Sekunden des Refrains als MP3).
  Möglichkeit zum Abspielen einer CD / direktes Anwählen von Tracks der Trackliste bzw.
     automatisches Anlegen von Trackdatensätzen mit Vorbelegung der Felder 'TrackNummer' und
     'Spieldauer' durch direkte CD-Analyse.
  Archivierung verschiedener CD-Typen (Audio, Daten, DVD, Multimedia/Hybrid) mit Unterschieden
     in den ListView-Icons sowie den Track-Tabellen.
  Bereitstellung der Datenbank für den Internetzugriff.

Wie du siehst, haben wir das Thema 'Cd-Archivierung' mit dem CdBank-Projekt lediglich angekratzt, es gibt noch eine Fülle von denkbaren Erweiterungen... lass dich inspirieren ;)

 
Download des Projekts voriges Thema [ Top ]   

Im Anschluss können Sie sich hier das fertig Projekt herunterladen.

  Download
CD Bank.zip
 (32,4 kB)
Downloadzeit: <1 Min. - 28.8k / <1 Min. - ISDN Downloads bisher: [ 25349 ]

Startseite | VB/VBA-Tipps | Projekte | Tutorials | API-Referenz | Komponenten | Bücherecke | VB-/VBA-Forum | VB.Net-Forum | DirectX | DirectX-Forum | Foren-Archiv | VB.Net | Chat | Links | Suchen | Stichwortverzeichnis | Feedback | Impressum

Seite empfehlen Bug-Report
Letzte Aktualisierung: Sonntag, 24. August 2003