none
MS Access importing visual foxpro tables

    Question

  • Win XP Pro - Access 2003 - current ODBC driver for VFP

     

    Set up an ODBC connection via control panel/admin services using a free table directory

    - the target directory has many dbf files that are visible in the "browse" window -

    - I then try to use the Access link table "wizard" - choose ODBC connection -

    - select the ODBC link I created -

    - the "wizard" presents a list of dbf files to which I can create a link (or alternatively import)

    - Here is the problem - only some of the dbf files in the directory are visible in the "wizard"s list  - - why is this and what can I do about it?

    Thanks in advance for your help.

    Dave Mosby

     

    Thursday, June 28, 2007 4:16 AM

Answers

  • Naomi,

    I found the problem.

    My client gave me a cd with what he (and I) believed was all the database files that needed to be used.  The cd had only *.dbf files - certainly looked like all the database files.  However, some of the tables has associated *.fpt files (which hold associated "memo" content).  Apparently when any of the drivers are asked to present a list of database files to be connected to (for whatever purpose), if there ought to be an associated fpt file and one is not found (same base name and in the same folder) then even though a *.dbf file exists, it is not presented in the list.  Therefore, whatever method I used, I could not get to the data I needed.  Now that I tracked down the corresponding *.fpt files everything is working fine.

    Thanks for your help.

    Thursday, June 28, 2007 10:22 PM

All replies

  • Use VFPOLEDB driver instead of ODBC driver. ODBC driver is not aware of new VFP table formats (ie: it wouldn't recognize a free table created using VFP9 - even if it did there would be problems with datatypes that it doesn't know. ODBC driver is not updated after VFP6).

    Unfortunately I don't know how you could import using an OLEDB connection in Access via menus (though I remember I did it in the past programmatically. Since Access help has reference to ADO, Excel 2003 can do that using menus there should be a way).

     

    If I were you I wouldn't downsize from VFP to Access but maybe upsize to SQL server instead.

    Thursday, June 28, 2007 11:38 AM
  • Thank you.

    This is along the lines of my suspicions.

    I will try importing the data into SS2005 and report back for others to see as well.

    Thursday, June 28, 2007 5:14 PM
  • Using the OleDB provider of SS2005 left the same origin files not visible in the import list

     

    Any other ideas from any one?

    Thursday, June 28, 2007 6:47 PM
  • Dave,

    What do you mean by OLEDB provider of SS2005? You would use VFPOLEDB driver (OLEDB provider for VFP). If you don't have it, you may download from msdn.microsoft.com/vfoxpro site (check downloads).

    Here is a typical connection string:

     

    "Provider=VFPOLEDB,Data Source=c:\my freetables folder" 

    Thursday, June 28, 2007 7:59 PM
  • Just so others reading here will know, the original question was about linking VFP tables to MS Access via ODBC. Cetin's suggestion of using OLE DB is correct but you can't link tables to Access using OLE DB.
    Thursday, June 28, 2007 8:18 PM
  • I have given up on importing or connection vfp to access - and have moved on to importing the vfp tables into SS2005 - see that post - but I encountered the same problem.

    Thursday, June 28, 2007 8:49 PM
  • Naomi,

    Thanks for your help so far -

    As close as I can describe without screen shots can be seen at:

    MSDN Forums » SQL Server » SQL Server Data Access » importing Visual Foxpro tables into SS2005

    Please take a look at and comment on that posting.

    I am on a production conversion deadline and installing a beta at this point would be my last gasp.

     

    Thursday, June 28, 2007 9:10 PM
  • Naomi,

    I found the problem.

    My client gave me a cd with what he (and I) believed was all the database files that needed to be used.  The cd had only *.dbf files - certainly looked like all the database files.  However, some of the tables has associated *.fpt files (which hold associated "memo" content).  Apparently when any of the drivers are asked to present a list of database files to be connected to (for whatever purpose), if there ought to be an associated fpt file and one is not found (same base name and in the same folder) then even though a *.dbf file exists, it is not presented in the list.  Therefore, whatever method I used, I could not get to the data I needed.  Now that I tracked down the corresponding *.fpt files everything is working fine.

    Thanks for your help.

    Thursday, June 28, 2007 10:22 PM
  • Hi Dave,

     

    Just so you and your client will know, Fox tables come in 3 parts. The DBF is the table itself, the FPT, as you said, is the contents of any Memo fields, and the CDX file contains indexes. All three need to be included when copying files.

     

    Thursday, July 05, 2007 11:52 PM
  • Cindy,

    thank you for the note - I will pay special attention to the associated files in the future.

    Best regards,

    Dave Mosby

    CEO, InterWorks Software, Inc.

    Co-author, "The Paradox of Excellence"

    Friday, July 06, 2007 3:38 AM
  • Naomi,

    Thank you too!

    Dave Mosby

    Friday, July 06, 2007 3:40 AM
  • Hi All,

     

    I've been following this thread because I've run into the same problem.  At first I couldn't get 1/3 of the files to show up, after downloading and installing SP2 for SS2005, I was able to recognize a majority of the .DBF files in the folder.  However, there is one that I am still not able to find in the source list within the import wizard.  I don't see an associated FPT file with the table, and none of the fields are of a memo type.  Most of the others that weren't recognized by the wizard before the install of SP2 didn't have an associated FTP file either. 

     

    Any other thoughts on ways that I can import that one DBF file into a SQL table? 

     

    Thanks! 

    Wednesday, October 03, 2007 10:15 PM
  • try to look in here http://leafe.com/dls/fox

     

    hope will help!

    Friday, October 05, 2007 2:52 AM
  • Do you have to use the wizard? If not temporarily enable ad hoc queries with sp_configure and reconfigure, then do a simple select using VFPOLEDB with OpenRowSet().

    If you'd do that often than you may also choose to create a linked server.

     

     

    Friday, October 05, 2007 8:25 AM