# Microsoft Access

Zuar Runner has the ability to ingest `.accdb` or `.mdb` files and output
the data into a desired database location. You must have the Microsoft
Access plugin installed on your Zuar Runner instance. [Create a Zuar support
ticket if you need assistance.](https://zuarkb.atlassian.net/servicedesk/customer/portal/2/group/7)

.. image:: assets/ms-access-1.png
   :alt: Zuar Runner + Microsoft Access

## .accdb/.mdb file

Zuar Runner requires that the `.accdb` or `.mdb` file be accessible within
the Zuar Runner file store . There are many ways to get the file into
Zuar Runner's file store like using [Rclone](/jobs/rclone/) from
a storage location to Zuar Runner, uploading/dropping the file into Zuar Runner's
file page, etc. 


## Meta Table

Once the file exists in Zuar Runner's file store, you first need to create a
meta table which will include a single column and 1 row per table name
from the `.accdb` or `.mdb` file. Create a **Generic Job** from the
Add Job menu. 

.. image:: assets/microsoft-access__generic_job_icon.png
   :alt: Zuar Runner Generic Job Icon

- Give the job a Title like **[Microsoft Access Meta] Table Names**
- Set the Type to **io**
- Paste the following configuration into the job, change the path to
  the location and name of your `.accdb` or `.mdb` file, adjust the
  schema and tablename output to your desired info: 

```json
    {
      input: {
        path: /var/mitto/data/{yourfilename.accdb}
        use: access.iov2#AccessTableNameInput
      }
      output: {
        dbo: postgresql://mitto:{password}@db/analytics
        schema: microsoft_access_meta
        tablename: table_names
        use: call:mitto.iov2.db#todb
      }
      steps: [
        {
          transforms: [
            {
              use: mitto.iov2.transform#ExtraColumnsTransform
            }
            {
              use: mitto.iov2.transform#ColumnsTransform
            }
          ]
          use: mitto.iov2.steps#Input
        }
        {
          use: mitto.iov2.steps#CreateTable
        }
        {
          transforms: [
            {
              use: mitto.iov2.transform#FlattenTransform
            }
          ]
          use: mitto.iov2.steps#Output
        }
        {
          use: mitto.iov2.steps#CollectMeta
        }
      ]
    }
```    

## Database Tables

Next, create individual **io** jobs to input individual tables from
the .accdb file into your desired database. 

- Follow the steps above to create a new **io** job 
- Name the job something like **[Microsoft Access] {Name of Table}**

Use the following configuration (adjust the input table_name to match
the table name in the meta table and .accdb file, edit the output
details to your desired location, schema, and table name):

```json
    {
      input: {
        path: /var/mitto/data/{yourfilename.accdb}
        table_name: {table name}
        use: access.iov2#AccessInput
      }
      output: {
        dbo: postgresql://mitto:{password}@db/analytics
        schema: microsoft_access
        tablename: example_table
        use: call:mitto.iov2.db#todb
      }
      steps: [
        {
          transforms: [
            {
              rename_columns: false
              use: mitto.iov2.transform#ExtraColumnsTransform
            }
            {
              use: mitto.iov2.transform#ColumnsTransform
            }
          ]
          use: mitto.iov2.steps#Input
        }
        {
          use: mitto.iov2.steps#CreateTable
        }
        {
          transforms: [
            {
              use: mitto.iov2.transform#FlattenTransform
            }
          ]
          use: mitto.iov2.steps#Output
        }
        {
          use: mitto.iov2.steps#CollectMeta
        }
      ]
    }
```