Is this approach folly?

[Noel]

Is this approach folly? FoxPro, Access intermingling

In my application, I will have tables originating from and periodically updated (serveral times daily) using existing FoxPro application data, alongside tables created and managed within Access. I.e. through a series of ODBC links and queries, I'm bringing the FoxPro data into Access as resident data. There are other related data in the application, but the primary data of interest in the application are the data that come from the FoxPro app.

The data originating from Access is supplemental data, entered via forms in Access and stored in tables that have relationships to the tables that originally came from FoxPro.

I'm wondering how best to handle the updates or the "sync" process?

On the one hand, I can setup the relationships between the tables that originated from FoxPro and the tables that originate from Access (thereby using the relational db as it should be used), HOWEVER, to me that means that I would have to carefully 'sync' the resident tables that store the FoxPro app data; such as roughly, step 1: run an append query to add the new records, step 2: run a delete query to remove the deleted records, and then step 3: run an update query for each of the records in the FoxPro related tables. This amounts to lots of slow running queries.

On the other hand, I can essentially delete the FoxPro data that resides in Access and then append the new FoxPro data. To me, this would minimize the time it takes to "sychronize" the access tables with the FoxPro tables, HOWEVER, that means I can't set up relationships with the other tables (b/c of key violation errors etc).

I'm thinking the second approach is folly and is asking for orphan records etc...trouble. Thoughts? But the first approach is almost unacceptably slow (up to 20 mins)

Can someone suggest a way to setup a "sync" procedure that also maintains referential integrity.

Thanks - Noel

 

[pbaldy]

Is there a reason you don't simply link to the original Foxpro data, rather than try to keep it in an Access table and constantly synced?

 

[Noel]

The foxpro app continues to be used; however, I'm told that it's not very stable.

The data in the foxpro free tables come with trailing spaces etc...

For some reason, I don't trust the FoxPro ODBC driver link enough to build an entire application around them.

I take it your experience has been fine? No driver failures or errors? I'm very curious.

Anyway, I'm copying the desired foxpro free tables (using fso copy) to a different directory and then cleaning (trimming the data) as I bring them in to access.

 

[Noel]

To be more clear, I am linked to the foxpro dbfs (i.e the dbfs that i copy over from the original location). But, from there, I'm bringing the data into access as resident data.

During testing, I've encountered some odbc errors/failures, which made me uncomfortable with the concept of running the application around the linked dbfs directly.

I'd love to hear other folks experience with running apps around linked foxpro dbfs.

Thanks - Noel

 

[pbaldy]

My experience has been fine. Our canned payroll/HR program uses Foxpro as a back end. Most of my apps link to the main table so that when a user enters an employee number for example, I can get the other info associated with that number. Plus, some of my apps calculate amounts due to employees, and I push data up into the appropriate Foxpro tables so they don't have to manually enter anything into the payroll system.

I did find that some queries that included the Foxpro tables ran slowly. A couple were slow enough that I did something similar to what you're talking about. I created a process that basically copies the data over to a SQL Server table, and the queries link to that.

I guess I'm arguing both sides of the point, but I did it for speed, not data integrity problems. Those queries that frustrated users and took minutes to run now take a very few seconds. To directly answer your original question then, in that process I empty the SQL Server table and append all records from the Foxpro table. It's done automatically every night plus they can start it manually if they've added people to the payroll program that they need to see right away. That process takes maybe 10-20 seconds, copying something like 5,000 employees.

 

[Noel]

writing back into the foxpro dbf via access? Now that's where I get real squeamish.

I'd love to be able to do that...I'm just too paranoid about the foxpro app (it syncs data from the local (CA) office with the corporate office (MN)) and is supposedly unstable.

Are there specific scenarios you've experienced where writing from access to the foxpro app table has resulted in or at least has a high probability of corrupting the foxpro app table?

Thanks - Noel

 

[pbaldy]

If yours is unstable, then I guess you're right to be squeamish. I haven't experienced any corruption of the Foxpro data due to writing to it. We have 8 operating companies in 2 cities, and each does payroll bi-weekly, so we're writing to Foxpro regularly (FWIW, each city has it's own copy of the payroll program).

 

[Noel]

any other approaches out there?

Curious if anyone else works with access/foxpro and has a way to sync the resident access tables with data coming from the foxpro application whereby 1) the processing time is kept to a minimum and 2) you can maintain your relationships between the tables.

Thanks - Noel

 

[Noel]

Hey pbaldy, anyone? - I hope you don't mind another question?

In access you can go to design view to see where the controls are mapped to ...specifically you can easily find the recordsource/controlsource table and fieldname etc

Is there a way to do that in the foxpro application?

Obviously, I'm only slightly familiar with the foxpro program/application components. I know that the dbfs are where to go to access the table data. I know there are fpt files that sometimes impact whether I can see the dbf as an option for ODBC linking ...

The foxpro app I'm working with resides in a network directory. I'd love to be able to understand how to identify recordsources and controlsources of the foxpro app, and I suspect the info is available in the directory, but I'm not sure how to find it/access it/read it.

Thanks in advance for any insight.

Noel