Connecting to a FoxPro DBF file

[Steve Roberts]

Access 2003 + SQL Server 7.0

I need to open a FoxPro .DBF file, Delete the entire contents then Import
new data into it and close the DBF. I really don't need a full time link to
the file. Is there a way to do this using a Stored Procedure or VBA?

The 2 existing Queries from a .mdb file that I have are below just for
clarity.

I need to Connect to \\Printsvr\pcounter\PPOPUP.DBF

Then Delete the Contents of the current File

DELETE PPOPUP.*
FROM PPOPUP;

Then Insert New Data.

INSERT INTO PPOPUP
SELECT [Advantage Projects].*
FROM [Advantage Projects];

Thanks in advance for your suggestions

Steve

 

[Cindy Winegarden]

Hi Steve,

Have you thought about using a linked server? Then you could use the SQL
below in a SQL stored procedure to update the Fox data.

One note of caution: when records are deleted in a FoxPro table they are
actually only hidden. They are not physically removed from the table until
the table is "packed." That means your table will grow and you could have
problems with primary keys.

One way around this is to recreate the table each time. You can use "Create
Table MyTable (Field1 I....)" to do this. You can find the abbreviations for
the FoxPro data types in the FoxPro Help. Another way is to create an empty
table somewhere and then each time copy the empty table to wherever you want
(delete or overwrite the one that's there from the last time) and then add
the records.

 

[Steve Roberts]

Your solution for deleting the table was awesomely simple!

I can't seem to figure out how to connect to the linked server and perform
an update. Since the table I am updating was created by another program I am
not sure if it has a primary key which SQL requires to perform an update.

If I use the code below I can successfully insert 1 row in the .dbf file.

Dim conn As ADODB.Connection
Dim rsPcounter As ADODB.Recordset

Set conn = New ADODB.Connection

With conn
.Provider = "VFPOLEDB.1"
.ConnectionString = "Data Source=\\PRINTSVR\PCOUNTER"
.Open
.Execute ("INSERT INTO PPOPUP.dbf(CODE, SUBCODE, DESCR, PAGES)
Values ('Test','.','test',0)")
End With

If I try to extend this functionality by referencing a View in the current
Access.ADP file(see query line below) that the code is located in I get an
error 2147217900 "Command Contains Unrecognized Phrase/Keyword"

.Execute ("INSERT INTO PPOPUP.dbf(CODE, SUBCODE, DESCR, PAGES) SELECT Code,
SubCode, Descr, Pages FROM dbo.AdvantageProjects)")

Any more awesome Ideas?

Thanks

Steve

Hi Steve,

Have you thought about using a linked server? Then you could use the SQL
below in a SQL stored procedure to update the Fox data.

One note of caution: when records are deleted in a FoxPro table they are
actually only hidden. They are not physically removed from the table until
the table is "packed." That means your table will grow and you could have
problems with primary keys.

One way around this is to recreate the table each time. You can use
"Create Table MyTable (Field1 I....)" to do this. You can find the
abbreviations for the FoxPro data types in the FoxPro Help. Another way is
to create an empty table somewhere and then each time copy the empty table
to wherever you want (delete or overwrite the one that's there from the
last time) and then add the records.

--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
(e-mail address removed) www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden

Access 2003 + SQL Server 7.0

I need to open a FoxPro .DBF file, Delete the entire contents then Import
new data into it and close the DBF. I really don't need a full time link
to the file. Is there a way to do this using a Stored Procedure or VBA?

The 2 existing Queries from a .mdb file that I have are below just for
clarity.

I need to Connect to \\Printsvr\pcounter\PPOPUP.DBF

Then Delete the Contents of the current File

DELETE PPOPUP.*
FROM PPOPUP;

Then Insert New Data.

INSERT INTO PPOPUP
SELECT [Advantage Projects].*
FROM [Advantage Projects];

Thanks in advance for your suggestions

Steve

 

[Cindy Winegarden]

Hi Steve,

Your Execute statement sends a SQL command string to the Fox OLE DB
provider. You are sending "Insert Into ...Select" and the OLE DB provider is
trying to perform the Select against the available Fox data; it is not
selecting your "local" data to the OLE DB provider. FWIW, "Insert Into ...
Select" was added in VFP8, so if you're intending to select against the Fox
data you'll need to be sure you have the latest OLE DB provider,
downloadable from msdn.microsoft.com/vfoxpro/downloads/updates.

If you need to insert values from your "local" data I suppose you could put
it in a recordset, iterate through the recordset, and send parameterized SQL
commands.