Automate and Replace your Access Databases

Did you know that you can use SolveXia to automate and replace your Microsoft Access databases?

• Access databases are often used to augment processes where data sets have become large (10k+ rows).
• Common uses for Access include combining, filtering and aggregating data.
• SolveXia has pre-configured instructions that work with your existing Access databases. This includes getting data in/out and running queries.

Introduction

Access databases are a popular way to combine and manipulate large data sets. Finance staff sometimes use Access to augment their reporting as follows:

In the example above, Access offers a more scalable way to manage and prepare data - particularly when data sets become large (10,000 rows or more). The Access database is often used to:

a) Join or map data (alternative to vlookups in Excel).

b) Filter data through queries.

c) Aggregate data.

Visual Basic is also sometimes used to orchestrate very complex Access databases. That is, the designer creates buttons and writes code to run queries in the database in a particular order.

A few examples of processes that may use Access:

1. Aggregating a large CSV file so that staff can perform downstream processes in Excel - e.g. aggregating 100k+ transactions into a smaller data set of 10k rows.
2. Cleaning and enriching data from SAP so that it can be used for reporting.
3. Calculating reinsurance premiums.
4. Analysing policy data to highlight exceptions and erroneous records.
5. Merging customer data sets from disparate legacy systems.

How to use Access databases in SolveXia

SolveXia has pre-configured instructions for Access databases. These include:

• Import from Excel to Access.
• Import from Text/CSV to Access.
• Export from Access to Excel.
• Export from Access to Text/CSV.
• Run a query (in the Access database).

For example, imagine a data set containing the countries of the world, ranked on their economic freedom:

This data is loaded into an Access database, cleansed and then aggregated to calculate the average economic freedom score for each region. This is then exported into an Excel report:

To automate this in SolveXia, you would start by using the Import from Excel function to copy the data into the Access database:

You can then add a step to export the data from a query in the Access database into an Excel file:

Once the process has run, the summarised data will be available in the Excel report (My Report.xlsx):

In addition to getting data into and out of the Access database, you can also write and run SQL queries. For example, to delete records that have a world economic rank of ’0’ (meaning that the country did not have any data):

Frequently asked questions

1. Do I need to change my Access databases to use them with SolveXia?

• Not at all. You can simply drop your existing Access file into your process in SolveXia.

2. What versions of Access does SolveXia support?

• We support any file that has been created in Access 2003 onward.

3. Is there a size limit for using Access with SolveXia?

• SolveXia processes your Access databases on our powerful, enterprise architecture. This means that you don’t need to worry about the size of your file - Important: Access has its own built-in size limit of 2GB however.

4. What if I want to replace my Access database - rather than simply automate it?

• We recommend migrating to SolveXia’s managed tables functionality. Managed tables look and feel like Access, but instead use SQL Server enterprise meaning no size limit and a significant increase in performance.

Do you have an Access database you want to automate or replace?

If so, we’d love to hear from you - please reach out via support@solvexia.com or call us on +61 2 9386 0202.