How to PACK or ZAP VFP Table using VFPOLDEDB?
Am looking for an equivalent to ZAP to remove records from a VFP Table using VFPOLDEDB in VB.NET 2008
And an equivalent to PACK, as a DELETE query leaves the deleted records in the file.
How can I remove these deleted records, so that files do not keep growing in size?
And an equivalent to PACK, as a DELETE query leaves the deleted records in the file.
How can I remove these deleted records, so that files do not keep growing in size?
Carl: s/he wants to do it using VFPOLDEDB. Does your answer apply to that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here's some code from an interactive ADO session within VFP that allows a separate session even from the currently open session in VFP. The ADO session ends up with its own environment and prevents the VFP its in to open the table I open exclusively in the ADO session. [I made liberal use of the oRS=NULL to assure I got old return values out of the way so as not to fool me into thinking the next return value was a good one. You can handle it more elegantly in your VB.Net environment.] At worst, this VFP code for an ADO session that uses the OLE DB Provider for VFP gives you an idea of what is possible with the ADO session and the use of the EXCESCRIPT() function I've mentioned in previous posts.]
o = NewObject('ADODB.CONNECTION')
** open up the connection pointing at folder with free tables/dbfs
o.Open('Provider=VFPOLEDB;Data Source= C:\Documents and Settings\warnerc\My Documents\Visual FoxPro Projects')
? o.Properties('Provider Version').Value && returns
o.Execute([SET TABLEVALIDATE TO 0]) && this is a setting I recommend OLE DB Provider for VFP users set to increase speed
ors = o.Execute("EXECSCRIPT([USE FlrDrains EXCL])")
ors = NULL
ors = o.Execute("EXECSCRIPT([RETURN RECCOUNT()])")
? ors.Fields(0).Value && returns total number of records in currently selected table-- shows 11
ors = NULL
o.Execute([SET DELETED OFF])
ors = o.Execute("SELECT SET('DELETED') FROM FlrDrains WHERE RecNo()=1")
? ors.Fields(0).Value && returns "OFF"
ors = NULL
** check to see if any records in FlrDrains.dbf are marked for deletion
ors = o.Execute("SELECT CNT(*) FROM FlrDrains WHERE DELETED()")
? ors.Fields(0).Value && returns total number of records marked for deletion-- shows 1
ors = NULL
ors = o.Execute("EXECSCRIPT([SELECT FlrDrains])")
ors = NULL
ors = o.Execute("EXECSCRIPT([RETURN DBF()])")
? ors.Fields(0).Value && just verifying the table/dbf is selected and ready for action
ors = NULL
ors = o.Execute("EXECSCRIPT([PACK])")
? ors.Fields(0).Value && returns a value of .T. saying it PACKed the selected table/dbf
ors = NULL
ors = o.Execute("EXECSCRIPT([RETURN RECCOUNT()])")
? ors.Fields(0).Value && see if we have fewer records than before we PACKed-- returns 10, one less than we started with
ors = NULL
ors = o.Execute("EXECSCRIPT([USE in flrDrains])") && close table
ors = NULL
o=nullASKER
Many thanks for your help
You're welcome.
As I said, I've never use an external connectivity driver to PACK a table since I work natively in VFP. But, now even I know it can be done and how to get there.
As I said, I've never use an external connectivity driver to PACK a table since I work natively in VFP. But, now even I know it can be done and how to get there.
EXECSCRIPT( ) Function
http://msdn.microsoft.com/en-us/library/9ssewd6y(VS.80).aspx
If you can't get exclusive access, look at the following:
How to Recycle Deleted Records in a Multiuser Environment
http://support.microsoft.com/search/default.aspx?mode=a&query=PACK+records&catalog=LCID%3D1033&1033comm=1&spid=1111