How to Close a File

[Sware]

Newbie here with perhaps a fundamental problem that I can't seem to resolve. In one part of my application I open a connection to a specific file (free table) and a recordset associated with that connection -- and leave the connection and recordset open. In another part of the application I want to delete the FILE.

I've tried closing both the connection and recordset, and setting them to Nothing (as well as the recordset's Active Connection). I then establish a FileSystemObject and attempt to delete the file with:

HRfso.DeleteFile ("GHM_V70.HST").

I'm getting a "Permission Denied" error because the file is in use. (I know the file is "in use" because in testing if I don't open the connection and recordset the DeleteFile succeeds.)

How to resolve? Thanks.

 

[SBerthold]

What are you using to open it with (ADO or DAO, and which driver)?

Create a copy of the file first using a random name, and work off of this.
It may take a few seconds for the cache to flush when using an ADO or DAO connection, before you can delete the copy.

There are several other ways of handling this though, such as building the recordset under ADO and adding the data from the file using FSO, rather than having an ADO or DAO connection on it.

 

[Sware]

I'm using ADO with the Visual FoxPro Driver.

 

[Sware]

Okay, I was looking for what I hoped would be a simple answer but I guess that's not the case. The following is what I want to accomplish; perhaps it will trigger some thoughts on alternatives.

The file I want to delete is a history file that includes all transactions to the main database file (also a free table - a .DBF file). As a result it can grow very large over time and we encourage users to reset it periodically after doing an analysis based on its content. I attempt to do the reset by deleting the file and then using FileCopy to copy a skeleton file (empty with just the structure) to the name of the history file. As mentioned previously, I get Permission Denied on the fso.DeleteFile statement.

I have also tried to do the reset by deleting all the records in the recordset and then issuing a Pack command from the connection - e.g. conn.Execute ("PACK GHM_V70.HST")
The record deletions work but I get "File is in Use" on the Pack statement.

 

[BobRodes]

This code works:

Option Explicit
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub Form_Load()
Dim fso As FileSystemObject
Dim strCn As String

Set fso = New FileSystemObject
fso.CopyFile "C:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb", "C:\Program Files\Microsoft Visual Studio\VB98\nwind2.mdb"
strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\nwind2.mdb;User Id=admin;Password=;"
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open strCn
rs.Open "select * from customers", cn
rs.Close
cn.Close
fso.DeleteFile "C:\Program Files\Microsoft Visual Studio\VB98\nwind2.mdb"
End Sub

You can cut and paste it directly, assuming that you have nwind.mdb in the default location. If you comment out the line that closes the connection, you get the Permission Denied error. So, either you have a different connection (and maybe a cursor on top of that) open that you're not aware of, or there's something going on with the Jet provider for dBase. I would investigate the first possibility first.

HTH

Bob

 

[SBerthold]

>I've tried closing both the connection and recordset, and setting them to Nothing

You're not using any bound controls are you, or have any other unknown connections open which you may not be aware of, either on your client machine, or someone elses?

If not, then try to wait a few seconds after closing the connection and then see if you can manually delete the file.
Is the file local?

I have seen this happen (that even after waiting a few seconds, the file is still locked) because of the system's optimistic locking on files, and it keeping locks on the file even though all connections are closed.

Have you tried the FoxPro specific commands to delete it?

USE
DELETE FILE TheFile.dbf

 

[Sware]

So, either you have a different connection (and maybe a cursor on top of that) open that you're not aware of

As I mentioned in a previous post, I DO HAVE a connection and recordset open on the file I want to delete. What I can't find a way to do is close them so I don't get Permission Denied on the DeleteFile statement. As suggested, I've tried to introduce delays after closing both the connection and recordset (up to 15+ seconds) but I still get Permission Denied. Use of direct FoxPro statements didn't work either.

Perhaps I'm missing something but I don't see how copying the the file to a new name results in what I want to do -- which is to create a new file (empty file with just the structure) WITH THE SAME NAME.

 

[BobRodes]

Yes, I'm aware of what you said in your previous post. If your solution isn't working for you, maybe you need to investigate the idea that you have a connection and possibly a cursor open somewhere that you're not aware of. That's what I would do; feel free of course to do something else. Perhaps there really is some obscure reason that you're unable to close the recordset and connection, but I'd rule out the more obvious things first. Would you mind posting the code where you close the connection and recordset and they fail to close?

Bob

 

[Sware]

Set rs3.ActiveConnection= Nothing
rs3.Close
Set rs3=Nothing
conn3.Close
Set conn3=Nothing

 

[Sware]

Breakthrough! I avoid the Permission Denied error if I close ALL recordsets and their connections (not just the recordset and connection for the history table/file).

As backgound, here is (was) the structure of the application:

-- Three separate functional areas, two of which are main processing areas that work with up to four free tables (including the history table) depending on user options. The third is an occasional-use area for certain utility functions, all of which work exclusively with the history table. All three areas are selectable from a single "master menu" form.

-- I'm using ODBC connections: Provider=MSDASQL; Driver=(Microsoft Visual FoxPro Driver); SourceDB=" & AppPath & "\; Source Type=DBF;.....

-- All the free tables are DBF tables but only one has a .DBF extension in the file name. For compatibility/conversion reasons related to the old Clipper version of the application, the other three tables have "descriptive" extensions of .HST, .TRN and .ADD

-- When the user has specified all available options and selects either of the two main processing areas from the master menu form, all four tables were opened (and left open) with separate connections and recordsets.

As mentioned in a previous post, the Permission Denied error occurred when the history table reset task was selected from the third functional area after work had been done in either of the other two functional areas. The error did not occur if the reset task was done independently, i.e. without having first done work in either of the other two functional areas.

So, the solution was simple -- close all recordsets and connections upon exit from the two main processing functional areas (they are opened/reopened when these areas are selected/reselected from the master menu).

However, even though the problem is solved, I don't understand why other than the history table recordset and connection need to be closed if only the history table is being used in the history reset task. Interestingly, if I use VFPOLEDB connections that include a DataSource parameter that specifies a specific file name, the history reset succeeds without having to close the other recordsets and connections. There doesn't appear to be a way to use the DataSource parameter in ODBC connections.

 

[BobRodes]

<So, either you have a different connection (and maybe a cursor on top of that) open that you're not aware of, or there's something going on with the Jet provider for dBase.

Or maybe both?

I used to be expert in Paradox, and the file organization is the same as it is in Foxpro/dBase, namely different individual DOS files for each table, and using DOS directory structure to define a database. Access, of course, puts all the tables for a given database in one DOS file. I believe I remember that JET/ODBC locked all the files in the directory (folder) when you accessed a table, since that would be analogous to locking a single mdb file in "native JET" (meaning Access). So, you might want to investigate putting two different DBF files in two different folders, and see if you can have a cursor open in one of them while deleting the other.

 

[Sware]

I believe I remember that JET/ODBC locked all the files in the directory (folder) when you accessed a table.

BobRodes, thank you for your input throughout this thread. Apparently, what you remember about JET/ODBC is what's happening with the Visual FoxPro driver. Also, your previous input led me to experiment with closing all recordsets and connections, and that led to a solution.

Given that I have a solutiion that was easy to implement, I'm content to go with what I have without further investigation/experimentation. Thanks again.

 

[BobRodes]

Yes, I had more in mind a way to while away a rainy Sunday afternoon.