Office: Verbinden von Access mit SQL Server

Verbinden von Access mit SQL Server
Access für Microsoft 365 Access 2019 Access 2016 Access 2013 Access 2010 Mehr... Weniger Denken Sie an die glücklichen Tage Ihrer Kindheit, als Sie so großen Spaß mit Ihrer Buchstabensuppe hatten. Bewahren Sie diese Erinnerungen im Hinterkopf, während wir die Datenbankversion der Buchstabensuppe kennenlernen und probieren werden. In den folgenden Abschnitten werden die Grundlagen des Zugriffs auf eine Datenbank mit Verbindungszeichenfolgen und Verwenden einer Datenbank-Programmierschnittstelle in Ihrem Access-VBA-Code beschrieben.



Inhalt dieses Artikels
Verwenden von ODBC-Treiber oder OLE DB-Anbieter

Programmgesteuerte Schnittstelle zu SQL Server über Access

Zusammenfassung zu ODBC-Treiberversionen

Zusammenfassung zu OLE DB-Anbieterversionen

Übersicht über ODBC-Schlüsselwörter

Übersicht über OLE DB-Schlüsselwörter

Verwenden von ODBC-Treiber oder OLE DB-Anbieter
Verbindungszeichenfolgen gibt es schon eine geraume Weile. Man kann eine formatierte Verbindungszeichenfolge entweder über die Access-Benutzeroberfläche oder in VBA-Code definieren. Eine Verbindungszeichenfolge (ob ODBC oder OLE DB) gibt Informationen direkt an die Datenbank weiter, beispielsweise den Serverspeicherort, den Datenbanknamen, den Sicherheitstyp und andere nützliche Optionen. Beispiel:

Zuerst gab es SQL Server Native Client (SNAC), eine eigenständige Bibliothek, die ODBC- und OLEDB-Technologien umfasste und weiterhin für SQL Server-Versionen 2005 bis 2012 verfügbar ist. Viele ältere Anwendungen verwendeten SNAC, und es wird immer noch aus Gründen der Abwärtskompatibilität unterstützt, es wird jedoch nicht empfohlen, es für die Entwicklung neuer Anwendungen zu verwenden. Sie sollten neuere, herunterladbare Versionen der ODBC-Treiber verwenden.

ODBC-Treiber

Open Database Connectivity (ODBC) ist ein Protokoll, das zum Herstellen einer Verbindung zwischen einer Access-Datenbank und einer externen Datenquelle, wie etwa Microsoft SQL Server, verwendet werden kann. In der Regel werden Dateidatenquellen (auch als DSN-Dateien bezeichnet) verwendet, um eine Verbindungszeichenfolge hinzuzufügen. In diesem Fall wird das FILEDSN-Schlüsselwort in der Verbindungszeichenfolge verwendet, während bei der Speicherung in der Registrierung das DSN-Schlüsselwort verwendet wird. Alternativ können Sie VBA verwenden, um diese Eigenschaften mit einer "DSN-Less"-Verbindungszeichenfolge festzulegen.

Bei der Auslieferung von ODBC-Treibern hat es im Laufe der Jahre drei Phasen gegeben:

• Vor 2005 wurden ODBC-Treiber mit Windows Data Access Components (WDAC) ausgeliefert, das ursprünglich als Microsoft Data Access Components (MDAC) bezeichnet wurde. Diese Komponenten sind weiterhin aus Gründen der Abwärtskompatibilität in Windows integriert. Weitere Informationen finden Sie unter Microsoft- oder Windows-Datenzugriffskomponenten.
  
• ODBC-Treiber wurden mit SNAC für SQL Server 2005 bis SQL Server 2012 ausgeliefert.
  
• Seit SQL Server 2012 werden ODBC-Treiber einzeln ausgeliefert und enthalten Unterstützung für neue SQL Server-Funktionen.
  

ODBC-Treiber aus den ersten beiden Phasen sollten nicht für Neuentwicklungen verwendet werden. Verwenden Sie stattdessen ODBC-Treiber aus der dritten Phase.

OLE DB-Anbieter

