Datenaustausch zwischen SQL Server und Access

Einstieg

Im Internet findet man viele Hilfsgesuche von verzweifelten SQL Server Verwendern die versuchen, Daten aus Excel nach SQL Server zu importieren bzw. zwischen SQL Server und Access auszutauschen. Ein solches Vorgehen ist insbesondere dann sinnvoll, wenn man sich für eine Migration der Office Daten nach SQL Server entschieden hat.

In diesem Artikel wird die Vorgehensweise zum Zugriff auf diese beiden Office Produkte beschrieben. Im Fall von Excel unidirektional, im Fall von Access bidirektional unter Verwendung von SQL Statements.

Die hier verwendeten Microsoft Produkte und Versionen sind

• Windows 11 Pro Version 21H2 (Build 22000.1219)
• SQL Server 2019 15.0.2095.3 (X64)
• Access und Excel auf Office 365 Version 2210 (Build 15726.20202)

Wichtig: Dieser Artikel beschreibt lediglich die Lösung des Benutzerproblems. Ob die hier beschriebene Vorgehensweise von Microsoft unterstützt wird, ist unbekannt und nicht Gegenstand dieses Beitrags. Das heißt insbesondere, dass man vor einer Umsetzung in einer wichtigen und/oder produktiven Umgebung eine entsprechende Anfrage beim Microsoft Support stellen oder zumindest in einer separaten Testumgebung intensiv testen muss, ob die gewünschte Konfiguration fehler- und absturzfrei arbeitet.

Voraussetzung

Zum Zugriff auf externe Datenbanken verwendet SQL Server eine Komponente die sich Linked Server nennt und sich auf Zugriffsmodule mit dem Namen Provider abstützen. SQL Server bietet per Default bereits einige Standard Provider an, insbesondere natürlich für Zugriffe auf externe SQL Server Datenbanken. Um von SQL Server aus auf die beiden Office 365 Produkte zugreifen zu können, muss ein zusätzlicher Provider installiert werden, der in dem Software Paket Microsoft Access Database Engine 2016 Redistributable enthalten ist und über den Link Microsoft Access Database Engine 2016 Redistributable von Microsoft heruntergeladen werden kann. Für den Excel-Zugriff wird die 32 bit Version (accessdatabaseengine.exe) benötigt, für Access die 64 bit Version (accessdatabaseengine_X64.exe). Die gleichzeitige Verwendung der 32 und der 64 bit Version auf dem gleichen Server führt zu einem Absturz.

Es ist nur möglich entweder:

• Excel-Daten in SQL Server zu importieren (32 bit)
  oder
• Access-Daten mit SQL Server auszutauschen (64 bit)

Installation der Access DB Engine 32 bit

Der Zugriff auf Excel Dateien erfolgt über den Import Wizard des SQL Server Management Studios. Unglücklicherweise liegt dieser Wizard immer noch als 32 bit Version vor, sodass für den Import die 32 bit Database Engine verwendet werden muss. Zunächst einmal lässt sich diese nicht auf dem normalen Weg installieren. Beim Aufruf des Executables in der Konfiguration entsprechend dem einleitenden Überblicksabschnitt erhält man die Fehlermeldung, dass man die 32 bit Version nicht installieren könne, weil man bereits eine 64 bit Office Version installiert habe.

Ruft man die Installation aus der cmd-Shell oder der PowerShell heraus auf und verwendet den /quiet Parameter, so verläuft die Installation erfolgreich.

An dieser Stelle erneut der Hinweis: Die 32 und die 64 bit Version der Access DB Engine können mit dem /quiet Parameter erfolgreich installiert werden, sind aber inkompatibel zueinander und führen beim Import von Excel-Daten zu einem Absturz. Es kann also nur eine Version geben, in diesem Fall 32 bit.

Installation der Access DB Engine 64 bit

Die 64 bit Version der Database Engine erfolgt durch Ausführung der Datei accessdatabaseengine_X64.exe. Auch hier die Warnung bezüglich der Inkompatibilität der 32 und der 64 bit Versionen zueinander. Mit der /quiet Option lässt sich die 64 bit Version trotz vorhandener 32 bit Installation installieren, führt aber bei der späteren Verwendung ebenfalls zu Abstürzen.

Import von Excel-Daten

Nachdem ausschließlich die 32 bit Version der Access DB Engine installiert wurde, kann der Datenimport im SQL Server Management Studio durchgeführt werden. Hierzu wird die Datenbank ausgewählt, in die die Excel Datei importiert werden soll. Mit der rechten Maustaste wird Tasks -> Daten importieren ausgewählt. Es öffnet sich der Import-Wizard. Auf der Startseite wird mit dem Next Button auf die zweite Seite navigiert.

Auf der zweiten Seite wird nach dem Typ der Datenquelle, dem Dateinamen und der Excel Version gefragt. Hier geht es um den Excel Import, so dass die vier auszufüllenden Felder hier wie folgt angegeben werden

1. Microsoft Excel
2. Pfad und Name der Excel Datei. Hier wurde eine zuvor heruntergeladene Beispieldatei von Microsoft verwendet, die im Internet bereitgestellt wird.
3. Im dritten Feld wird die Excel Version ausgewählt, in diesem Fall “Microsoft Excel 2016”
4. Im vierten Feld wird der Default-Parameter beibehalten, der die erste Zeile als Spaltennamen interpretiert

Auf der dritten Seite wird das Ziel des Imports erfragt. Da das Ziel im vorliegenden Beispiel eine Tabelle in einer SQL Server Instanz sein soll, wird hier Microsoft OLE DB Provider for SQL Server ausgewählt. Im zweiten Feld wird die Zielinstanz <Servername>\<Instanzname> ausgewählt. Im dritten Bereich wurde die von unserer Instanz verwendete Windows Authentifizierung (Default-Wert) gewählt und im vierten Feld schließlich die Datenbank, in die die Excel-Daten übernommen werden sollen.

Auf der vierten Seite besteht die Möglichkeit, den Datenimport auf bestimmte Zeilen und Spalten zu beschränken. Wegen der gewöhnungsbedürftigen Syntax wurde diese Möglichkeit hier als Beispiel verwendet.

Bei Auswahl des Radio Buttons Abfrage zum Angeben der zu übertragenden Daten schreiben folgt eine fünfte Seite zur Angabe einer Select-Anweisung.

Das Excel-Blatt, aus dem die Daten stammen sollen, muss hier in umgekehrten Hochkommata und mit einem abschließenden Dollarzeichen spezifiziert werden. In diesem Beispiel werden nur die Spalten Segment, Country und Product ausgewählt und nur diejenigen Zeilen deren Segmentnamen die Zeichenkette ‘ern’ enthalten. Bei Bedarf kann das SQL-Statement über den Analysieren Button syntaktisch überprüft werden.

Auf der sechsten Seite besteht die Möglichkeit, das Schema und den Namen der Zieltabelle (hier der Default-Name für das Ziel einer Abfrage: [dbo].[Abfrage]) zu vergeben und auf der hierzu gehörenden Unterseite, die über den Button Zuordnungen bearbeiten erreichbar ist, die Spalten zuzuordnen und Datentypen der Zieltabelle zu spezifizieren.

Auf der 7. Seite schließlich kann der Import direkt ausgeführt oder als SSIS Paket gespeichert werden. In diesem Beispiel wird die Abfrage sofort ausgeführt.

Auf der nächsten Seite erfolgt eine kurze Zusammenfassung der auf den vorherigen Seiten vorgenommenen Einstellungen (hier ohne Abbildung) und nach Bestätigung wird der Import mit der Erfolgsseite beendet.