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.

Mainzer Datenfabrik - Datenaustausch zwischen SQL Server und Access

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.

c:\temp> accessdatabaseengine.exe /quiet

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.

c:\temp> accessdatabaseengine_64.exe

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

Microsoft Excel
Pfad und Name der Excel Datei. Hier wurde eine zuvor heruntergeladene Beispieldatei von Microsoft verwendet, die im Internet bereitgestellt wird.
Im dritten Feld wird die Excel Version ausgewählt, in diesem Fall “Microsoft Excel 2016”
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.

SELECT segment, country,product FROM `Sheet1$` where segment like '%ern%'

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.

Datenaustausch zwischen SQL Server und Access

Zum Zugriff auf Access Datenbanken muss die 64 bit Version accessdatabaseengine_X64.exe der Access DB Engine installiert werden. Auch an dieser Stelle der Hinweis auf Inkompatibilitäten zwischen der 32 und der 64 bit Version. Bei Verwendung der /quiet Option beim Aufruf der Installation lässt sich die 64 bit Version zwar trotz vorhandener 32 bit Installation durchführen, aber bei der Nutzung der OLE DB Schnittstelle kommt es zu Fehlern durch die Inkompatibilität der beiden Versionen. In diesem Fall hilft dann nur beide Versionen zu deinstallieren und die 64 bit Version neu zu installieren. In einigen Fällen war darüber hinaus ein Neustart der SQL Server Instanz erforderlich.

Nachdem die Access DB Engine sauber installiert wurde, sieht man entsprechende Provider im SQL Management Studio entsprechend folgender Abbildung:

Konfiguration der Schnittstelle

Nach erfolgter Installation muss der Zugriff auf die Access Datenbank konfiguriert werden. Hierzu wird über das Kontext-Menü auf dem SSMS Verbindungsserver-Knoten ein Neuer Verbindungsserver eingerichtet. Man gelangt in das folgende Fenster, in dem im Wesentlichen nur die Access Datei ausgewählt wird, mit der man eine Verbindung aufbauen möchte.

Im Feld Verbindungsserver wird ein frei wählbarer Name eingetragen unter dem der Linked Server im SSMS Objektbaum aufgelistet werden soll
Im Feld Anbieter wird der Provider ausgewählt. Im vorliegenden Fall der für den Office 16.0 Access Zugriff
Der Produktname kann hier ebenfalls frei gewählt werden. Hier wurde der Name der Access-Datei gewählt.
Als Datenquelle wird der komplette Pfad der Access Datenbank angegeben.
Die beiden Unterseiten zur Sicherheit und zu den Serveroptionen können mit ihren Default-Werten übernommen werden. Hier sollte aber zur Sicherheit überprüft werden, ob dies für die benötigte Verwendung passt. Hier seien insbesondere
- die Sortierung
- das Transaktionsverhalten
- die RPC Verwendung bei Verwendung von Stored Procedures

auf der Unterseite Serveroptionen genannt.

Nachdem der Access Zugriff wie oben dargestellt eingerichtet wurde, sieht man das zugehörige Linked Server Objekt im SQL Server Management Studio. In den folgenden beiden Abbildungen wird das Objekt mit aufgeklappten Unterknoten für Tabellen und Views dargestellt.

Zum Zugriff auf die Access Objekte können zwei Mechanismen verwendet werden

Data Manipulation Language (DML) Statements zum Zugriff auf die Objekte, also select, insert, update, delete
openquery() Aufrufe

Zugriff mit SQL Syntax

Am Einfachsten ist der Zugriff mit der von MSSQL gewohnten Syntax. Hier ist zu berücksichtigen, dass ein kompletter Tabellenname aus den Komponenten

Datenbank
Schema
einfacher Tabellenname

besteht, also z.B. <Meine_DB>.<Mein_Schema>.<Meine_Tabelle>.

Durch den Linked Server kommt eine weitere Komponente hinzu. Innerhalb von Access gibt es aber weder Datenbanken noch Schemas, sodass die beiden Komponenten Datenbank und Schema entfallen und der komplette Tabellenname drei Punkte enthält, also beispielsweise