Object Linking and Embedding, Database (OLE DB) ist ein aktuelleres Protokoll, das zum Herstellen einer Verbindung zwischen einer Access-Datenbank und einer externen Datenquelle, wie etwa Microsoft SQL Server, verwendet werden kann. OLE DB erfordert keinen DSN und bietet außerdem Vollzugriff auf ODBC-Datenquellen und ODBC-Treiber.

Tipp    Normalerweise wird das Dialogfeld für Datenverknüpfungseigenschaften verwendet, um eine OLE DB-Verbindungszeichenfolge hinzuzufügen. Es gibt zwar keine Möglichkeit, das Dialogfeld Datenverknüpfungseigenschaften aus Access heraus zu öffnen, Sie können jedoch in Windows-Explorer eine leere .txt-Datei erstellen, den Dateityp in .udl ändern und dann auf die Datei doppelklicken. Erstellen Sie die Verbindungszeichenfolge und ändern Sie anschließend den Dateityp wieder in .txt.

Bei der Auslieferung von OLE DB-Anbietern hat es im Laufe der Jahre drei Phasen gegeben:

• Vor 2005 wurden OLE DB-Anbieter mit Windows Data Access Components (WDAC) ausgeliefert, das ursprünglich als Microsoft Data Access Components (MDAC) bezeichnet wurde.
  
• OLE DB-Provider wurden mit SNAC für SQL Server 2005 bis SQL Server 2017 ausgeliefert. Die Unterstützung wurde 2011 eingestellt.
  
• Seit 2017 wird der OLE DB-Anbieter für SQL Server wieder unterstützt.
  

Die derzeit empfohlene Version für die Entwicklung neuer Lösungen ist OLE DB-Treiber 18 für SQL Server.

Optimieren der Leistung mithilfe einer ODBC-Verbindungszeichenfolge

Verwenden Sie so wenige Verbindungszeichenfolgen wie möglich, um die Leistung zu optimieren, den Netzwerkdatenverkehr zu minimieren und den Mehrbenutzerzugriff auf die SQL Server-Datenbank zu verringern, indem Sie Verbindungszeichenfolgen über mehrere Datensatzgruppen freigeben. ACE übergibt zwar nur eine Verbindungszeichenfolge an den Server, versteht und verwendet jedoch die folgenden Schlüsselwörter: DSN, DATABASE, UID, PWD und DRIVER, um zur Reduzierung der Client-Server-Kommunikation auf ein Minimum beizutragen.

Hinweis    Wenn eine ODBC-Verbindung zu einer externen Datenquelle unterbrochen wird, versucht Access automatisch, eine erneute Verbindung herzustellen. Wenn die Wiederherstellung gelingt, können Sie weiterarbeiten. Wenn sie fehlschlägt, können Sie weiterhin mit Objekten arbeiten, die von der Verbindung unabhängig sind. Um die Verbindung erneut herzustellen, schließen Sie Access und öffnen Sie es erneut.

Empfehlungen für die Verwendung von ODBC und OLE DB

Vermeiden Sie das Mischen von Technologien für Verbindungszeichenfolgen und Datenbankzugriff. Verwenden Sie eine ODBC-Verbindungszeichenfolge für DAO. Verwenden Sie eine OLE DB-Verbindungszeichenfolge für ADO. Sollte Ihre Anwendung VBA-Code enthalten, der sowohl DAO als auch ADO verwendet, verwenden Sie den ODBC-Treiber für DAO und den OLE DB-Anbieter für ADO. Sorgen Sie möglichst dafür, dass Funktionen und Unterstützungen sowohl für ODBC als auch für OLE DB immer auf dem neuesten Stand sind.

ODBC verwendet den Begriff-Treiber und OLE DB verwendet den Begriffs-Anbieter. Die Begriffe beschreiben den gleichen Softwarekomponententyp, sind jedoch in der Syntax von Verbindungszeichenfolgen nicht austauschbar. Verwenden Sie den korrekten Wert wie dokumentiert.

Seitenanfang

Programmgesteuerte Schnittstelle zu SQL Server über Access
Grundsätzlich gibt es zwei Hauptmethoden, um eine programmgesteuerte Verknüpfung einer SQL Server-Datenbank aus Access zu erstellen.

