Accessing Visual FoxPro Data When You Get the ODBC “Not a table (#123)” Error

A while back, someone handed over some scheduling data in a somewhat clunky old application in desperate need of modernization. I looked under the hood and figured out that this thing uses a FoxPro database! Microsoft discontinued development of Visual FoxPro way back in 2007, and I hadn’t seen a working FoxPro database since the late 1990s…so I knew it might be tricky to get the data out.

TL;DR: I fired up Microsoft Access to see if I could link to the external data. This was harder than I expected because Windows 10 and Access 2016 no longer ship with a Visual FoxPro ODBC driver. Once I found one, I could easily link to all of the tables—except for the ones that included variable-width memo fields. I could use an OLE DB provider to get to tables with memo fields, but only via Excel or programmatically. Access apparently can’t create a linked table via OLE DB.

Finding a Visual FoxPro ODBC Driver

As I mentioned above, if you’re on Windows 10 and Microsoft Access 2016, chances are good that your machine doesn’t have an ODBC driver for Visual FoxPro installed like earlier versions. You can find a page on Microsoft’s website that used to have a link to download of the driver, but it’s been removed since they no longer support it. Thankfully, it seems you can reliably find a link to the old Microsoft VFPODBC.msi file at the Visual FoxPro Wiki.

Creating Linked Tables in Access 2016

I know Microsoft Access isn’t an ideal tool for creating applications, but I sure saw and wrote plenty of them back in the day. It’s still a great tool to quickly test stuff like this.

So I clicked over to the External Data ribbon, clicked New Data Source→From Other Sources→ODBC Database and configured a connection to the FoxPro data I wanted to get into. I easily pulled up a table listing and decided to select all the tables to start off. Several prompts came up to indicate a unique identifier. Being that my attention span for message and dialog boxes has shrunk to zero, I answered the first two or three, and then just clicked Cancel through the rest. Everything seemed all too easy!

Problems with Tables Having Memo Fields :-/

Now that I knew this was indeed a FoxPro database and that I could access its contents, I set my focus on analyzing the table structure to find the specific data I needed. At this point I hit a snag…

Microsoft Access - Could not execute query; could not find linked table. [Microsoft][ODBC Visual FoxPro Driver]Not a table. (#123)
The error message I received from Microsoft Access when I tried to link to Visual FoxPro data tables that included any memo fields.
After much googling and digging, I concluded that the cause of these error messages was that the Microsoft Visual FoxPro ODBC driver was choking on the memo fields.

. Every other table I linked to via ODBC worked perfectly, but any table that included one or more memo fields bombed.

If you look at the file structure of a FoxPro database, you see that each data table has a corresponding .DBF and .CDX file. Using a text editor, I could infer that the .DBF file contains headings and data for the table’s fixed-width fields, and that the .CDX file contains indexes. Any table that included a variable-width memo field had a corresponding .FPT file containing that data.

So if you want a shortcut to determine whether ODBC will get you into all of your FoxPro tables, look for .FPT files. If you see them, you will have problems. If you don’t have problems, I hope you’ll tell me about it! The best solution I’ve found is only a workaround. I’d love to know if you’ve found a method to link directly.

Turning to OLE DB

In my quest to crack this open, I found others recommend OLE DB as a way to overcome the memo field problem. While an OLE DB driver for Visual FoxPro no longer ships with Windows 10 or Access 2016 of course, I easily tracked one down on Microsoft’s website here.

After I installed it, my next problem was how to use it in Microsoft Access to create a linked table. After again scouring the Internet, I found no way to do this. Access apparently only allows you to create linked tables to Access, SQL Server, and ODBC databases. I could however import the data into Excel, but that too was tricky.

Importing Visual FoxPro Data Into Excel via OLE DB

Getting to external data via OLE DB is pretty intuitive in Excel…up until you go to create the connection string. (Click Data→Get Data→From Other Sources→From OLE DB.) After I clicked Build, selected the Visual FoxPro driver, and clicked Next, Excel crashed…every single time…

Microsoft Excel has stopped working while setting up an OLE DB connection string
Building an OLE DB connection string to a FoxPro database crashes Excel after the first step…seriously?!?

Thankfully there’s more than one way to build an OLE DB connection string. For the most part, it’s pretty straightforward (so long as you don’t need any special options):

Provider=vfpoledb;Data Source="C:\FoxProDatabaseFolder\Database.dbc";

Once I had my connection string, I pasted it into Excel and it connected to every table in the FoxPro database. I could import all of the data I needed with no problems.

From here I could link to the data in Excel to use it in Access. I could also use some VBA code to directly import the data into Access. These are not simple ways to get to my data, but at least I know I can get it out when the time comes.

4 thoughts on “Accessing Visual FoxPro Data When You Get the ODBC “Not a table (#123)” Error”

  1. Hi Lee
    I am faced with the same problem as you. I have a number of .dbf files whose contents need to be pulled into Excel 2016 so that folks have access to the data. This was never an issue in Excel 2013 where you could select From Other Sources > From Data Connection Wizard > Other / Advanced > Microsoft OLE DB Provider for Visual Foxpro.
    As you are aware, that option is no longer offered in Excel 2016. I do not have the technical expertise to configure the connection and was wondering if you could assist?
    I have downloaded copies of VFPOLEDBSetup.msi and vfpoledb.msm from the official Microsoft site (I did the same with VFPODBC.msi, although I am not sure if this is required).
    I assume that I need to install the vfpoledb driver but is there anything else I need to do before I get to configuring the connector?
    In Excel 2016, we have From Other Sources > From Data Connection Wizard > Other / Advanced > … and this is where my wheels fall off.
    I notice my screens look different from yours (probably due to 2016 updates) but we both end-up on the Data Link Properties dialog.
    Which of the OLE DB Providers do I select and how do I go about the configuration? I have made various attempts at replicating your instructions but have drawn a blank. Note that I work at table – not database level. I can provide an example of the .odc file generated by Excel 2013, if that would help. I have tried making use of a modified version of this but it did not work – possibly due to
    incorrect driver configuration.
    I would be most obliged for any insight and assistance you could provide.

    1. Jon, my sincerest apologies for letting your comment slip through the cracks! I have hopefully just installed a plugin on my site that will relieve me of the insane amounts of spam I get–and your legitimate comment was lost in the shuffle :-/

      Anyway, I may be able to help…but it’s been over a year now. Let me know if you ever found success getting into your tables.

      1. Lee, I was wondering if this solution works with 64 bit versions of MS Excel, or if you must use the 32 bit version of excel?

        1. I couldn’t say for sure. I just bought a new laptop though, and installed the 64-bit version of Office on it. I run the 32-bit version on my main machine…and it wouldn’t surprise me if this only works with 32-bit Excel.

          And sorry I took so long to reply! :-/

Leave a Reply

Your email address will not be published. Required fields are marked *