Connect to Jantek - Visual Foxpro ODBC and import data to Excel, Access or SQL

• I ended up using the following steps to move data from FoxPro to SQL
• Excel 2013 32-bit using Microsoft query Advanced ODBC connection using the FoxPro 32-bit ODBC driver
• Set the connections to automatically refresh
• Installed Microsoft Data Access Components 64-bit using /passive to get both drivers on the machine
• Read data into sql using
• SELECT * FROM OPENROWSET ( 'Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0; Database=C:\JantekLink\janteklink32.xlsx;HDR=Yes', 'Select * from [EMP$]' )
• Setup System Scheduler to launch the excel sheet and close it
• Setup system scheduler to run a script to read the updated data from the sheet into sql

Notes on Connecting to Foxpro

• Cannot connect as linked server if using 64-Bit SQL (FoxPro is 32-bit)
• This method only works if connecting to 32-bit BQL.
• Use Visual FoxPro OLE DB Driver to connect (ODBC has been replaced, however, the ODBC still works to connect to the table sources)
• https://msdn.microsoft.com/en-us/vfoxpro/bb190232

http://serverfault.com/questions/361226/how-to-successfully-connect-to-foxpro-database-files-using-mssql-linked-server-f


Use Sybase Anywhere 64-bit ODBC driver
https://www.progress.com/download?interface=odbc&ds=sybase&os=win-64

Install 32-Bit office AND 64-bit Data Access Components in /passive mode and use an Access Database or Excel sheet as an intermediary between FoxPro and SQL.

Jantek Folder Structure - Visual FoxPro Tables - Free Tables

Jantek Database Folder - Free Tables
\DBF.JTA\EMPCLOCK.DBF - employees site assignments
\DBF.JTA\AUTOLOG.DBF - system data transfer log
\DBF.JTA\TIME.DBF -  clockin/out transactions
\DBF.JTA\Payroll.DBF -  approved payroll

Jantek Databases - FoxPro databases
\DBF\Company.dbf - Companies
\DBF\Branch.dbf - Branches
\DBF\Emp.dbf -Employees