select * from [<Mein_Linked_Server_Name>]…[<Meine_Tabelle>]

Eine Beispielabfrage ist in der folgenden Abbildung wiedergegeben:

Zugriff mit `openquery()`

Als Alternative zu der bekannten DML Syntax können Aufrufe der Prozedur openquery() durchgeführt werden. Hier ist die Syntax etwas gewöhnungsbedürftig und erfordert bei Verwendung für eine existierende Applikation entsprechend eine Umkodierung der DML Statements, was zu einem hohen Aufwand führen kann.

Beispiel zu Abfragen mit openquery()
Im folgenden Codeblock werden beispielhaft ein

select
update
insert
delete

Statement unter Verwendung von openquery() wiedergegeben. Als Beispieltabelle wird hier die Kunden-Tabelle der Northwind Datenbank verwendet.

----------
- SELECT -
----------
select * from openquery([NORTHWIND DEMO DB]
                       , 'select [Firma]
                       , [Nachname]
                       , [Vorname]
                       , [Position]
  FROM [Kunden]');
-- Ergebnis (Auszug):
-- Firma	Nachname	 Vorname	     Position
-- Firma A	Bedecs	     Anna	         Inhaberin
-- Firma B	Gratacos     Solsona Antonio Inhaber
-- Firma C	Axen	     Thomas	         Einkaufsmitarbeiter
-- Firma D	Lee	         Christina	     Einkaufsmanagerin
-- Firma E	O’Donnell	 Martin	         Inhaber
-- Firma F	Pérez-Olaeta Francisco	     Einkaufsmanager

----------
- UPDATE -
----------
update openquery([NORTHWIND DEMO DB]
                , 'select * from [Kunden] where
				   Nachname = ''Lee'' and
				   Vorname  = ''Christina''')
				set Position = 'Inhaberin';
-- Test des Update:
select * from openquery([NORTHWIND DEMO DB]
                       , 'select [Firma]
                       , [Nachname]
                       , [Vorname]
                       , [Position]
  FROM [Kunden]');
-- Ergebnis (Auszug nur für Frau Lee):
-- Firma	Nachname Vorname   Position
-- Firma D	Lee	     Christina Inhaberin
    
--------------------------------------------------
- INSERT                                         -
--------------------------------------------------
insert openquery([NORTHWIND DEMO DB]
                ,'select [Firma]
				        ,[Nachname]
						,[Vorname]
						,[E-Mail-Adresse]
						,[Position]
						,[Straße]
						,[Ort]
						,[Webseite]
					from Kunden')
values('MaDaFa'
      ,'Schackenberg'
	  ,'Benedikt'
	  ,'benedikt@madafa.de'
	  ,'Inhaber'
	  ,'Taunusstraße 72'
	  ,'Mainz'
	  ,'www.madafa.de');
-- Test des Insert:
select [Firma]
	  ,[Nachname]
	  ,[Vorname]
	  ,[E-Mail-Adresse]
	  ,[Position]
	  ,[Straße]
	  ,[Ort]
	  ,[Webseite]
	  from openquery([NORTHWIND DEMO DB]
	                ,'select * from [Kunden]where Nachname = ''Schackenberg''');
-- Firma	Nachname	 Vorname	E-Mail-Adresse	    Position Straße	            Ort	    Webseite
-- MaDaFa	Schackenberg Benedikt	benedikt@madafa.de	Inhaber	 Taunusstraße 72	Mainz	www.madafa.de
--
-- Alternativ kann übrigens auch das folgende Statement verwendet werden
--
select * from openquery([NORTHWIND DEMO DB]
                ,'select [Firma]
				        ,[Nachname]
						,[Vorname]
						,[E-Mail-Adresse]
						,[Position]
						,[Straße]
						,[Ort]
						,[Webseite]
					from Kunden where Nachname = ''Schackenberg''');

----------
- DELETE -
----------
delete openquery([NORTHWIND DEMO DB]
                ,'select * from [Kunden] where Nachname = ''Schackenberg''');
 -- Test
 select * from openquery([NORTHWIND DEMO DB]
                ,'select [Firma]
				        ,[Nachname]
						,[Vorname]
						,[E-Mail-Adresse]
						,[Position]
						,[Straße]
						,[Ort]
						,[Webseite]
					from Kunden where Nachname = ''Schackenberg''');
-- Firma	Nachname	Vorname	E-Mail-Adresse	Position	Straße	Ort	Webseite
-- Kein Ergebnisdatensatz mit dem angegebenen Kriterium

Vorteil und Nachteil von `openquery()` gegenüber SQL

Bei der Verwendung der klassischen SQL Syntax ohne openquery() werden zunächst die Inhalte der betroffenen Tabelle(n) ohne Rücksicht auf die Prädikate (= where-Kriterien) vom Remote Server gelesen und lokal unter Verwendung der Prädikate verarbeitet. Damit müssen also unter Umständen sehr viele Datensätze übertragen werden und eine Verwendung von Indizes ist nicht möglich. Wird also effektiv eine einzige Zeile einer Tabelle im Umfang von 1 GB benötigt, so findet zunächst ein Transfer der 1 GB Daten in die lokale Datenbank statt, anschließend erfolgt dann ein Full Table Scan um die benötigte Information zu ermitteln und ggf. zu ändern.

Im Gegensatz dazu werden openquery() Statements in der Remote Datenbank ausgeführt, das heißt in der Datenbank, die die Informationen über möglicherweise verwendbare Indizes und die aktuellen Statistiken besitzt und damit einen optimalen Datenzugriff durchführen kann. Bei dem hier dargestellten Fall wird also remote zunächst die eine Zeile ermittelt, diese wird anschließend in die lokale Datenbank übertragen.

Der Nachteil wurde bereits erwähnt und ist auch aus den o.a. Beispielen zu openquery() ersichtlich: Es wird eine vom Standard abweichende Syntax benötigt, die im Fall einer Umstellung auf openquery() einen erheblichen Codierungsaufwand bedeuten kann.

Fazit

In diesem Dokument wurden zwei Möglichkeiten des Datenzugriffs von MSSQL Datenbanken auf Office-Produkte dargestellt. Diese Vorgehensweisen können natürlich auch im Rahmen einer Migration von Office-Daten in MSSQL Datenbanken verwendet werden. Bei weiterführenden Fragen unterstützen unsere Expert:innen Sie gerne. Kontaktieren Sie uns dafür gerne ganz unverbindlich über unser Kontaktformular.

Datenaustausch zwischen SQL Server und Access

Einstieg

Voraussetzung

Installation der Access DB Engine 32 bit

Installation der Access DB Engine 64 bit

Import von Excel-Daten

Datenaustausch zwischen SQL Server und Access

Konfiguration der Schnittstelle

Zugriff mit SQL Syntax

Zugriff mit `openquery()`

Vorteil und Nachteil von `openquery()` gegenüber SQL

Fazit

Auf dieser Seite

Interesse geweckt?

Unsere Expert:innen stehen Ihnen bei allen Fragen rund um Ihre IT Infrastruktur zur Seite.

Datenaustausch zwischen SQL Server und Access

Einstieg

Voraussetzung

Installation der Access DB Engine 32 bit

Installation der Access DB Engine 64 bit

Import von Excel-Daten

Datenaustausch zwischen SQL Server und Access

Konfiguration der Schnittstelle

Zugriff mit SQL Syntax

Zugriff mit openquery()

Vorteil und Nachteil von openquery() gegenüber SQL

Fazit

Auf dieser Seite

Interesse geweckt?

Unsere Expert:innen stehen Ihnen bei allen Fragen rund um Ihre IT Infrastruktur zur Seite.

Zugriff mit `openquery()`

Vorteil und Nachteil von `openquery()` gegenüber SQL