How to use INSERT INTO.... SELECT FROM query to export MS SQL data in FoxPro 2.6 DBF

Hi all,

This is my very first post to this forum. How to use the INSERT INTO... SELECT FROM query to export MS SQL 2000 data into FoxPro 2.6 DBF file? I want to write a VB 6.0 program, and already connected to MS SQL 2000 Database by MS SQL ODBC connection. Now I want to Export this MS SQL data to FoxPro 2.6 DBF. I found ROWSET option, but don't know how to use it for FoxPro.
Please help me.

Thanks in Advance.

Regards,
Rajeev VandakarYou can try using a DTS package to export the data directly and you can schedule the same too.|||

Hi Rajeev,

If you want to just copy the whole table then Satya's idea is great. If you want to select specific rows then here's one way that works:

Download and install the FoxPro and Visual FoxPro OLE DB data provider from msdn.microsoft.com/vfoxpro/downloads/updates. It works for all versions of Fox tables.

Create a linked server:

EXEC master.dbo.sp_addlinkedserver
@.server = N'VFP_Test',
@.srvproduct=N'Microsoft Visual FoxPro OLE DB Data Provider',
@.provider=N'VFPOLEDB',
@.datasrc=N'C:\Temp\',
@.provstr=N'VFPOLEDB.1'

After that you can access data with the four-part naming convention: LinkedServerName...TableName. Note the 3 dots as FoxPro has no catalog or schema. I tested by creating a FoxPro 2.x format table C:\Temp\Table_1. I also created a table in my SQL Server test database called Table_1 with the same structure as the Fox table. Then I inserted data successfully by running the following command in a SQL code window:

Insert Into VFPTest...Table_1 Select * From Table_1

The following VB6 program worked for me:

Public Sub Main()

Set conn1 = New ADODB.Connection
conn1.Open _
"Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=Test;Data Source=."

Dim sql1 As String

sql1 = _
"Insert Into VFPTest...Table_1 Select * From Table_1"

Set cmd1 = New ADODB.Command
With cmd1
.CommandType = adCmdText
.ActiveConnection = conn1
.CommandText = sql1
.Execute
End With

End Sub

||| Thanks Cindy Winegarden,

I had created the Linked Server for the FoxPro 2.6 DBF and its functioning as I wanted.

|||

Cindy,

I am trying to do something similar, but cannot get variations of your approach to work. I have successfully created a Linked Server (MBFOXPRO) to a free directory of VFP tables using the VFPOLEDB Provider, and I can select data all day long. However, any sort of update or insert fails.

If I try:

Code Snippet

update openquery(MBFOXPRO,'select * from tkflin where recnum=500 and linnum=68')

set prtdsc='test update'

I get the error:

Msg 7301, Level 16, State 2, Line 3

Cannot obtain the required interface ("IID_IRowsetChange") from OLE DB provider "VFPOLEDB" for linked server "MBFOXPRO".

If I try:

Code Snippet

update MBFOXPRO...tkflin

set prtdsc='test update' where recnum=500 and linnum=68

I get the error:

OLE DB provider "VFPOLEDB" for linked server "MBFOXPRO" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Msg 7333, Level 16, State 2, Line 15

Cannot fetch a row using a bookmark from OLE DB provider "VFPOLEDB" for linked server "MBFOXPRO".

I have read a lot of different posts on this subject and some older ones indicate that updates through a Linked Server cannot be done at all (e.g., http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=144712&SiteID=1).

I am pretty confident I have the latest and greatest VFPOLEDB Provider.

My config info is:

SQL Server 2005 Developer Edition running on Windows SBS 2003. I'm not sure the version of the FoxPro tables (they are part of a 3rd party accounting package) but I can manipulate them just fine using VFP 9.

I can run updates using Ad Hoc SQL statements from VB.Net just fine, but it sure would be handy to be able to keep all of my data stuff neatly on the SQL Server side of the fence.

Any suggestions?

Thanks,

Todd