Exporting a Kingdom SQL Database to Access

If you have a Kingdom project stored as a Microsoft SQL database, you could convert it to an Access database to avoid all the requirements of an SQL database, including being able to access your data on Linux/Mac. Follow the steps below to export an SQL database to Access by using either Kingdom's Database Admin Tool or Microsoft SQL Server's Import and Export Wizard. Attached is an empty Access database file that you would need if you are using the second method.

 

Attached Files

Export using Kingdom's DB Admin Tool

If you have Kingdom installed on your computer, you can access the Database Admin Tool that comes with Kingdom to create a database dump file and a dummy Access project, and do a database dump from SQL to Access.

Notes:

  • If your SQL database (mdf and ldf files) is larger than 2GB, it will be too large to dump into an Access database.
  • To work with an Access database, a 32-bit Kingdom DB Admin Tool is required. If you have a 64-bit Kingdom, you will need access to a 32-bit Kingdom.

Open Kingdom's DB Admin Tool

Open Kingdom's DB Admin Tool
  1. At the Start menu, click on All Programs.
  2. Open the Database Admin Tool under the Kingdom Software.
  3. From the Database menu, click Connect to connect to the project database.
  4. Select DB type (Auto/Manual SQL) and select the Kingdom project’s .tks file.
  5. The database should appear in the main window. Highlight the database.
  6. At the Project menu, select Export to file. This will create a database dump file (export.dmp). This file will have all the information from your SQL database file.
  7. Then, create a dummy Access project with Kingdom, this will create an empty Access database (.mdb).

Import from file

To import the database dump file into the dummy Access project:

  1. From the Database menu, click Connect to connect to the Access database.
  2. Select Access and navigate to the Access database file (.mdb). Select the project's .tks file.
  3. The database should appear in the main window. Highlight the database.
  4. At the Project menu, select Import from file.
  5. Import the database dump file (export.dmp) into the new Access database. You will now have all the information from the SQL database in an Access database.

Proceed to Insight and Kingdom Overview to begin linking your Access database with Insight.

Export using SQL Server Import/Export Wizard

If you do not have Kingdom, you can also convert an SQL database to Access by using the SQL Server Import and Export Wizard. To begin:

  1. Download the empty Access database file attached at the top of this article (empty.mdb) and copy it to your Kingdom project directory.
  2. Rename the Access database <project_name>70sav.mdb to adhere to Kingdom's naming convention for inactive Access databases. This will help remove any confusion about which database Kingdom will use (the SQL database will remain as the project's active database) as Kingdom does not allow users to select when opening a project.

Notes:

  • To work with an Access database, a 32-bit SQL Server Import and Export Wizard is required. To install the 32-bit version, select either Client Tools or SQL Server Data Tools (SSDT) during setup.
  • Also, the SQL Server Import and Export Wizard is only available in SQL Server 2008 and newer versions.

Open SQL Server Import and Export Wizard

Open SQL Server Import and Export Wizard
  1. Click the Start button to access the Start menu.
  2. At the Start menu, click on All Programs and expand the Microsoft SQL Server 20XX folder. Click on Import and Export Data to open the Import/Export wizard.
    Alternatively, you can access the wizard from SQL Server Management Studio by right-clicking on a database, selecting Tasks and click Import/Export Data.
  3. Click Next to proceed.
  4. In the Choose a Data Source page, choose SQL Server Native Client as your Data Source and complete the following:
    • Server name — Type the name of the server or choose from the drop-down list.
    • Use Windows/SQL Server Authentication — Choose whether the wizard should use Windows or SQL Server Authentication to log in to the database. If you choose SQL Server Authentication, input a User name and Password.
    • Database — Select from the list the database you wish to export.
    • Refresh — Click to restore the list of available databases.
  5. Click Next.
  1. In the Choose a Destination page, select Microsoft Access as your data destination and complete the following:
    • File name — Browse and select the empty Access database file (empty.mdb) given in this article.
    • User name & Password — Leave these blank. The empty database does not require any kind of authentication.
  2. Click Next to proceed.
  1. Select Copy data from one or more tables or views and click Next.
  1. In the Select Source Tables and Views window, select all tables by clicking on the top left check box.
  2. Click Next to review data type mapping.
  1. At the Review Data Type Mapping page, select Ignore from the drop-down list at On Truncation (global) to allow the wizard to ignore any truncation and continue the process. By default, this is set to Fail.
  2. Click Next to proceed with the rest of the settings.
  3. Once the export is successful, you will have all the information from the SQL database in the Access database.
  4. Save the package and set your preferred protection level.

This process only creates an Access database for use with Insight. It does NOT convert the Kingdom project itself to Access. Any changes made in the Kingdom project after this will render the Access database out-of-date and the user must then repeat this process.

Note: The SQL Server Import and Export Wizard tool is NOT part of Insight. As such, please make sure to check that your data is unaffected after completing this process.

Proceed to Insight and Kingdom to begin linking your Access database with Insight.