07-30-13, 15:03 #1Registered User
- Join Date
- Jul 2013
Unanswered: Is it possible to instantly update a mySQL database with data from msAccess?
Hi DBForums. My name is George, and this is my first time posting here. I hope you guys can give me some insight into my dillema. Please bare with me, despite mentioning mySQL, this is certainly a MS Access question. Without further ado, here is my question:
I want to create a table in mySQL that will be listening to the MS Access table, and upon seeing a new entry, it will immediately make an identical entry into its own table. In short, I want a clone database in mySQL that constantly updates itself with data from the msAccess database. Is this possible? If not, what is the closest way to approximate this function?
My employers use mySQL as their primary database. We use it for real-time monitoring on the PLCs running in our many facilities. The PLCs are connected kepware which monitors addresses in the PLC's memory, and logs them to the database instantly as they change. Recently, I've been tasked with finding a way to communicate a MS Access database connected to a PLC in a facility in another country with our mySQL database. The PLC in question communicates with MS Access running on a PC located beside its machine.
My goal is to have instant access to the data from the MS Access db without having to deal with ridiculously slow queries. Our main database has between several hundred million and several billion records in it, and we expect several hundred thousand from this machine alone. Thus, the data needs to be available in the mySQL database. A query into the MS Access database would not meet our speed requirements.
07-30-13, 16:11 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
Access uses SQL, MySQL uses SQL, you can have different backends in Acces (you can use the default db engine called JET, you can use any other SQL compliant engine such as MySQL. to get an Access front e end to talk to MySQL use an ODBC connector
however you will have to use a query (or your Access front end will have to use a query on your behalf). whether the query is ridiculously slow or not depends on the netwrok infrastructure to send the data from the remote site into the MySQL server
I suspect you would be better off sleaving the current machine alone, but modifying the current update process (the bit that writes the data to the local Accress db to also write that data to the server). how you do that depends on your local systems network. it does mean that plc (or the access d)b) will need a permanent open connection to the MySQL server.
depednign on the business requirement it may make more sens to send batched of results say once a minuteI'd rather be riding on the Tiger 800 or the Norton
07-30-13, 16:47 #3Registered User
- Join Date
- Jul 2013
I am not really interested in using Access as a front-end. The only purpose access serves in our system is that it communicates with that one machine. We do not use it to access the database beyond this, or to write queries, and all of our other machines write directly to mySQL so there is no point to use msAccess as a front end.
As I mentioned in my first post, I'm looking for a way to essentially clone the access database into my mySQL database. I want the tables in mySQL to update within milliseconds, or at most, seconds, of the access tables receiving information. As this is an international facility and each query often sorts through millions of logs, we want to keep the data local and simply cannot access it from another continent.
Finally, I like your last piece of advice quite a bit. I had the same idea but my boss holds that there must be a better solution. Chances are I'll end up using this solution if I can't find a better one, so thanks!