Pulling Data from FoxPro files into Access

[virgo]

I 'inherited' a large Foxpro Win 2.6 database about a year ago. This runs peer to peer off NT 4 - SP 6.

Many of the datafiles are very large. One is about 3.5 Gig. Many of the variables in the original datafiles are not being used in the system. However, as all paths, locations, etc. are hard coded into the database, I am hesitant to work within the system.

(This may actually be sort of an access question.)

I would like to pull some of this data into Access files so that they would be in a more usable form. And, we do need to do reports, etc. off the data. When bringing in the desired variables, 2 tables will link and then there are problems with the third.

I need to link 4 tables to get the required information. I am using about 2-3 variables out of each table. EAch table contains from 100 to 150 pieces of information.

TIA.

 

[rgbean]

To get a better handle on your problem, could you perhaps supply the version of Access you are using, the version of the FoxPro (or VFP) ODBC drivers you are using, the workstation's OS (and version), the amount of memory you have, and more detail on the "... problems with the third" table.

Rick

 

[virgo]

A little more information. I have tried this on 2 workstations with the same results.

Workstation 1
Access 97, FP 2.6a Win.
ODBC drivers tried. General ODBC, FoxPro, VFP tables, and Dbase 4
98 SE,
196 Meg RAM

Workstation 2 - differences from 1
Access 2000, 95 B, 64 Meg RAM

I have 4 tables that need to be linked. I need very little information from each of the tables.

The tables are as follows:
MBFIL1 - contains ID - about 2 gig
MBFIL5 - contains ID - about 7 gig. Also only place where there are 2 variables, cat, and item that are needed.
CBHH - contains ID and batchno - about a gig
CBBATCH - contains batchno - about a half gig

There is a 1 to many relationship between mb1 and mb5. There is a 1 to 1 between mb5 and cbhh. There is also a 1 to 1 between cbhh and cbbatch.

I have tried it 2 directions. If I link mb1 to mb5 and then do that query, no problem. If I try to add one of the CB files, I get the 'illegal operation bit'.

If I start with the cb files, I can link and then query them. However, if I try to add one of the mbfiles, then back to 'illegal operation'

 

[dgrewe]

Sounds like you should start with the MB5 file , relate it to the other 3 and go from there. Actually a Query here would be a waist of time and memory

look this over
use MBFIL5
use MBFIL1 order ID in 0
use CBHH alias CBHID order ID in 0
use CBHH alias CBHBN order BATCHNO in 0
use CBBATCH order BATCHNO in 0
set relation to ID into MBFIL1 , ID into CBHID , BATCHNO into CBHBN , BATCHNO into CBBATCH
copy fields MBFIL5.xxx , MBFIL1.xxx , CBHID.xxxx, CBHBN.xxxx, CBBATCH.xxx for MBFIL5.xxx = yyyy to file c:\accesstable
then
let access play with c:\accesstable
David W. Grewe
Dave@internationalbid.com
ICQ VFP ActiveList #46145644

 

[rgbean]

Well the systems seem adequate, but are you sure about the MBFIL* table sizes? FoxPro (and even VFP) tables are limited to 2 gigabytes - it's a DOS legacy thing. If they have gotten bigger than this limit, it's possible that the ODBC drivers simply can't handle them.

Rick

 

[virgo]

You are so right. And, I know that and just didn't think about it.

Is there any possibility of cutting these monsters, perhaps with Access queries into more managable size?