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.

5 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
6

I think my post was muddied a little bit, because there were actually two questions being asked:

1: Right now I have test benches. Some of them run code which is not editable by my team, and some have code which is editable, but I'd rather avoid the headache if possible. These benches write data over ODBC to Access databases. How do I most effectively move those Access databases over to SQL Server?

One solution could be having Access act like some piece of middleware, where it effectively receives an ODBC request from the testing station program, does some translation to the request, and then forwards it over to the SQL server.

That's what linked tables seem to do. However, linked tables seem to be designed for the case where the data is being written to the table by something within Access (eg, a form). If the data is being written by something external, are linked tables a solution at all?

It could also be possible to have Ignition read directly from those Access databases, although given that large queries against these databases (500k+ lines) have caused production slowdowns, this is likely not a good idea.

2: Assuming I can get the data available to MS SQL Server, how do I structure it in such a manner that it's easy to use and understand. This, as you said, is less of a concern. Likely the simplest solution would either be to create 30 different databases (one for each access database), or one database, with table names concatenated with station names.

7

Huh. No idea. But an ODBC or ADODB (or whatever) connection to MSAccess can be replaced by a connection to SQL Server instead, perhaps seamlessly with just a DSN change.

Really bad idea.

That's a really open-ended question. Multiple classes in database technology at the college level are needed to really cover it. (Or many years of experience.) Most people start by normalizing their data.

Don't do any restructuring for this purpose until you have it all excised from Access. IMNSHO. (FWIW, my love/hate relationship with MS Access goes back to when I got my own copy of MS Access 97 Developers' Edition. Many of y'all weren't even born yet.)

8

I'm hoping this option will be possible. The tables are all quite simple, just rows of date/part_number/value1/value2/etc, and so even if I do need to update the SQL a little, there shouldn't be any complicated JOIN operations which burn me.

My main worry on this part is simply that some of the software was developed by outside people, who may have used the equivalent of "Know How Protection" on the code. In such a case, it would be impossible to update the bench in this manner.

Another possible annoyance here is the incompatibility between 32 and 64 bit programs

Yeah, I'm less worried about the data in the tables. It's a mess, but it's a mess that's baked into the code to a level I'm not touching. I've looked into normalization somewhat, and have been using it as much as possible when developing new tables (as well as enforcing constraints).

It's mostly just that even copying 30 databases * 5 tables each means 150 tables that need to go somewhere in SQL server. I'm not sure if there is a nice way to organize them (it doesn't seem like tables can be put into folders), so that it's possible to nicely navigate to them in Ignition.

I haven't worked with it before, but it seems very strange that there is no way to stop users of an access program from simply opening up the tables and messing around with them. Add onto that the SQL editor not having any common features, and I'm confused as to Access's target market

9

Suuuuper naive answer: just set up a service polling the access DBs, watching for changes, then replicating them over to sql server?

This is compute inefficient but it has the upside of not requiring whiteboxing the code