Importing Foxpro data and appending

[GAVRACK]

Hi chaps, could anyone help please?

I want to create a database where I get the data from a Microsoft Foxpro app, version 2.6A. i'm using Access 2000

I know the filename and all field names that I want but I need to set Access up so that every time I refresh it grabs the fields I want from the location previously specified but appends to what was there before, not overwrite.

This is because i wish to enter pieces of data manually alongside existing data in the first available blank column alongside the imported columns and dont want the rows of imported data to get out of sync with the manually entered data.

I want to tell it to go get new data where a field value is greater than the largest value already present for that column. As an example, grab all external data where date is greater than one week ago and place it on the end of what was already there.

If its not possible in Access then I'd be happy to use Excel if anyone knows how to do it using that.

Any help would be appreciated, thanks

 

[Jon K]

In Access 97 I import data from Visual FoxPro 3.0 using an IN CLAUSE in an append query. The query goes like this:

INSERT INTO AccessTable
SELECT Name as Customer, InvoiceDate
FROM FoxTable IN "C:\Directory" "DBASE IV;"
WHERE InvoiceDate = Date()

I arrived at this query by trial and error. (I still cannot find the correct word to use for Visual FoxPro.)

These are the limitations I found in using the words DBASE III or DBASE IV:
The FoxPro dbf must be in fox2 format, i.e. in dBase III+ format, not Visual FoxPro format.
The name of the FoxPro dbf cannot exceed 8 characters.


These were the commands I used for the conversion in Visual FoxPro:
use dbfName
copy to FoxTable fox2

Luckily Visual FoxPro can continue to use this new FoxTable.dbf in fox2 format. So the conversion was needed only once.

Since you use FoxPro 2.6a, perhaps you can use the dbf without having to covert it to fox2 format first.

Hope this helps.

 

[AccessKid]

why not link the foxpro table to your access database and then create a query that appends new/selected records as per criteria you can specify either hard coded or parametrized. this way, you don't have to worry about the foxpro table version.