DAO

Ein Datenzugriffsobjekt (DAO, Data Access Object) stellt eine abstrakte Schnittstelle zu einer Datenbank bereit. Microsoft-Datenzugriffsobjekte (DAO) ist das native Programmier-Objektmodell, mit dem Sie im Innersten von Access und SQL Server Objekte, Tabellen, Felder, Indizes, Beziehungen, Abfragen, Eigenschaften und externe Datenbanken erstellen, löschen, ändern und auflisten können finden.

Weitere Informationen finden Sie unter Microsoft-Datenzugriffsobjekte – Referenz.

ADO

ActiveX Data Objects (ADO) stellt ein Programmiermodell auf hoher Ebene bereit und ist in Access über einen Verweis auf eine Drittanbieter-Bibliothek verfügbar. ADO ist einfach zu erlernen und ermöglicht Clientanwendungen den Zugriff auf und die Bearbeitung von Daten aus einer Vielzahl von Quellen, einschließlich Access und SQL Server. Seine Hauptvorteile sind die Anwenderfreundlichkeit, die hohe Geschwindigkeit sowie der geringe Arbeitsspeicher- und Festplattenbedarf. ADO unterstützt zudem die wichtigsten Funktionen für die Erstellung webbasierter Anwendungen.

Weitere Informationen finden Sie unter Microsoft ActiveX Data Objects – Referenz und Microsoft ActiveX Data Objects (ADO).

Was sollten Sie verwenden?

In einer Access-Lösung, die VBA-Code verwendet, können Sie DAO, ADO oder beides als Datenbankschnittstellen-Technologie verwenden. DAO ist weiterhin die Standardoption in Access. Beispielsweise wird für alle Formulare und Berichte sowie Access-Abfragen DAO verwendet. Wenn Sie jedoch zu SQL Server migrieren, empfiehlt sich die Verwendung von ADO, um Ihre Lösung effizienter zu gestalten. Hier sind einige allgemeine Richtlinien, die bei der Entscheidung, ob Sie DAO oder ADO verwenden sollten, hilfreich sein können.

Verwenden Sie DAO für Folgendes:

• Das Erstellen eines gebundenes Formular mit Lese-/Schreibberechtigung ohne Verwendung von VBA.
  
• Das Abfragen lokaler Tabellen.
  
• Das Herunterladen von Daten in temporäre Tabellen.
  
• Das Verwenden von Pass-Through-Abfragen als Datenquellen für Berichte oder Formulare im schreibgeschützten Modus.
  
• Das Definieren und Verwenden eines TableDef- oder QueryDef-Objektes in VBA.
  

Verwenden Sie ADO für Folgendes:

• Die Nutzung zusätzlicher Optimierungsoptionen, z. B. das Ausführen asynchroner Vorgänge.
  
• Das Ausführen von DDL- und DML-Pass-Through-Abfragen.
  
• Direkten Zugriff auf SQL Server-Daten über Recordsets in VBA.
  
• Das Schreiben von einfacherem Code für bestimmte Aufgaben wie z. B. das Streaming von Blobs.
  
• Das direkte Aufrufen einer gespeicherten Prozedur mithilfe von Parametern über ein Befehlsobjekt in VBA.
  

Seitenanfang

Zusammenfassung zu ODBC-Treiberversionen
In der folgenden Tabelle finden Sie eine Zusammenfassung der wichtigsten Informationen zu ODBC-Treiberversionen, Downloadspeicherorten und zur Unterstützung von Funktionen. Stellen Sie sicher, dass Sie die richtige Bit-Version (64-Bit oder 32-Bit) des Treibers verwenden, basierend auf Windows und nicht auf Office. Wenn Sie die 32-Bit-Version von Access auf der 64-Bit-Version von Windows ausführen, installieren Sie 64-Bit-Treiber, die die für Access erforderlichen 32-Bit-Komponenten enthalten.

