Database Upscaling

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.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

strivoliConnect With a Mentor Commented:
It's time to consider MS SQL Server. You might consider also other alternatives but since you must import data from Access and FoxPro (both from MS) you'd better chances to do it successfully with MS SQL Server.
wwitAuthor 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.
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).
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

pcelbaConnect With a Mentor Commented:
Well this depends on your budget... and on your basic requirements.

1) Do you want to preserve the externally provided system? If not, what is the possible replacement?
2) Do you want to stay with heterogeneous databases (FoxPro + Access + anything new) ?
3) Do you plan to build the new system from a scratch?
4) Do you have any estimations how long (mandays) and how much it could take?

After you'll answer these basic questions then you may start thinking about possible implementations.

Almost any product allows to access data from various sources via ODBC drivers. This is slower than native data access but it works. So independently on your new platform you have to check this possibility. Remember the fact Microsoft ceases the DBF support and FoxPro (obsolete) ODBC driver may stop working soon.

BTW, we are using Visual FoxPro for everything and we don't have possibility to switch to something else namely from cost point of the view... Data size is about 50 GB (saved documents represent the most of it). The system works in client-server mode, the server is served by Visual FoxPro COM+ application and 150 concurrent clients are working over various connections from cell modems to local network connections. The system still grows - development folder has over 1 GB (repository is about 400 MB) and it will stay this way for several more years... To switch this system to e.g. MS SQL Server would need several manyears of work, to switch to SAP is impossible, also other not so expensive products (Navision, ...) do not have features implemented in our today's system.

The COM+ application has one big advantage: All data are accessed locally on the server so all queries are fast and we did not observe any data corruption over years. Known DBF files remote access problems like corruption due to bugs in SMB2 or oplocks do not apply in COM+ but we are aware of any OS upgrade. Fortunately the next one will come in about 4 years and I believe the server can stay with W 2008 R2 in the worst case.
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
You can keep your front-end in Access. It's a very common setup - the Forms, Reports, etc are maintained in Access, and your data resides on the SQL Server.

I've had better results using the SQL Server Migration Assistant here:

This will move your tables to SQL Server, but you'd still work in Access via linked tables.

Your FoxPro tables will also be linked (presumably), and you should be able to continue working with them as you are now. I would assume your Java program connects to the Access database, and then runs stored queries? If so, this should continue to work as before.

There are some "gotchas" when moving data to SQL Server. See these articles for more information: (The Best of Both Worlds)
PatHartmanConnect With a Mentor Commented:
If you are looking for a non-Access solution, I'm not sure why you would post the request in an Access forum.  We like Access here.

For starters, is your Access application split?  Does each user have his own personal copy of the FE installed on his C: drive?

If you are having trouble with the upsizing wizard, it is likely that the problem stems from the original schema definition in Access.  I would clean that up first.  
1. Every table should have a primary key.  If you don't have a candidate key, use an autonumber.
2. Most PKs should be autonumbers anyway but when you have candidate keys, create unique indexes to enforce business rules.
3. Create referential integrity.
4. Adding RI to an existing schema will cause errors if you have invalid data.  You MUST clean up the data before Access will allow you to enforce RI.
5. Change any column names that SQL Server objects to.
6. Change any table names that SQL Server objects to.
7.  Access automatically creates hidden indexes for fields involved in relationships.  You may need to add other indexes to help speed up searches.

Once the original schema is cleaned up so that it doesn't have any errors, the upsizing will be smooth and fast.

Access is called "Access" for a reason and you are learning that now.

When people complain about Access being not scalable and frail, they are actually talking about Jet and ACE the database engines that are closely associated with Access but actually ship with Windows and so are installed on most desktops.  They are not talking about Access the rapid application development environment that you used to build your forms and reports.  You don't even need MS Access to be installed to use Jet and ACE.  They are stand alone products.  Access does rely on Jet or ACE to hold its own objects but by using ODBC drivers, your data can be anywhere.
Olaf DoschkeConnect With a Mentor Software DeveloperCommented:
>Our system is based around an externally provided system with data stored in a FoxPro database

Which means you don't get away from the DBC/ DBFs involved here.

There are ways to add DBFs to SQL Server as Linked Server. But that doesn't mean queries joining data you migrate to SQL Server and data from DBFs in linked server tables are easy to join. Access to linked servers differ from the usual SQL Select.

Data synching may be an option, VFP itself surely is one, BI or Reporting tools pulling data from different ODBC sources are a third option, but then I know nothing joining data live, you query one ODBC source, then another, then combine that data, you can't optimize data retrieval of an inner join, for example. That's a problem of data distributed in several databases anyway.

If you don't need to combine live data, but do analytical processing and statistics, a datawarehouse solution might be worth thinking about, then you aggregate data to a warehouse anyway and work on that data instead of the single transactional databases.

Is your Foxpro system custom made for you? You could think about migrating that data to SQL Server, then the rest would follow.

Bye, Olaf.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.