Solved

Database Upscaling

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

Accepted Solution

by:
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.
0
 

Author Comment

by:wwit
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.
0
 
LVL 19

Expert Comment

by:strivoli
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).
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 41

Assisted Solution

by:pcelba
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.
0
 
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:

http://www.microsoft.com/en-us/download/details.aspx?id=28763

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:

https://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/index.html
http://www.jstreettech.com/downloads.aspx (The Best of Both Worlds)
0
 
LVL 34

Assisted Solution

by:PatHartman
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.
0
 
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.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now