Weitere Informationen hierzu finden Sie unter Verwenden von Schlüsselwörtern für Verbindungszeichenfolgen mit SQL Server Native Client, Versionshinweise zu ODBC für SQL Server unter Windows (V17) und Funktionen von Microsoft ODBC Driver für SQL Server unter Windows (v13,11).

ODBC-Treiber	Version	Herunterladen	Neue Funktionen
ODBC-Treiber 17.0 bis 17.3	SQL Server 2017	Herunterladen	ODBC-Treiber 17.3 Verwenden von Azure Active Directory mit dem ODBC-Treiber Einschränkungen des ODBC-Treibers bei der Verwendung von "immer verschlüsselt" Verwenden von XA-Transaktionen ODBC-Treiber 17.2 Verwenden von "immer verschlüsselt" mit dem ODBC-Treiber für SQL Server Datenklassifizierung UTF-8-Servercodierung mit Sortierungs- und Unicode-Unterstützung ODBC-Treiber 17.1 Verwenden von "immer verschlüsselt" mit dem ODBC-Treiber für SQL Server ODBC-Treiber 17.0 Immer verschlüsselt UseFMTONLY    Zur Verwendung alter Metadaten in Sonderfällen, für die temporäre Tabellen erforderlich sind. Siehe Versionshinweise zu ODBC für SQL Server unter Windows. Unterschiede bei der Verwendung verwalteter Instanzen (ODBC-Version 17)
ODBC-Treiber 13.1	SQL Server 2016 SP1, SQL Azure	Herunterladen	Immer verschlüsselt Azure Active Directory AlwaysOn-Verfügbarkeitsgruppen Treiberkompatibles Verbindungspooling im ODBC-Treiber für SQL Server
ODBC-Treiber 13.0	SQL Server 2016	Herunterladen	Internationalisierter Domainname (Internationalized Domain Name, IDN)
ODBC-Treiber 11.0	SQL Server 2005 bis 2012	Herunterladen	Driverkompatibles Verbindungspooling Verbindungsresilienz im Windows ODBC-Treiber Asynchrone Ausführung Dienstprinzipalnamen (Service Principal Names, SPN) in Clientverbindungen (ODBC) Funktionen des Microsoft ODBC-Treibers für SQL Server unter Windows

Seitenanfang

Zusammenfassung zu OLE DB-Anbieterversionen
In der folgenden Tabelle finden Sie eine Zusammenfassung der wichtigsten Informationen zu OLE DB-Anbieter-Versionen, Downloadspeicherorten und zur Unterstützung von Funktionen. Stellen Sie sicher, dass Sie die richtige Bit-Version (64-Bit oder 32-Bit) des Treibers verwenden, basierend auf Windows und nicht auf Office. Wenn Sie die 32-Bit-Version von Access auf der 64-Bit-Version von Windows ausführen, installieren Sie 64-Bit-Treiber, die die für Access erforderlichen 32-Bit-Komponenten enthalten.

Weitere Informationen finden Sie unter Verwenden von Schlüsselwörtern für Verbindungszeichenfolgen mit SQL Server Native Client.

OLE DB-Anbieter	Version	Herunterladen	Neue Funktionen
OLE DB Driver 18.2.1 (MSOLEDBSQL)	SQL Server 2017	Herunterladen	OLE DB-Treiber für SQL Server-Funktion und Versionshinweise für den Microsoft OLE DB-Treiber für SQL Server.
SQL Server Native Client (SQLNCLI)	SQL Server 2005 bis 2012		Veraltet, nicht verwenden
OLE DB-Treiber (SQLOLEDB)			Veraltet, nicht verwenden

Seitenanfang

Übersicht über ODBC-Schlüsselwörter
Die folgende Tabelle enthält eine Zusammenfassung der von SQL Server erkannten ODBC-Schlüsselwörter und deren Zweck. Nur eine Teilmenge wird von Access erkannt.

