1

We were having some issues with our old Microsoft Visual Fox Pro ODBC driver that we shoehorned into Windows 7 and is no longer supported by Microsoft and has not been for quite a long time...

So when our anti-virus software finally stopped it from working, we needed to find another way to make it work. The Anti-virus company couldn't help us; they said it was a Microsoft problem, but they did recommend a couple of related KBs from Microsoft, which stated that we should use the Microsoft OLE DB Provider for Visual Fox Pro.

After making sure that the Microsoft OLE DB Provider for Visual Fox Pro was installed, I created a linked server in Microsoft SQL Server Management Studio:

  1. Right clicked the following element in the left hand pane, <SQL Server Instance> -> Server Objects -> Linked Servers and clicking New Linked Server ... from the context menu.
  2. Then in the resulting New Linked Server dialog:
    1. On the General page:
      1. Entered a name in the Linked server: textbox
      2. In the Server type: radio button, selected Other data source
      3. In the Provider: drop down selected Microsoft OLE DB Provider for Visual Fox Pro
      4. In the Product name: textbox entered VFPOLEDB
      5. Entered the path to the data in the Data source: textbox.
      6. Clicked OK

I was then able to query the data in the Linked Server, and in order to make this accessible to MS Access on the workstations over an ODBC connection (as well as make it more secure) I created VIEWs with only the necessary fields for each of the tables in question using the following syntax:

USE <name-of-database-to-store-views-in>
GO

CREATE VIEW [dbo].[name-of-view-likely-the-same-name-as-referred-to-in-access] AS 
SELECT *
  FROM [name-of-linked-server]...[name-of-dbf-file-without-extension]
GO

Then on the workstations with the MS-Access Database that used to use the VFP ODBC driver, I created a new ODBC connection to the SQL Server containing the Linked Server, so I could access the VIEWs I just created.

Then in the MS-Access database I added the linked tables (VIEWs really) from the ODBC connection, they were imported being named as dbo_<view-name> and very similar to <old-linked-table-name>. So I renamed the <old-linked-table-name> with a old_ prefix, and renamed the new ones to <view-name> removing the dbo_ prefix.

It now appears that the Microsoft Access database is able to access those tables correctly, via Linked Table, via ODBC, via SQL Server, via VIEW, via Linked-Server, via Microsoft OLE DB Provider for Visual Fox Pro without the anti-virus getting in the way; however, I'm using test data to do this at the moment and NOT production; production has several other people accessing it, modifying it, etc...so provided that it turns out to be the case that the MS-Access database is just pulling data, and not writing it out, do you think this will work alright in production? And what if it is writing the data out? Will I be okay then too?

3
  • 1
    Get a couple of people to try it in the non-production environment. It sounds like it should work.
    – Hannah Vernon
    Commented Mar 22, 2016 at 15:15
  • As someone who worked with FoxBASE/.../MS_Visual_FoxPro for many years and tried to support access to legacy data after Microsoft [effectively] abandoned it, I feel your pain. However, reading your final paragraph describing the path from Access (linked tables) through SQL Server (linked server) to FoxPro_OLEDB (technology orphaned for years now) and then asking "Will I be okay...?" is both bewildering and heartbreaking. My advice: Just stop the madness. Do "whatever it takes" to permanently get the data out of FoxPro format and move on. Commented Mar 26, 2016 at 1:04
  • I can't do that because that's what their system still uses!
    – leeand00
    Commented Mar 26, 2016 at 19:12

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.