We are a growing company and feel that we have outgrown our current database structure. We have dug ourselves into a bit of a hole with our current setup and we have run out of ideas of how to move forward. Any suggestions would be greatly appreciated.
Here is a brief outline of what we currently have:
Our system is based around an externally provided system with data stored in a FoxPro database.
To expand on the available functionality we currently use a number of MS Access programs, databases and reports linked to the FoxPro data to give what our users need.
A number of queries are run on both the FoxPro and Access databases from a separate program in Java.
As the system has grown we have become aware of the many issues surrounding Access Databases. We continually have issues with corruption and the system is slowing down significantly.
We are now looking for a way to eventually move both the data and the user interfaces away from Access. The main problem we have had so far is that, by moving away from Access, we seem to be losing the ability to run queries combining the data from both the FoxPro database and whatever medium we moved the Access data to. The only way to resolve this was to use an Access program with links to all our required data sources and use an ODBC link to that for all our queries etc.
Hopefully that makes some sense. Any help at all would be greatly appreciated.