Schlüsselwort	Description
Addr	Die Netzwerkadresse des Servers, auf dem eine Instanz von SQL Server ausgeführt wird.
AnsiNPW	Gibt die Verwendung von ANSI-definierten Verhaltensweisen zum Behandeln von NULL-Vergleichen, Auffüllung mit Zeichendaten, Warnungen und NULL-Verkettungen an (Ja oder Nein).
APP	Der Name der SQLDriverConnect aufrufenden Anwendung.
ApplicationIntent	Deklariert den Arbeitsauslastungstyp der Anwendung beim Herstellen einer Verbindung mit einem Server (ReadOnly oder ReadWrite).
AttachDBFileName	Name der primären Datei einer anfügbaren Datenbank.
AutoTranslate	Gibt an, ob ANSI-Zeichenfolgen zwischen dem Client oder Server übermittelt oder in Unicode übersetzt werden (Ja oder Nein).
Datenbank	Name der Datenbank. Description Der Zweck der Verbindung. Driver Der Name des Treibers, der von SQLDrivers zurückgegeben wird.
DSN	Der Name einer vorhandenen ODBC-Benutzer- oder Systemdatenquelle. Encrypt Gibt an, ob Daten vor dem Senden über das Netzwerk verschlüsselt werden sollen (Ja oder Nein).
Failover_Partner	Name des Failoverpartnerservers, der verwendet werden soll, wenn keine Verbindung mit dem primären Server hergestellt werden kann.
FailoverPartnerSPN	Der SPN für den Failoverpartner.
Fallback	Veraltetes Schlüsselwort.
FileDSN	Der Name einer vorhandenen ODBC-Dateidatenquelle. Language Name der SQL Server-Sprache.
MARS_Connection	Definiert die Verwendung von mehreren aktiven Resultsets (MARS) bei einer Verbindung für SQL Server 2005 (9.x) oder höher (Ja oder Nein).
MultiSubnetFailover	Gibt an, ob eine Verbindung mit dem Verfügbarkeitsgruppen-Listener einer SQL Server-Verfügbarkeitsgruppe oder einer Failoverclusterinstanz hergestellt werden soll (Ja oder Nein).
Net	dbnmpntw gibt benannte Pipes und dbmssocn gibt TCP/IP an.
PWD	Das SQL Server-Anmeldekennwort.
QueryLog_On	Gibt die Protokollierung von langwierigen Abfragen an (Ja oder Nein).
QueryLogFile	Vollständiger Pfad- und Dateiname einer Datei, die zur Protokollierung von Daten über langwierige Abfragen verwendet werden soll.
QueryLogTime	Ziffernzeichenfolge, die den Schwellenwert (in Millisekunden) zum Protokollieren von langwierigen Abfragen angibt.
QuotedId	Gibt an, ob SQL Server die ISO-Regeln hinsichtlich der Verwendung von Anführungszeichen in SQL-Anweisungen befolgen soll (Ja oder Nein).
Regional	Gibt an, ob der ODBC-Treiber von SQL Server Native Client die Clienteinstellungen verwendet, wenn Währungs-, Datums- oder Zeitdaten in Zeichendaten konvertiert werden (Ja oder Nein).
SaveFile	Der Name einer ODBC-Datenquellendatei, in der die Attribute der aktuellen Verbindung gespeichert werden, wenn die Verbindung erfolgreich hergestellt wurde.
Server	Der Name einer SQL Server-Instanz: Server im Netzwerk, eine IP-Adresse oder der Aliasname eines Konfigurations-Managers.
ServerSPN	Der SPN für den Server.
StatsLog_On	Ermöglicht die Aufzeichnung von Leistungsdaten zum ODBC-Treiber von SQL Server Native Client.
StatsLogFile	Der vollständige Pfad- und Dateiname einer Datei, die zum Aufzeichnen der statistischen Daten zur Leistung des ODBC-Treibers von SQL Server Native Client verwendet werden soll.
Trusted_Connection	Gibt an, ob zur Überprüfung der Anmeldung der Windows-Authentifizierungsmodus oder ein SQL Server-Benutzernamen bzw. ein Kennwort verwendet werden soll (Ja oder Nein).
TrustServerCertificate	Bei Verwendung mit Encrypt wird die Verschlüsselung mit einem selbstsignierten Serverzertifikat aktiviert.
UID	Der SQL Server-Anmeldename.
UseProcForPrepare	Veraltetes Schlüsselwort.
WSID	Die ID der Arbeitsstation, der Netzwerkname des Computers, auf dem sich die Anwendung befindet.

