I am unsuccessful at linking tables from a visual FoxPro database to MS Access 2010; however I have been able to import a table into Excel.
I have download the Microsoft OLE DB Provider for Visual FoxPro 9.0 (C:\Program Files (x86)\Microsoft Visual FoxPro OLE DB Provider) and excel reconices this but access doesn't?
These are the steps I took in Access:
- External Data
- ODBC Database
- Link to the data source by createing a linked table
- Machine Data Source
- Select a driver... Microsoft Visual FoxPro Driver
- Error You must firt install the current version of the Visual FoxPro ODBC Driver...
In Excel I have recorded the a macro which shows the steps i did with the GUI, and it works:
' VFP Macro
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=VFPOLEDB.1;Data Source=C:\TestNET\DATA\TestS.DBC;Mode=Share Deny None;Extended Properties="""";User ID="""";Mask Password=" _
"False;Cache Authentication=False;Encrypt Password=False;Collating Sequence=MACHINE;DSN="""";DELETED=True;CODEPAGE=1252;MVCOUNT=163" _
.CommandType = xlCmdTable
.CommandText = Array("`client`")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"C:\Users\Dustin\Documents\My Data Sources\(Default) client.odc"
.ListObject.DisplayName = "Table_Default__client"
I am not good at writing code, but is there a way to link all the VFP tables in MS Access through code, if the GUI doesn't work?