Error using Compact() 1

[LWB]

I have a button on a form that compacts several tables. I am using the code shown in the help example. It used to work on all the tables, but at one point it stopped working on the most complex table, but works fine on the others. I can pack the table using the restructure command interactively, but this doesn't help a user with Runtime. This occurs in both Paradox 7 and Paradox 8.

Lynn

 

[footpad]

Lynn,

If it used to work and no longer does, that sounds like one of two things:

a) The table is somehow open when compact is called. Typically, this happens when you don't close TCursors formally. (Perhaps there's a value that is no longer part of a lookup table or the end-user has inadvertantly added a duplicate key/index value. Hard to say at this point.)

b) You might have either corrupt indexes or a corrupt tables. I'd start by deleting and recreating the indexes (this will pack the table by default). Next, I'd add a few dummy records, delete them, and then run a script that only compacts this particular table. If it works, then you should be finished. If it doesn't, then you may have a corrupt table.

Hope this helps...

-- Lance

 

[LWB]

Lance,

Thanks for your reply. I suppose it is possible that a TCursor was not closed. The table is the main table in the database and TCursors to it are opened and closed often. Is there an easy way to determine this? I am able to open the table interactively (with the same forms open) in the restructure mode which is not normally possible if the table is somehow in use.

The table does have a couple of aliases assigned to it. Perhaps that makes a difference? But they have always been there.

The Compact command quit working on this table some time ago (perhaps 2 years). I can no longer remember what changes were made about that time. I have a nagging feeling that I made some changes to the table structure about that time, it might have been an increase in the length of a field or the addition of a date of when the record was last changed. This may have caused a change in the table type (to the Paradox 7,8,9 format from whatever was before).

The table has grown by about 20,000 records since the compact() quit working (It has about 50,000 records in it now). I have seen no signs of a corrupt table - is it possible to be corrupt yet be working correctly in all other ways? And I can compact it interactively.

Lynn

 

[footpad]

Lynn,

Hm. I have seen some reports where restructuring a table with cause compact() to fail until the table is rebuilt, but I've not seen this behavior myself. At this point, I'd probably try rebuilding the table directly and then seeing if compact() starts working again. That's the advice I recall seeing on some of the newgroups some time ago.

I have also heard that it's a good idea to rebuild tables after restructuring them. I've not had to do this in my applications, but I've heard it enough from other people that it stuck.

In general, there aren't any ways to know whether or not a TCursor is still open in the background...until, that is, you get a "record already locked" error while trying to update the same table with a second tCursor.

This is why I almost always recommend people formally close their TCursors before the end of the method. Example:

if tc.isAssigned() then
   tc.close()
endIf

In a way, it's more defensive programming that anything. It was far more important in older versions of PAradox, but I still do it today and I run into very few tCursor problems.

Aliases shouldn't make a difference; they're only used to locate the data when the TCursor is opened.

Hope this helps...

-- Lance

 

[LWB]

Lance,

You were right about rebuilding the table. I created a new table (interactively) borrowing all the structure, indexes, etc from the existing table. Then I added all the data from the old file to it. After renaming the old file I changed the new file's name to the original name. Now Compact() works fine.

I have always tried to close any TCcursors I opened, but it is always possible I missed one somewhere!

Thanks for your help. I never would have guessed that rebuilding the table would fix the problem.

Lynn