Seitenanfang

Übersicht über OLE DB-Schlüsselwörter
Die folgende Tabelle enthält eine Zusammenfassung der von SQL Server erkannten OLE DB-Schlüsselwörter und deren Zweck. Nur eine Teilmenge wird von Access erkannt.

Schlüsselwort	Description
Addr	Die Netzwerkadresse des Servers, auf dem eine Instanz von SQL Server ausgeführt wird.
APP	Die Zeichenfolge, die die Anwendung identifiziert.
ApplicationIntent	Deklariert den Arbeitsauslastungstyp der Anwendung beim Herstellen einer Verbindung mit einem Server (ReadOnly oder ReadWrite).
AttachDBFileName	Name der primären Datei einer anfügbaren Datenbank.
AutoTranslate	Konfiguriert die OEM/ANSI-Zeichenübersetzung ("true" oder "false").
Connect Timeout	Der Zeitraum (in Sekunden), der bis zum Abschluss der Datenquelleninitialisierung abgewartet werden soll.
Current Language	Name der SQL Server-Sprache.
Data Source	Der Name einer SQL Server-Instanz in der Organisation.
Datenbank	Name der Datenbank.
DataTypeCompatibility	Eine Zahl, die angibt, welcher Modus der Datentyp-Verarbeitung verwendet wird.
Encrypt	Gibt an, ob Daten vor dem Senden über das Netzwerk verschlüsselt werden sollen (Ja oder Nein).
FailoverPartner	Der Name des für die Datenbankspiegelung zu verwendenden Failoverservers.
FailoverPartnerSPN	Der SPN für den Failoverpartner.
Initial Catalog	Name der Datenbank.
Initial File Name	Der Name der Primärdatenbank (einschließlich des vollständigen Pfadnamens) einer anfügbaren Datenbank.
Integrated Security	Für die Windows-Authentifizierung verwendet (SSPI).
Language	Name der SQL Server-Sprache.
MarsConn	Definiert die Verwendung von mehreren aktiven Resultsets (MARS) bei einer Verbindung für SQL Server 2005 (9.x) oder höher (Ja oder Nein).
Net	Die Netzwerkbibliothek, die zum Herstellen einer Verbindung mit einer Instanz von SQL Server in der Organisation verwendet wird.
Network Address	Die Netzwerkadresse einer SQL Server-Instanz in der Organisation.
PacketSize	Netzwerkpaketgröße. Die Standardeinstellung ist 4096.
Persist Security Info	Gibt an, ob Persist Security aktiviert ist ("true" oder "false").
PersistSensitive	Gibt an, ob Persist Sensitive aktiviert ist ("true" oder "false").
Provider	Für SQL Server Native Client muss dies "SQLNCLI11" sein.
PWD	Das SQL Server-Anmeldekennwort.
Server	Der Name einer SQL Server-Instanz: Server im Netzwerk, eine IP-Adresse oder der Aliasname eines Konfigurations-Managers.
ServerSPN	Der SPN für den Server.
Timeout	Der Zeitraum (in Sekunden), der bis zum Abschluss der Datenquelleninitialisierung abgewartet werden soll.
Trusted_Connection	Gibt an, ob zur Überprüfung der Anmeldung der Windows-Authentifizierungsmodus oder ein SQL Server-Benutzernamen bzw. ein Kennwort verwendet werden soll (Ja oder Nein).
TrustServerCertificate	Gibt an, ob ein Serverzertifikat überprüft wird ("true" oder "false").
UID	Der SQL Server-Anmeldename.
Use Encryption for Data	Gibt an, ob Daten vor dem Senden über das Netzwerk verschlüsselt werden sollen ("true" oder "false").
UseProcForPrepare	Veraltetes Schlüsselwort.
WSID	Die ID der Arbeitsstation, der Netzwerkname des Computers, auf dem sich die Anwendung befindet.

Seitenanfang

Siehe auch
Verwalten von ODBC-Datenquellen

Verwalten verknüpfter Tabellen

:)