Pack a Free Table

[Sware]

VB6 app using the VFPOLEDB Driver to work with free tables (.DBF files). Three such tables, each opened on its own Connection when the app is run and remain open. The following code works on one of the Connections:

conn1.Execute ("USE IN SELECT([GHM_V70])")
conn1.Execute ("PACK GHM_V70")

In this case the name of the file is GHM_V70.DBF. However, when I use the following code on a file named GHM_V70.HST (a DBF file whose name with the .HST extension is used in the DataSource for its Connection) an error occurs.

conn3.Execute ("USE IN SELECT([GHM_V70.HST])")
conn3.Execute ("SET EXCLUSIVE ON")
conn3.Execute ("SET DELETED OFF")
conn3.Execute ("PACK GHM_V70.HST")

The error is "Cannot Open File...." The error message include both the Path and the name of the file.

What could be causing this situation?

 

[Mike Lewis]

Sware,

This is probably because the table is aleady open, perhaps by another user or on another connection. Is that possible?

By the way, this has got nothing to do with the fact that it is a free table. The point is that the PACK is trying to open the table exclusively, and it presumably cannot do so.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Sware]

Mike, thanks for your input. There is only one user and the .HST table is not open on another connection. In fact, as part of the testing on this situation I closed conn3, set it to Nothing and then reopened it with a new connection string.

Couls this situation have anything to do with the fact that other connections are open on different tables?

 

[Sware]

RESOLVED! There is a recordset associated with the file (rs3). Closing the recordset (rs3.Close) was not sufficient to prevent the "Can't Open File" error. However, when I added rs3.ActiveConnection = Nothing the error went away and the Pack succeeded.

 

[Mike Lewis]

Sware,

That makes sense. When you retrieved the recordset, it would have opened the table behind the scenes. You had to remove the connection in order for it to be closed.

Glad to hear you've resolved it.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Sware]

I spoke too soon about a resolution. The "midstream in the program" Pack that worked (or I thought worked) doesn't work - I get the "Can't Open File" error. However, if I Close and Set to Nothing ALL recordsets and connections, the Pack does work. (Of course, I then need to reopen everything.)

Given that the file I want to Pack is used BY ONLY ONE of the connections, I don't understand why it's required that ALL connections need to be closed. If all the files (tables) were in a Container (.dbc file) the requirement may make sense. But, these are so-called "free tables." They are all in the same folder but not part of a .dbc file.

This situation seems to be a FoxPro flaw. I guess "what is, is." But, why is it?

 

[Sware]

I received the following response in another forum:

If you have another connection open, you can't get exclusive access and you can't pack the table.

So, the situation seems to be related to gaining exclusive access. Before issuing the Pack command I do SET EXCLUSIVE ON for the connection on which I want to do the Pack. What I don't understand, and what seems to be illogical, is that other separate and presumably independent connections need to be closed in order to have exclusive access on one connection.

However, I guess "what is, is." Not to steal phrases from Al Gore but the situation appears to be "an inconvenient truth.

 

[Mike Lewis]

Sware,

What I don't understand, and what seems to be illogical, is that other separate and presumably independent connections need to be closed in order to have exclusive access on one connection.

But the whole point of exclusive access is that only one process (or connection) can open the table at a time. If another process has already opened it, you can't get exclusive access. Which is why you must close the table in that other process.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Sware]

I have done some experimentation which seems to disprove the claim that you can't gain exclusive access on a connection if another different connection is open.

To "purify" the situation I moved the Pack code to the very beginning of program execution and did the following:

-- Established and opened conn1 and rs1 on the table/file GHM_V70.DBF and left them open.
-- Established and opened conn2 and rs2 on the table/file GHM_V70.TRN and left them open.
-- Established and opened conn3 on the table/file GHM_V70.HST.
-- Performed SET EXCLUSIVE ON on conn3 and did the Pack on GHM_V70.HST. The Pack succeeded.

Now what?! Given the above, I'm at a loss to understand why the Pack on GHM_V70.HST fails when placed midstream in the program. At that point before performing SET EXCLUSIVE ON and doing the Pack on conn3 I close both the connection and recordset, set them to Nothing. I then reopen the connection and do the Pack -- which fails.

