I am working on an application where all of my machine data is in MSSQL and the customer and order data is in access. I can read each individual value OK but running multiple queries to Access is slow and Access is not very friendly when it comes to multi-user access.
What I would like to do is run one query against Access using:ExecuteQuery() and then store the resulting dataset to SQL where I can do what I want with it. The above function will return between 1 and 8 rows by 8 columns of data
Can this be done now or would this be a feature request?
Might be a good use for the “StoreVariable” / “GetVariable” expression functions in FactorySQL, but I don’t think you can store a dataset with those - you’d have to break them up into individial values I think.
You know, MySQL ships with a very nice Access-to-MySQL conversion tool…
Well, you can currently get kind of close, but probably not all the way, or at least not very easily. That ExecuteQuery can be run with a secondary connection name, and will load the data into an in-memory dataset. Then you can create a secondary action item (or multiple) which uses the GetValue… functions to do something with the values.
The getvalue functions DO have a default value that gets returned if the row doesn’t exist, so if you know you’ll never have more than 8 rows you could have an action item that executes the query, and then 8 more that will be fairly big expressions checking that the row exists (by getting a value and checking that the default value isn’t returned), and then executing the secondary query with all the values.
For more info on the functions, check in the manual under Technical Reference-> Base Scripting language-> Database functions (though I imagine you’ve already found that).
We could also look into throwing together a quick plugin that could make it a tad easier, but see if this gets the job done first.
Your suggestion works, however, it does take some time to put all those items in groups in that manner.
I discussed converting Access with IT again this morning. The problem with converting to anything is the time involved. Converting the data is easy. It is the Access front end and all of the macros, vba scripts, etc. that make it messy. I have never seen an Access implementation this intense at any other company I have worked at. They have been working toward moving the data but it is a slow process.
In the mean time I still have to deal with Access locking records that shouldn’t be locked and causing errors in my groups.
Speaking from experience, it is well worth the investment of time to switch from Access to MySQL. The data conversion tool works really well, and the stability gains are like night and day. I would push your IT department into switching. Access should be avoided like the plague