Migrating/duplicating 30+ Access tables to SQL

General Discussion
1

Hello.

Currently, we have approximately 30 automated testing stations, which ensure product quality. These all save to a number of different MS Access databases. We're looking to centralize our data, and integrate it into Ignition.

Assuming we can get the data from MS Access, is there a best way to structure all of these different tables? One thing I've noticed while using SQL (although I'm not a expert) is there isn't a way to put tables into folders in the designer. Eg, if I am looking at my default (and only) database, there are 40 tables, all in a bit of a mess.

An obvious solution to this problem would be to condense the testing data down into one table, but the data types are all different. Eg, some access tables have a "Test Start Time", some have a "Test Start Time" and "Test Length", and some have a "Test Start Time" and "Test End Time". Same issue with the types of test data. Some might record five ints, some three floats.

A co-worker suggested a table storing one column with the station name, and then all the other columns as varchars, and then casting them to appropriate types as needed. This feels like a poor choice, as it requires a lot of casting operations, and stores what are mostly dates/ints/floats as strings.

Another option would be to have one table, storing information about each inspection event: station name, start and end time (the latter possibly null if not recorded in Access), and then an event_id. Then there would be a separate table with event_id, specification name, and value.

This still doesn't completely solve the issue of needing to cast the value to various types though. Ignition's historian seems to store three values, int float and string, using only the one that matches the tag type. Is this a good solution to the problem?

Are there any other options to allow us to more easily manage the various types of data to be connected?

2

My naive approach would be pushing these tables to a postgres server or similar, and just connecting ignition to that postgres server?
See e.g. SQL Bridge (Transaction Groups) | Ignition User Manual

3

You generally would not use the Ignition historian as a replacement for custom production information tables.

The simplest approach is to convert your Access application to use linked tables pointing at a SQL Server. If you do this first, for all Access files, you can migrate the user interfaces in a leisurely fashion while leveraging proper storage for external analysis/reporting/monitoring. Ignition would simply connect to that SQL Server instance.

Yes, you should clean up the table structures, but that can be an optimization later. I'd take the shortest path to not have your data held hostage in Access.

4 Likes
4

Sorry, I was a little unclear. I more meant "The ignition historian handles the fact that you want to store different data types by using different columns for each. In my own, separate tables, is this also a good idea"?

5

IMO, almost certainly yes. There are normalization optimizations you can do after, but as a first pass, just have your tables mirror your incoming data structures.

1 Like