Go Premium for a chance to win a PS4. Enter to Win


Database Upscaling

Posted on 2014-01-14
Medium Priority
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 20

Accepted Solution

strivoli earned 400 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 20

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).
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

LVL 43

Assisted Solution

pcelba earned 400 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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 400 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:

http://www.jstreettech.com/downloads.aspx (The Best of Both Worlds)
LVL 40

Assisted Solution

PatHartman earned 400 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 30

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 400 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

926 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