I just need to find out the right database architecture before developing an application either in VBA in Ms Access or VB6.
This is for a data cleaning software where the user needs a copy of the database from the live server and the application runs the queries of a local copy of the database.
This is how the application works now.
• We are using a local MS Access 2007 database to import selective tables from the database servers. There is a data sync feature on the application that does this importing
• There is a java applications that runs its queries from the local Access database
• All the tables in the local database is a copy of the required tables from the live database except for two which are linked tables. The linked tables are in place so that the users don't work of the same set of online data.
The issue that we are having now are:
• java application doesn't come with inbuilt classes like VB or other Microsoft products for filtering, tables, etc. Although some of these have been achieved in the existing application, the performance is unsatisfactory.
• The local access database gets corrupted often as we sometimes deal with 30k+ lines of data which queries the local database around 10 times per line
We use Mysql as our RDBMS. The solution we possibly are looking for:
• Is it possible to create a local copy of the live MySql database (only selected tables) in mySql or any other RDBMS similar to MySql
• The key element of having a local copy is to sync the database both ways. any changes made to the local DB should be reflected in the live DB as well. It doesn't have to be at run time. A trigger from the main application is good enough to do the job
• Please bear in mind that we are planning to develop the main application using VB oriented language
• and the most important thing is do we really need to have a local copy of the database? the only reason we were doing it was the query that were running on the old application were taking forever to complete. Perhaps, master- slave architecture is the way to go ( don't know much about it)
Looking forward to hearing from you experts