Click or drag to resize

Database Migration

Database migration from SOWI Auftragsbearbeitung customer Bike Windlin to database Velo of web application Velo

Name MS SQL database: SOWIAuftragsbearbeitungBikeWindlin2021

Name MS Access database SOWI Auftragsbearbeitung: BikeShopWindlin_2.sowiauf

Database password SOWI Auftragsbearbeitung MDB: sowi

See also SQL scripts

This topic contains the following sections:

Prepare MS Access SOWI Auftragsbearbeitung MDB

Open MDB in MS Access

Table ADRVEL

  • Correcting table ADRVEL field adrvelDatumVon with SQL statement in a View:

    Copy
    UPDATE ADRVEL SET ADRVEL.adrvelDatumVon = #1/1/1990#
WHERE (((ADRVEL.adrvelDatumVon) Is Null));

  • Correcting table ADRVEL field adrvelDatumBis with SQL statement in a View:

    Copy
    UPDATE ADRVEL SET ADRVEL.adrvelDatumBis = "#01/01/1990#"
WHERE (((ADRVEL.adrvelDatumBis) Is Null));

  • Check manual column adrvelDatumVon and adrvelDatumBis of date.
    Open table ADRVEL and sort by column.

Table VEL

  • Correcting table VEL field velDatum with SQL statement in a View:

    Copy
    UPDATE VEL SET VEL.velDatum = #1/1/1990#
WHERE (((VEL.velDatum) Is Null));

  • Check manual column velDatum of date.
    Open table VEL and sort by column velDatum.

MS Access database to MS SQL
Note Note

Handling in MS SQL Server Management Studio

Has exist migrated database so create a backup and delete this.

Database migration from MDB to SQL

  1. Rename database BikeShopWindlin_2.sowiauf to BikeShopWindlin_2.mdb

  2. Open MS SQL Server Management Studio

  3. MS SQL Server Management Studio

    1. Create a new empty database by name SOWIAuftragsbearbeitungBikeWindlin2021

    2. Open content menu of new empty database (SOWIAuftragsbearbeitungBikeWindlin2021) in the Object Explorer.

      Select menu: Tasks, Import Data....
      Open dialog SQL Server Import and Export Wizard

      Setup import. Dialog "SQL Server Import and Export Wizard"

      1. Go to step dialog: Choose a Data Source

      2. Choose Data Source: to Microsoft Access (Microsoft Jet Database Engine"

      3. Select MDB file(button Browse...).

      4. Setup import MDB button Acvanced...
        Open dialog Datenverknüpfungseigenschaften

      5. Setup import MDB. Dialog: "Datenverknüpfungseigenschaften"

        1. Register: Alle

          Property: Jet OLEDB:Database Password

          Set value: sowi

        2. Register: Verbindung

          Button Verbindung testen

    3. Go to step dialog: Specify Table Copy or Query

    4. Select option: Copy data from one or more tables or views

    5. Go to step dialog: Select Source Tables and Views

    6. Select all tables (ADR, ADRVEL, ART, AUF, RAP, VEL) to import

    7. Button Edit Mappings... repeat all selected tables.
      Open dialog: Column Mappings

      Setup column mappings. Dialog: "Column Mappings"

      • Activate checkbox: Enable identity insert

    8. Go to Finish

SQL database validating (SOWI Database Migration Tool)
Note Note

This procedure until all tables

SOWI Database Migration Tool

  1. Select Table

  2. Load Source

    Check data

  3. Clean up Source

    This write a SQL statement in register SQL Script

    Execute SQL Script

    This write result of execute SQL script

  4. Load Target

    Check data

  5. Drop Target

    This write a SQL statement in register SQL Script

    Execute SQL Script

    This write result of execute SQL script

  6. Create Target

    This write a SQL statement in register SQL Script

    Execute SQL Script

    This write result of execute SQL script

  7. Transfer

    Note Note

    Migration of sales write directly to source database table Sales

    This write a SQL statement in register SQL Script

    Execute SQL Script

    This write result of execute SQL script

  8. Clean up Target

    This write a SQL statement in register SQL Script

    Execute SQL Script

    This write result of execute SQL script

Delete migrated database on MS SQL Server Management Studio
Note Note

This procedure is in single steps if the database cannot be deleted directly.

Example of message from Microsoft SQL Server Management Studio:
Drop failed for Database 'SOWIAuftragsbearbeitungBikeWindlin2021'. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot drop database "SOWIAuftragsbearbeitungBikeWindlin2021" because it is currently in use. (Microsoft SQL Server, Error: 3702)

Delete komplett database

  1. Delete tables

  2. Delete database