We help IT Professionals succeed at work.

Database Upscaling

wwit
wwit asked
on
278 Views
Last Modified: 2016-05-17
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.
Comment
Watch Question

IT Manager
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
We have had a go at the Access Upsizing wizard to SQL Server and have had quite a few problems with it. I'm sure if we stuck at it we could get that working. My main issue with that is that we would no longer be able to use our Java program to query and update both sets of data at once. One of the few advantages of Access is that it is simple to run a query based on 2 tables on 2 different ODBC connections. Java is unable to do this as it is restricted to one connection per query. Is there an alternative to using Java for this? We could replace this program with an Access one but I feel that would be taking 2 steps forward and one step back.
strivoliIT Manager
CERTIFIED EXPERT

Commented:
I would leave Access because you reached a dimension that is not suitable for Access. I can't suggest any front-end technology. When trying the wizard that imports Access into SQL consider both versions (they might not be fully compatible).
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Olaf DoschkeSoftware Developer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.