Sample Header Ad - 728x90

SQL Server Linked Server with Microsoft OLE DB Provider for Visual FoxPro?

1 vote
0 answers
2254 views
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, -> Server Objects -> Linked Servers and clicking New Linked Server ... from the context menu. 1. Then in the resulting New Linked Server dialog: 1. On the General page: 1. Entered a name in the Linked server: textbox 1. In the Server type: radio button, selected Other data source 1. In the Provider: drop down selected Microsoft OLE DB Provider for Visual Fox Pro 1. In the Product name: textbox entered VFPOLEDB 1. Entered the path to the data in the Data source: textbox. 1. 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 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_ and very similar to `. So I renamed the with a old_ prefix, and renamed the new ones to 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?
Asked by leeand00 (1722 rep)
Mar 22, 2016, 01:19 PM