Foxpro Database to Foxpro Database - Data Exchange

Posted on 2011-04-21
Last Modified: 2012-05-11
I have several application with Foxpro database backends that need to exchange data between one another when they're open.  Currently I have program that copies needed data from a database to temporary tables on a thumb drive.  I have a program associated with the other database that imports data from the thumb drive.  Kind of a pain.  Do I just creat a connnection?  If yes, how do you work with the connection to copy data from one database to the other?  Thanks in advance for your help/suggestions.
Question by:NIDDK1550
    LVL 41

    Expert Comment

    FoxPro isn't a pure backend. FoxPro is a file oriented database and it can handle both data and user interface. Thus if you can see DBF file from any location you may open it, read it, or even update it from elsewhere. All the work is done "locally" not on the machine where the file is located. It means all data must be copied over the network when you want to query or update them. But you probably know these details.

    You can make a connection via ODBC or OLE DB driver to FoxPro tables but again, this driver does not work on the backend but it reads all DBF data over the network.

    If you can map disk drives from one server on the other one then you may read data directly from FoxPro application and you don't need any ODBC connection or the thumb drive.

    OTOH, the FoxPro application is sensitive to a network stability and reliability. Recovery from an error is almost impossible if the connection is broken during the read operation. And how would you like to ensure copied data integrity? The thimb drive can represent a certain kind of a safety element.
    LVL 1

    Author Comment

    I'm just trying append a record(s) from a table in one database to a table in another database while both databases are open.  For example, I want to take the lab results for a patient from the labs database and copy those results to the patient letters database.  Yes, I'm aware that I'm duplicating data and no I wouldn't do it this way if I had the time to rewrite the applications involved.  At some point I'll rewrite the front end with .NET and the backend would be SQL.  Right now I just have to work the best I can with these legacy applications.  Again, in a nutshell I just need to copy data from one database to another database (both foxpro) while both databases are open.  What's the best way to do that?  
    LVL 1

    Author Comment

    Also should have mentioned that these databases exist on the network and therefore they should be able to access one another.
    LVL 41

    Accepted Solution

    To append records from one table into another one simply issue following INSERT command (these two tables can be in different databases):

    SELECT 0
    USE d:\path\OneTable SHARED
    SELECT 0
    USE x:\path\AnotherTable SHARED

    INSERT INTO OneTable (ColumnA1, ColumnA2, ColumnA3, ...) ;
       SELECT ColumnA1, ColumnA2, ColumnA3, ... FROM AnotherTable WHERE SomeCondition

    If you need to be sure nobody will change records in the "AnotherTable" during the query then you have to lock it. The table "OneTable" will be locked automatically (or an eror appears when the lock will be unsuccessful).

    Several other commands exist which could do this work. The "best way" depends on your data and also on indexes created on these data.

    You should also properly handle primary keys generation or autoincremental columns if any. Difference is also in processing record updates vs. new data insertion. Databases could contain additional validations which will avoid updates made by 3rd party programs etc.

    If you are not sure how exactly are data handled by your legacy applications then you have to analyse it carefully or ask somebody who is able to do this work for you.
    LVL 12

    Expert Comment

    The advice you have been given above is very good.

    If you already know this stuff, please don't take offense, but, based on the nature of your question, you also might want to look over the various VFP tutorial videos regarding the use of data tables, etc. at:      

    Good Luck
    LVL 29

    Expert Comment

    by:Olaf Doschke
    Based on your current workaround and the recommendations you got, why don't you do the same stuff like now, but with a folder in the LAN instead of a thumb drive?

    There is no really different and better way, as pcelba initially said, foxpro is not a database server, there is no replication or connections between databases, you always go through some vfp process querying data into cursors, then appending them to other data or whatever.

    If you have data used by both applications, why not simply use the same dbf in both apps instead of synching?

    You could also think of creating a data access layer usable by all applications. In the simples case a vcx with database queries to common data, compiled into each application needing that data.

    Remember: Redundancy is the worst in databases, it's destined to get out of synch.

    Bye, Olaf.
    LVL 1

    Author Closing Comment

    Thanks to everyone else for their comments.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: (http://msd…
    Read about the 3 stages of the buyer's journey: awareness, consideration, and decision.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    746 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