Access-2-SQL-Server die Unvollendete … (Migration)

1. Access-2-SQL-Serverdie Unvollendete …(Migration) Thomas Kowoll Björn Schober 17.05.2014

2. Erfahrungsbericht Bitte beachten: Wir sind Anfänger! - Learning bytesting/presenting Blöcke: • „Umgebungsvariablen“ • SSMA-A SQL Server Migrations-Assistenten für Access 5.3 • SQL-Server 2012 (inkl. SQL-Server Profiler = nicht in Express-Edition) • MS Access 2010 • Objekt der Leidenschaft: Klassenbuch-Anwendung (Front-/Backend, Backend als Replikat) • SSMA – Backend- Tabellen migrieren - ohne/mit link-Tables • SSMA – Frontend-Abfragen migrieren • Analyse im SQL-Server-Profiler Vergleich • Parameter-Abfrage

3. SQL-Server-Konfiguration • Protokolle für SQL-Server Netzwerkkonfiguration TCP/IP als Dienst starten • Für SQL-Server-Profiler: SQL Native Client – Clientprotokolle TCP/IP aktivieren • Ohne Primär-Schlüssel kein Update • Bei Änderung des Datentyps: Extras-Optionen-Designer-“Speichern von Änderungen verhindern, die die Neuerstellung einer Tabelle erfordern“ auf ‚nein‘ setzen. ACHTUNG: Gefahr von DatenverlustNur temporär/Entwicklungsphase deaktivieren!

4. Ausgangslage:Klassenbuch-Anwendung • Stundenplan -> Klassenbuch -> Dozenten tragen ein … auch Versäumnisse • Gibt‘s doch schon! Aber nicht so: • Mehrere Berufsbilder in einem Kurs • Berichtshefte • Soll-/Ist-Lehrplan • Performance zu gering - Lösungsansatz: Front- und Backend mit Replikation

5. SSMA for Access 5.3 (32 bit!) Vorbereitung • Feldtypen-Entsprechungstabellen Tools – Project-Settings – Type Mapping, z.B. Datum  datetime2 • SQL-Server-Connect WICHTIG: Entweder „localhost“ oder servername/instanzname --- sonst laaaaange Suche 1. Wizard durchlaufen: Tabellen aus dem Backend • Ohne Link-Tables • mit Link-Tables • Umbenennung der Tabellen im Backend in SSMA$(Tabellenname)$local • Link auf Tabellen im SQL-Server angelegt (ohne „dbo“ im Präfix) • Lokale Anbindung • Keine Änderung im Frontend nötig • Protokoll genau durchgehen • Arten der Einträge • Hinweise: Timestamp-Felder werden automatisch erstellt (Präfix „SSMA“), keine Standardwerte hinterlegt, E-Mail als Textfelder (nicht als „Hyperlink“), Replikationsspalten werden nicht übernommen. • Warnungen: Leerzeichen, Bindestriche, Spaltenname „E-Mail“, Standardwert bei Ja/Nein-Felder • Fehler • Klick auf Protokoll-Einträge bewirkt die Darstellung der Tabelle • Link convertedtables: Synchronisationsfehler bei Replikationsspalten und bei Conflict-Tabellen • Was passiert mit den Systemspalten? 2. Wizard-Durchlauf: Abfragen aus Frontend

6. Performance analysieren:SQL-Server-Profilers. Kap. 5 aus „Access und SQL-Server“ • SQL-Server-Profiler: Nicht in der Express-Edition vorhanden und ist abgekündigt • Alternative: XEvent = Zukunft (Verwaltung – Erweiterte Ereignisse)

7. Parameter-Abfrage Pass-Through-Abfrage via VBA erstellen • Connect • StoredProcedure auf dem SQL-Server • Parameter Public FunctionSPRecordsetMitParameter(strStoredProcedure As String, strVerbindungszeichenfolge As String, ParamArrayvarParameter() As Variant) As DAO.Recordset Dimdb As DAO.Database Dimqdf As DAO.QueryDef DimstrParameter As String Set db = CurrentDb Set qdf = db.CreateQueryDef("") strParameter = Parameterliste(varParameter) Withqdf .Connect = strVerbindungszeichenfolge .SQL = "EXEC " & strStoredProcedure & " " & strParameter Set SPRecordsetMitParameter = .OpenRecordset On Error GoTo 0 End With Set db = Nothing End Function

8. Ohne dbo-Präfix • Public Sub Remove_DBO_Prefix() • Dim SQL As StringDim DB As databaseDim RS As Recordset • SQL = “SELECT Name FROM MSysObjectsWHERE (((Left([Name],4))=’dbo_’));” • Set DB = CurrentDb()Set RS = DB.OpenRecordset(SQL) • If RS.EOF = FalseThenRS.MoveFirst • Do UntilRS.EOF • DoCmd.RenameMid(RS!name, 5, 100), acTable, RS!nameRS.MoveNext • LoopRS.Close • End If • End Sub

9. Code-snippet: Link 2 SQL Public Sub TabellenRelink (tblNameAs String, strNewLink As String) ' ------------------------------------------------------- ' Diese Funktion ändert den Link auf die Tabelle ‚tblName‘. ' ------------------------------------------------------- DimtdfLoop DimNewName As String ForEachtdfLoop In CurrentDb().TableDefs IftdfLoop.Name = tblNameThen tdfloop.Connect = strNewLink tdfloop.RefreshLink Exit For End If Next tdfLoop End Sub Bsp. für ConnectString : ODBC;Description=SQL-Server BE;DRIVER=SQL Server;SERVER=KOWOLL-THINK-14;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=KB1

10. Empfehlungen • Developer-Version vom MS SQL-Server 2012 kostet ca. 60 EUR, sie ist sehr leistungsstark und enthält alle wichtigen Features. • Buchtipp: Jungbluth und Minhorst „Access und SQL Server“ gehen sehr detailliert und praxisorientiert auf das Thema Migration ein.