Database Upscaling

Posted on 2014-01-14
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.
Question by:wwit
LVL 19

Accepted Solution

strivoli earned 100 total points
ID: 39779368
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.

Author Comment

ID: 39779438
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.
LVL 19

Expert Comment

ID: 39779479
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).
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

LVL 42

Assisted Solution

pcelba earned 100 total points
ID: 39779544
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.
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 100 total points
ID: 39779564
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)
LVL 36

Assisted Solution

PatHartman earned 100 total points
ID: 39780040
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.
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 100 total points
ID: 39783113
>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.

Featured Post

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question