Dave Mosby


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




Re: MS Access importing visual foxpro tables

CetinBasoz


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.






Re: MS Access importing visual foxpro tables

Dave Mosby

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.






Re: MS Access importing visual foxpro tables

Dave Mosby

Using the OleDB provider of SS2005 left the same origin files not visible in the import list

Any other ideas from any one





Re: MS Access importing visual foxpro tables

CetinBasoz

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"





Re: MS Access importing visual foxpro tables

Cindy Winegarden

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.




Re: MS Access importing visual foxpro tables


Re: MS Access importing visual foxpro tables

Dave Mosby

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.





Re: MS Access importing visual foxpro tables

Naomi Nosonovsky

How exactly you're importing your tables to SQL Server 2005

You may want to try to get SP2 CP (beta version), because the Upsizing Wizard was greatly improved there, as I heard.





Re: MS Access importing visual foxpro tables

Dave Mosby

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.





Re: MS Access importing visual foxpro tables

Dave Mosby

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.





Re: MS Access importing visual foxpro tables

Naomi Nosonovsky

It's great you found a problem. We would not be able to figure that Smile

Best of luck!





Re: MS Access importing visual foxpro tables

Cindy Winegarden

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.






Re: MS Access importing visual foxpro tables

Dave Mosby

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"





Re: MS Access importing visual foxpro tables

Dave Mosby

Naomi,

Thank you too!

Dave Mosby