Avatar of logicalc
logicalcFlag for United Kingdom of Great Britain and Northern Ireland asked on

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?
Visual Basic.NETFoxPro

Avatar of undefined
Last Comment
CarlWarner

8/22/2022 - Mon
CarlWarner

You must have EXCLUSIVE use of the VFP table to issue a PACK command or a ZAP command.  If you can assure yourself EXCLUSIVE use of that table by (re)opening it (USE <mytable> EXCLUSIVE), you could also then issue either the PACK or the ZAP via VFP commands sent through ADO and the use of the EXECSCRIPT() function, a function that is supported by the OLE DB Provider for VFP.

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
ramrom

Carl: s/he wants to do it using VFPOLDEDB. Does your answer apply to that?
ASKER CERTIFIED SOLUTION
CarlWarner

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
CarlWarner

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=null

Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
logicalc

Many thanks for your help
CarlWarner

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.