Access-Datenbanken mit Powershell durchsuchen

In einer von Microsoft Produkten dominierten Arbeitsumgebung ist MS Access ein ziemlich geniales Programm, um für Systemadministratoren einfach strukturierte Daten übersichtlich aufzubereiten. Bei der Organisation großer Datenmengen, wie sie bei der Verwaltung von Hardware, Benutzern, Patchfeldern und so weiter anfallen, ist Excel mit der Aufbereitung oft überfordert. Meist sind es aber auch noch nicht genug Daten, dass sich das Aufsetzen eines SQL-Servers lohnt. Außerdem liegt es für Administratoren nahe, Informationen aus den Datenbanken mit der Powershell zu verarbeiten. Doch hier liegt der Hund begraben, denn das Thema ist nicht ganz so einfach. Am Ende des Artikels zeige ich, wie man Access-Datenbanken mit der Powershell durchsucht, auch ohne, dass MS Access installiert ist.

Die Powershell und Datenbank-Provider

Während man den ersten Gehversuch startet, kann man auch schon gleich auf Probleme stoßen, zum Beispiel wie „Der Provider ist nicht auf dem lokalen Computer registriert“. Den entsprechenden Provider der Microsoft Access Database Engine zu installieren, oder Betriebssystemarchitektur und Office so anzugleichen, dass beide entweder 32- oder 64-Bit sind, ist besonders in größeren Umgebungen kaum umsetzbar. Gerade hier läuft häufig ein 32-Bit Office auf einem 64-Bit Betriebssystem wie Windows 7 Enterprise.

Sollte der entsprechende Provider installiert sein, benötigt man in so einer Umgebung häufig den Connection-String

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$DatabaseName;"

Ohne den Provider bleibt für Datenbanken mit DSN-Struktur (Data Source Name) noch

"Provider = Microsoft.Jet.OLEDB.4.0;Data Source = $DatabaseName;"

In beiden Fällen muss man auf einem 64-Bit-System darauf achten, dass man die 32-Bit-Version der Powershell nimmt, wenn MS Access auch als 32-Bit_Version vorliegt, was der Normalfall sein dürfte. Beide Provider haben in meinem Fall nicht funktioniert. Zum Testen habe ich eine sehr simple Datenbank „Datenbank.accdb“ mit MS Access 2007 erstellt.

„

Sie besteht aus den beiden Tabellen „User“ und „Customer“ und den Spalten „Login“, „Abteilung“, „Funktion“ und „Name“. Mit der Powershell will ich aus der Tabelle „User“ die Namen aller „Mitarbeiter“ ausgeben. Unterstützen soll mich dabei Scripting Guy.

$path = "C:\Users\Public\Datenbank.accdb"
$adOpenStatic = 3
$adLockOptimistic = 3

Der Pfad zur Datenbank kommt in die Variable $path, anschließend weise ich zwei Variablen den Wert 3 zu. Ich benötige die beiden Variablen später, wenn ich Daten aus der Datenbank abrufe.

$cn = new-object -comobject ADODB.Connection
$rs = new-object -comobject ADODB.Recordset
$cn.Open("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = $path")

Danach erstelle ich mit dem Cmdlet New-Object zwei Referenzen auf die COM-Objekte ADODB.Connection und ADODB.Recordset, um auf die Datenbank überhaut zugreifen zu können. Der Versuch danach, die Datenbank mit dem Provider Microsoft.Jet.OLEDB.4.0 zu öffnen, geht allerdings in die Hose, der Provider erkennt die ACCDB-Datenbank nicht:

Ausnahme beim Aufrufen von "Open" mit 1 Argument(en):  "Nicht erkennbares Datenbankformat 'C:\Users\Public\Datenbank.ac
cdb'."
Bei Zeile:1 Zeichen:9
+ $cn.Open <<<< ("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = $path")
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

Stattdessen den Connection-String Provider = Microsoft.ACE.OLEDB.12.0; zu nehmen, funktioniert in diesem Fall, 64-Bit-Betriebssystem mit 32-Bit-Office ohne entsprechenden ACE-Provider, leider auch nicht:

$cn.Open("Provider = Microsoft.ACE.OLEDB.12.0;Data Source = $path")
$rs.Open("Select * From User", $cn,$adOpenStatic,$adLockOptimistic)

