1

We have a really old data source (file-based) in Visual FoxPro, that we currently use via linked table through an ODBC data source. Recently, we started having an issue because the latest update to our Anti-virus Suite is stopping it from connecting in this manner. There are other ODBC data sources which do not use the same driver which do not have this issue.

Also the Visual FoxPro driver ODBC driver is being shoehorned in using the registry because it's no longer supported by Windows 7.

Is adding my Visual FoxPro data source as a linked server in SQL server and then in the MS-Access database, relinking the effected tables to it a possible solution to this issue? Note: the MS-Access databases use VBA and queries to retrieve and manipulate the data.

3

You should be able to get away with this by creating views in SQL Server that are effectively wrappers for tables in the linked server. You will of course have to deal with the extra layer of security configuration for the linked server, thus ensuring that the Access users are connecting to the external data with the proper credentials (whether it be something per-user, or just a global set of credentials that's used for everybody accessing the FoxPro data).

Keep a close eye on performance, since Access is going to be generating queries meant for SQL Server, and it tends to be a bit chatty to begin with. SQL Server is then going to generate queries for fetching the data from FoxPro. With that many layers of query generation, performance could be a crapshoot for large tables, and it'll be pretty difficult to diagnose or improve it.

If the FoxPro data is mostly static historic data, then I'd recommend bringing it into SQL Server. If there's still read/write activity happening, then you'll have to either settle for the linked server approach, or periodically refreshing the data in SQL Server via SSIS or some other ETL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.