Robert Weißbach
Technical Editor
Last updated on
14.8.24
News
Migration from Microsoft Access to Ninox
6
min
Table of contents
An industrial agency goes digital!
In 2009, we created a customized CRM solution based on Microsoft Access for an engineering office in Essen. A comprehensive system developed which, in addition to convenient customer and appointment management, also had a largely automated document management system.
The customer was very satisfied. Over the years, the data flowing into this system increased more and more. We automated the data to be imported via Excel macros, so the effort was limited.
But then in 2017, when the desire arose to provide a home office for the field staff, a new system was needed. Migrating to a server-based system was out of the question because of the high administrative overhead. We needed a cloud solution. That's how we came across Ninox. From the first moment we had a good feeling, which has not left us until today.
Within 6 weeks we migrated the complete Access database with 56 tables, 143 queries, 64 forms, 23 reports, 57 class objects and 10 modules to Ninox. It's hard to believe, but in Ninox we now have only 19 tables, 53 views and about half a dozen reports. We really wondered if we had forgotten something essential. No, we hadn't. All the information we need is available in the new database and can be accessed clearly and quickly by all employees.
Ninox has succeeded in making the operation of the views and queries so simple that each employee can compile his or her own information from the database. This eliminates the need to program complex queries, lists and reports, and the database automatically adapts to the workflow of the employees. In the course of the migration to Ninox, a completely new workflow was created, which used synergies in many areas of the operation and thus considerably reduced the maintenance effort of the system.
Migration - How we proceeded:
Step 1: Describe data model
There is a serious difference between the database model of an Access database and Ninox:
In Access, the entire tables are linked relationally via self-defined key fields. The linking in Ninox is done via the individual records or rows of a table. This means that in Ninox the linking of one record to another always requires an action. So it is not enough to switch on the linking of two tables in Ninox. Rather, each record must now be physically associated with another. This is usually done when a new record is created, but can also be defined when importing a table.
So we first described all tables from Access in Ninox, in order to then have the possibility to link the data sets during the import. The import worked after small initial difficulties with the browser without problems. However, we noticed that more links also required more performance. Thus we needed 2 weekends for the import of the data. We imported about 15,000 customer masters and several hundred thousand contacts, call partners, sales, etc.
Step 2: Customize forms and views
In Ninox, a data entry form is automatically created for each table. To make it easy for us, we used these forms and only moved the fields to specific positions for some complex forms.
To customize the design of a Ninox form, the iPad app is suitable. Here it is particularly easy and clear to position the fields and insert design elements. An adaptation of the forms to the respective output medium (screen, iPad, iPhone) makes Ninox independently. A huge advantage over Access.
Also the views are already provided by Ninox in an "all" version. Basically, after a little instruction, every employee can quickly and easily click together their views.
However, we provided the customer with more complex views over several table levels. Here, the architecture of Ninox was particularly helpful. Due to the fixed bindings in Ninox it is possible to get really all linked information in one single view. In Access, we sometimes had to create 2 or more queries to do this.
Step 3: Create reports
Again, Ninox provides a standard report for each view, which we modified. Together with the customer, we replaced a large part of the reports with exported Excel tables. Admittedly, this would have been possible in Access as well, of course. The introduction of the new system was taken as an opportunity to optimize the workflow in the company.
Step 4: Data transfer
As already described in step 1, we transferred the data from Access after we had described the data model.
Currently, only CSV files can be imported into Ninox. We exported the individual tables from Access to Excel spreadsheets, then saved them in CSV files and read them into Ninox.
In order to import the data with correct character sets (umlauts, special characters, etc.), we used the following formats:
1. Export from Access to Excel: Data should be exported without formatting and layout.
2. Export from Excel to CSV: File type: CSV (separated characters) (*.csv)
3. Import to Ninox: Character set: ASCII
Key: The key fields from Access should be transferred as well. This way fields can still be updated from Access after the data transfer.
Relationships: As already described in step 1, the relationships must be defined during the import.
The import in Ninox worked without problems. We had no problems with importing any table. On the contrary, before finally importing the data, a preview of the data to be imported is created. Here the data can be checked in peace and any errors can be corrected before the import.
Step 5: Conclusion and outlook
Admittedly, the conversion to Ninox cost us quite a bit of energy and especially a large portion of courage, but the result was definitely worth it.
The customer is more than satisfied with his Ninox database and still has a lot of ideas for optimizing his workflows. The acceptance of the new system is also very high among the employees. They enjoy getting more and more new information from the database and thus making their work even more efficient.
Finally, a word should be said about Ninox's interfaces. At first glance, import and export look somewhat limited. However, one should keep in mind that Ninox Cloud is a full-fledged web application. Thus, interfaces to and from Ninox can be described with ReST API's. This enables the automatic exchange of information between Ninox and other systems and eliminates the need for manual import and export.
We are glad to have discovered Ninox and look forward to many exciting projects with this database.
Susanne Wittpoth
