First off, let’s explain a little about the common situation that this “how to” is targeted towards. FoxPro 2.x was a big part of many applications in the early 90’s and those applications ran/run just fine. However, compared to SQL Server 2000, or any other modern DBMS, it’s a joke.
Now it’s time to upgrade your FoxPro database to SQL Server and we’re about to run into a problem. Since it has been such a long time since the implementation of the FoxPro database, some of the software we need to connect to it has been knocked off the “supported” list by Microsoft. So, the problem is going to be we can’t use the obvious choice of an ODBC driver when importing the FoxPro tables into SQL Server.
Let’s start from the beginning. Suppose you have a handful of FoxPro tables that you want to import into SQL Server. First, you would stop any DBMS that is accessing those tables in order to release them so we can access them. Next, in SQL Server, open the Data Transformation Services Wizard. You can read the little explanation of what it is or you can just click “next”. Now we have to select a data source. Since FoxPro tables are stored with the .DBF extension, you must choose “Microsoft FoxPro VFP Driver (*.dbf).” Assuming we don’t already have a proper Data Source Name (DSN) to connect to this source, we must create one.
If you already have one, then it is imperative that you delete it first. Click “new…” with the “User/System DSN:” radio button selected. The ‘Create New Data Source” dialog opens and you have three choices. These choices are basically a matter of opinion and ultimately do not effect the import. Pick one and click “next.” Now, here is where our biggest problem arises. Since we have old FoxPro tables and not Visual FoxPro tables, the obvious selection here would be the “Microsoft FoxPro Driver(*.dbf).” Unfortunately, that’s not correct. Actually, since the release of Microsoft Data Access Components (MDAC) 2.1 and the Visual FoxPro Driver, the installable ISAM* ODBC driver is no longer supported by Microsoft. If you had proceeded with the original selection you would have eventually run into and error message similar to this; “The Microsoft FoxPro driver is no longer supported. Use the Visual FoxPro Driver instead.” You have to choose the Microsoft FoxPro VFP Driver (*.dbf). Click “next” then click “finish.” Here, you must choose “free table directory.” This is because your old FoxPro tables don’t have a .dcb extension like Visual FoxPro databases. Also, click “options” and deselect the NULL checkmark box. This makes sure that the ODBC driver doesn’t attempt to put NULL values into the FoxPro 2.x tables. This would be bad since FoxPro 2.x tables don’t accept NULL values. Now, type in the path to the tables and click “ok.” Set up your destination, select what tables, etc.. in the DTS Wizard and let her fly. It should copy all the tables into the SQL database that you chose. Viola!
Although this seems easy, it is not an apparent solution. Why would you choose the Visual FoxPro Driver for old FoxPro tables? There are a few other discrepancies that I have noticed in the GUI for the DTS Wizard that I’m not particularly happy with, however I’m not 100% sure that it is Microsoft’s fault…..yet. As soon as that is confirmed I will expose those discrepancies, or I will expose my unique problem if in the unlikely event it is my computer’s fault. In the meantime, import FoxPro tables at will and good luck.
Nick Pile is an Murdok staff writer
