Data Optimization SQL / MySQL
I wanted to bring an interesting situation to your attention and see what you think. It goes a bit more outside Access than originally planned, but it may work nonetheless.
So I have a database with hundreds of millions of records that are going to be stored. The data is coming from an SQL Server. There is one database that contains a table that contains a specific ID (personalID) and a Name, and on a different database there are many records of phonecalls and each phonecall has a personalID attached to link it to that person.
The goal is to have an Access database that could in theory house the data for a specific person, query down to only certain required fields, and then base reports from that query. The issue is, of course, is Access has a 2 gigabyte limit.
So, the solution to this was using MySQL to house the data for me and use Access as a front-end. Which, in theory once more, has it's benefits. I'm using a tool called 'Navicat' to act as a middleware between the SQL and MySQL server. Now comes the big issue:
I want to have a button cmd that, when clicked on a form, it will grab the previous day's data from the SQL Database and append it to a table in the MySQL database.
The only way I've seen a viable way to do this is link to the tables in Access, do a union query that combines the two SQL Database tables together, and (using ODBC connection), append it to the table in the MySQL database. The problem with this is, to have Access as a 'front-end' all tables AND queries are housed on the back-end side, which would be the MySQL database. But I'm baffled as to a more optimal way to link the two servers together.
What do you guys think? Any and all opinions are appreciated. Thanks in advance!
Also, to note, the reason this is being done is because the SQL Server dumps the data after storing it for so long, so it will not be available for reporting / queries after a set amount of time.
Angelo