(I will try to close the threads in the other forums and reference this one.)

 

[Sware]

Mike, pardon my lack of "forum knowledge" -- how do I close the threads in the other forums and reference this forum?

 

[Mike Lewis]

how do I close the threads in the other forums and reference this forum?

Go to the first message in the thread that you want to close. Click "Inappropriate post? If so, Red Flag it!". In the popup window, explain briefly your reason for wanting to delete the thread. That's all you need to do.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[GriffMG]

Mike,

I think he is confused, he thinks he can get exclusive access to an already open table by asking for it - even if someone else has it open.

The thing is, he can't. If the table is open, any number of people can open it again - but none of them will get EXCLUSIVE access, the clue is in the name.

That's just a fact, not a bug, not a feature, just a thing - like the ten billion bicycles in Bejing.

If I have the file open exclusively, you can't even read it. If you have it open at all, I can open it exclusively.

Regards

Griff
Keep [Smile]ing

 

[GriffMG]

Sorry, Can't not can

Regards

Griff
Keep [Smile]ing

 

[Sware]

Thanks Mike for the "forum knowledge." However, the Pack issue is still unresolved. As I said above:

I'm at a loss to understand why the Pack on GHM_V70.HST fails when placed midstream in the program. At that point before performing SET EXCLUSIVE ON and doing the Pack on conn3 I close both the connection and recordset, set them to Nothing. I then reopen the connection and do the Pack -- which fails.

I have searched throughout the program and in every case where I open conn3 and its recorset I first close the recordset and set it to Nothing.

 

[Sware]

Thanks GriffMG for pursuing this. I certainly am confused but I don't think in the way you are suggesting. At this point the app is in single-user mode so the "someone else has it open" aspect doesn't apply. Plus, as I said above, before issuing the Set Exclusive and Pack, I Close and Set to Nothing both the recordset and connection. I then reopen the connection and do the Set Exclusive and Pack.

Perhaps the following sequence will shed some light on the midstream Pack attempt:

1) With rs3 open I identify a record to be deleted.
2) I issue rs3.Delete and then rs3.Update
3) I close rs3 and conn3 and set them to Nothing
4) I reopen conn3 and attempt the Set Exclusive and Pack
5) The Pack fails with "Cannot Open File Path\GHM_V70.HST"

The Pack succeeds when I place the code at program start. In that case, after opening two other connections (conn1 and conn2) and their associated recordsets, I just open conn3 and issue the Use in Select, Set Exclusive and Pack. Thus, there is no associated recordset and 1), 2) and 3) above are not in play.

Does the above suggest anything? I'm inexperienced at this but I'm wondering:

-- Could their be a timing situation as a result of the rs3.Delete and rs3.Update before attempting the Pack?

-- Could there be something connected with the conn3.Execute ("USE IN SELECT([GHM_V70.HST])") statement?

 

[Mike Lewis]

Sware,

So, to summarise, you are saying that you are sure the file is not open at the point that you do the pack. But you are still getting an error saying that the pack cannot open the file.

A couple of points ....

You say you are working in single-user mode, so the "someone else has it open" aspect doesn't apply. It's helpful to know that. But the problem would also arise if you have the file open by the same user (yourself) in another process or connection. I know that you understand that point, but it's worth emphasising.

Also, we're assuming that the file in question is a normal Foxpro table -- a DBF file, in other words. The reason I mention that is that the file has an extension HST, which is non-standard. That shouldn't matter, but, again, it's worth mentioning.

The "USE IN SELECT([GHM_V70.HST])" statement is the correct code to use to close the table (on the same connection, of course). Essentially, this code will close the table if it is open, otherwise it does nothing.

If you feel sure that the table is not open before you reach the Pack, but the Pack still fails to open it, you might like to try using a file monitoring utility to verify that. I use Filemon, from

http://www.sysinternals.com

(a free download), but there are others available as well.

Basically, you would reboot your computer, then launch the file monitor, telling it to monitor the file in question (if it has an associated index (CDX) or memo (FPT) file, they should be monitored as well). Then go a head and run your test. The utility should show you every attempt to open or access the file, including the process that does so.

If the file is being opened somewhere unexpectedly, the monitor should tell you. If it isn't, we'll have to look elsewhere for the sourse of the error.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk