Execute querys to DBF files on SQL Server

Question

As I've posted on this thread I need keep synchronized an SQL Server (not only SQL Server, if you recommend another server which do the same thing) and VFP DBF tables to use on the systems of the company.

As @alex-k said, Linked Server doesn't support INSERT, UPDATE nor DELETE but I need the CRUD statements to the systems work. I've already tried the suggestion of @alex-k but returns error.

As a lot of things in computation has more than one way, what do you recommend to my situation?

Thanks.

Answer 1

I've actually just installed SQL2008R2 on an old machine. Successfully created a linked server and tried the update direct from within SQL. Ran into your same error that it won't work. So, I looked around too and found something on "tek-Tips" forum that descdribed opening a rowset to apply an update via this link

When trying that, I got a follow-up error listed below.

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

Since I don't want to open up features regarding security, even on a machine just for sample purposes, you might be more open to enabling/disabling this feature and trying the suggestion from tek-tips...

Good luck otherwise.

Answer 2

Have you considered building a small Visual FoxPro application to do this. Visual FoxPro can read and write to both SQL Server and DBFs. We have done this numerous times. The key is to understand what records have changed, and using surrogate primary keys on both sets of data.

Rick

Answer 3

I have not used them since their pre-apollo days (uit sed to be called successware or SDE), but this company might have something that will help. The problem you are going to encounter with other solutions that can read-write the dbfs is that they might not be able to keep the index files up to date (cdx, ndx, etc). If they don't have a driver that helps, you might have to resort to creating an extended stored procedure to natively call their interface. Hopefully you won't have to resort to that.

-don

Answer 4

Today DBF files are not widely supported. As far as I know Microsoft Access still supports dBASE DBF files which should be compatible with VFP DBFs.

You can build some kind of gateway in MS Access responsible for synchronizing VFP and SQL Servers databases.