Migrate UserLock data from Access to SQL Server

Preserve your existing UserLock data by importing it into a production-ready SQL database.

Published May 1, 2024

Introduction

UserLock ships with a default MS Access database, which is not designed for production use.

If you already have production data stored in Access and need to keep it, you can migrate it to SQL Server (including SQL Express).

This guide explains how to transfer UserLock data using the SQL Server Import and Export Wizard.

Note

Steps

  1. Launch the Import Wizard

    • From the Start menu, search for Import and Export Data (32-bit).

    • Alternatively, run: C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\DTSWizard.exe

    • Click Next.

  2. Select the data source

    • In Data source, select Microsoft Access.

    • In File name, enter the path to the Access DB (default): C:\ProgramData\ISDecisions\UserLock\Database\UserLock.mdb

    • Click Next.

  3. Select the destination

    • Choose SQL Server Native Client.

    • Enter your server name\instance.

    • Select SQL Server Authentication with an account that has owner permissions (e.g. sa).

    • In Database, choose your empty UserLock SQL database.

    • Click Next.

  4. Choose the transfer type

    • Select Copy data from one or more tables or views.

    • Click Next.

  5. Map the tables

    • In the Select Source Tables and Views window, check the table UserLogonEvents.

      • Click Edit Mappings….

      • In the Column mappings window, set the ID column to Ignore, then click OK.

    • Repeat the same action for the tables UserStatus and AdminActionResults.

    • ⚠️ For the table AdminActions, do not change the ID.

    • Once the three mappings are done, select all the required tables and click Next.

  6. Run the import

    • Click Finish.

    • Once the process completes, click Close.

Next steps

The migration only transfers the data. To make UserLock actually use the new SQL database, follow the guide Define a production database.

Best practices

  • Verify the transfer: compare row counts between Access and SQL.

  • Back up your SQL database after migration.

  • Archive the Access file to avoid confusion and ensure UserLock only uses the new database.