Ziel: Access-Frontend mit stabilem SQL Server-Backend

Access als Oberfläche. SQL Server als Speicher.
Skalierbarer, sicherer, mehr Benutzer, weniger Stress.

Du willst:

• Tabellen im SQL Server
• Access-Formulare bleiben erhalten
• Performance und Sperrprobleme im Griff

Variante 1: SQL Server als verknüpfte Tabellen

Schritt 1: Tabellen in SQL Server anlegen

CREATE TABLE dbo.Kunden (
    ID INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(100),
    Email NVARCHAR(255),
    Aktiv BIT
)

Schritt 2: Access → „Externe Daten“ → ODBC-Datenbank verknüpfen

DSN oder DSN-less möglich.
Verknüpfung erzeugt dbo_Kunden in Access.

Achte auf:

• Primärschlüssel
• Datentypen (z. B. kein ntext)
• Null-Werte (SQL NULL ≠ Access "")

Variante 2: DSN-lose ODBC-Verknüpfung per VBA

Public Sub SQLVerknuepfen()
    Dim tdf As TableDef
    Dim db As DAO.Database
    Set db = CurrentDb

    On Error Resume Next
    db.TableDefs.Delete "dbo_Kunden"
    On Error GoTo 0

    Set tdf = db.CreateTableDef("dbo_Kunden")
    tdf.Connect = "ODBC;Driver={SQL Server};Server=SRV01;Database=MeineDB;Trusted_Connection=Yes;"
    tdf.SourceTableName = "dbo.Kunden"
    db.TableDefs.Append tdf
End Sub

Erspart Setup mit DSN auf jedem Client.

Variante 3: Access nur als Frontend, SQL per ADODB

Nur lesen/schreiben via Code – kein direkter Formularzugriff.

Public Sub KundenAuslesen()
    Dim cnn As Object, rs As Object
    Set cnn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    cnn.Open "Provider=SQLOLEDB;Data Source=SRV01;Initial Catalog=MeineDB;Integrated Security=SSPI;"
    rs.Open "SELECT * FROM dbo.Kunden WHERE Aktiv=1", cnn

    Do Until rs.EOF
        Debug.Print rs!Name
        rs.MoveNext
    Loop

    rs.Close: cnn.Close
End Sub

Vorteil: volle Kontrolle, weniger Abhängigkeiten
Nachteil: kein Formular-Binding

Variante 4: SQL Server Migration Assistant (SSMA)

Access-Projekt nach SQL migrieren:

1. SSMA installieren
2. Access-Datenbank auswählen
3. Tabellen konvertieren
4. Daten übertragen
5. Access-Formulare verknüpfen

Achte auf:

• AutoWert → IDENTITY
• Datumsfelder
• Memo → nvarchar(max)
• Makros und VBA bleiben in Access

Performance-Tipps

• Nur verknüpfen, was Du brauchst
• Abfragen im SQL Server als Views anlegen
• dbo.vw_AktiveKunden → in Access als Tabelle einbinden
• TOP 100, WHERE, JOIN immer auf SQL-Ebene

Beispiel: SQL-View für Access vorbereiten

CREATE VIEW vw_AktiveKunden AS
SELECT ID, Name, Email FROM dbo.Kunden WHERE Aktiv = 1

Dann in Access verknüpfen wie eine Tabelle.

Beispiel: Daten schreiben per ADODB

Public Sub NeuerKunde(Name As String, Email As String)
    Dim cnn As Object
    Set cnn = CreateObject("ADODB.Connection")

    cnn.Open "Provider=SQLOLEDB;Data Source=SRV01;Initial Catalog=MeineDB;Integrated Security=SSPI;"
    cnn.Execute "INSERT INTO dbo.Kunden (Name, Email, Aktiv) VALUES ('" & Replace(Name, "'", "''") & "', '" & Replace(Email, "'", "''") & "', 1)"
    cnn.Close
End Sub

Achtung: SQL-Injection absichern bei externen Eingaben.

Best Practices

• kein SELECT *
• Felder explizit benennen
• bei Performanceproblemen: SQL Server Profiler nutzen
• regelmäßig Database Compact im Frontend
• Access .accde verteilen, Backend bleibt zentral

Wenn Du Synchronisierung brauchst

• Access-Tabelle lokal
• SQL Server-Tabelle zentral
• Sync-Tabelle mit LetzteÄnderung, GUID, SyncStatus

Oder: Nur SQL Server + Offline-Modul als Option.

Variante 5: Pass-Through-Queries für maximale Performance

Wenn Du SQL Server richtig arbeiten lassen willst, nimm Pass-Through-Queries.
Die laufen direkt auf dem Server, ohne dass Access die Abfrage interpretiert.
Kein ODBC-Gefrickel mit Jet/ACE – nur reines T-SQL.

So geht’s:

1. Neue Abfrage → Ansicht auf SQL umstellen
2. Beispiel-SQL:

EXEC dbo.spUmsatzNachRegion '2025-01-01', '2025-12-31'

3. In den Eigenschaften:

• ODBC-Verbindungszeichenfolge eintragen:
  ODBC;Driver={SQL Server};Server=SRV01;Database=MeineDB;Trusted_Connection=Yes;

Vorteile:

• läuft komplett auf dem SQL Server
• keine Zwischenauswertung durch Access
• ideal für komplexe Views, Stored Procedures oder Performance-Spezialfälle

Zugriff in VBA:

DoCmd.OpenQuery "qryPT_Umsatz"

Oder:

CurrentDb.QueryDefs("qryPT_Umsatz").Execute

Wichtig: Pass-Through-Abfragen sind read-only, außer Du rufst einen EXEC auf, der etwas schreibt.
Du brauchst also saubere Prozeduren im SQL Server, wenn Du Daten ändern willst.