Der String scheint auch erstmal zu funktionieren, kracht dann aber beim Öffnen des Recordsets:

Ausnahme beim Aufrufen von "Open" mit 4 Argument(en):  "Syntaxfehler in FROM-Klausel."
Bei Zeile:1 Zeichen:9
+ $rs.Open <<<< ("Select * From User", $cn,$adOpenStatic,$adLockOptimistic)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

DSN-lose Verbindungen in der Powershell

Die Lösung heißt hier DSN-lose Verbindungen, also eine, die keinen Datenquellennamen (Data Source Name = DSN) verwendet. Das vollständige Skript sieht dann so aus:

$path = "C:\Users\Public\Datenbank.accdb"
$adOpenStatic = 3
$adLockOptimistic = 3
 
$cn = new-object -comobject ADODB.Connection
$rs = new-object -comobject ADODB.Recordset
 
$cn.Open("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=$path")
$rs.Open("Select * From User", $cn,$adOpenStatic,$adLockOptimistic)
 
$rs.MoveFirst()
 
do {
    $funktion = $rs.Fields.Item("Funktion").Value;
    $name  = $rs.Fields.Item("Name").Value;
 
    if ($funktion -eq "Mitarbeiter"){write $Name}
    $rs.MoveNext()
} 
until ($rs.EOF -eq $True)
 
$rs.close()
$cn.close()

Mit dem Befehl rs.Open() wird die SQL-Abfrage formuliert, die hier die komplette Tabelle „User“ einliest. Danach wird der Recordset an den Anfang der Tabelle gesetzt. In der anschließenden Do-Schleife wird der Inhalt der einzelnen Felder aus den Spalten „Funktion“ und „Name“ an entsprechenden Variablen übergeben. Die If-Bedingung siebt dann alle Mitarbeiter heraus, deren Name dann in der Konsole ausgegeben wird. Die Schleife endet, wenn das Ende des Recordsets (EOF) erreicht ist. Danach werden Connection und Recordset noch geschlossen.

Aus den Daten eine CSV-Datei erstellen

So ein Skript ergibt vor allem erst dann Sinn, wenn man die Daten weiterverarbeitet, z.B. in einer CSV-Datei, die man dann wiederum auch weiter verarbeiten kann:

$file = "C:\Users\Public\Mitarbeiter_Produktion.csv"
$path = "C:\Users\Public\Datenbank.accdb"
 
# Erstelle leere CSV-Datei
write-host | export-csv $file -Delimiter ";"
 
$adOpenStatic = 3
$adLockOptimistic = 3
 
$cn = new-object -comobject ADODB.Connection
$rs = new-object -comobject ADODB.Recordset
 
$cn.Open("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=$path")
$rs.Open("Select * From User", $cn,$adOpenStatic,$adLockOptimistic)
 
 
$rs.MoveFirst()
 
do {
    $funktion = $rs.Fields.Item("Funktion").Value;
    $name  = $rs.Fields.Item("Name").Value;
    $login  = $rs.Fields.Item("Login").Value;
    $abteilung = $rs.Fields.Item("Abteilung").Value;
 
    # Schreibe die Ausgabe zeilenweise in die CSV-Datei:
    if ($abteilung -eq "Produktion"){
        $NewLine = '{0};{1};{2}' -f $login,$name,$funktion 
        $NewLine | add-content -path $file 
    }
    $rs.MoveNext()
} 
 
until ($rs.EOF -eq $True)
 
$rs.close()
$cn.close()

Das Ergebnis lässt sich dann in Excel betrachten:

Bei Powershellpraxis.de gibt es übrigens ein interessantes Spezial zu Access und Powershell.

Auf Access-Datenbanken zugreifen ohne MS Access

Beide Skripte sollten auch funktionieren, wenn man kein MS Access unter Windows installiert hat. Dazu benötigt man allerdings die Microsoft Access Database Engine 2010 Redistributable. Wobei man auf die Systemarchitektur achten muss, also die Datei mit X64 am Ende herunterlädt, wenn man ein 64-Bit-Windows hat. Für das Erstellen von CSV-Dateien braucht man glücklicherweise kein Excel. Beim Textimport in LibreOffice muss man allerdings darauf achten, dass der Zeichensatz UTF-8 ist.