Access won't "let go" of a linked text file.

[Papa_Bear1]

I'm trying to process some large text files, and although I can import them, I would rather link to them pull the data I need and then unlink and process the next file. Turns out I need to do some clean-up of these files before working on them (removing junk up top - leaving nice delimited data), so I run code to translate each input file into a TempFile.txt, which I intend to connect/reconnect to over and over, each time with data from a new file.

The problem is - I can't delete that temp file once Access has its claws in it. (I get Run-time error '70': Permission denied.) Even after deleting the linked table. Apparently deleting a linked table does not actually/fully delete it.

Is there a way I can fully delete a linked table, allowing me to delete and replace the source file in Windows, and then relink?

 

[LarryE]

Have you been able to delete the linked table using the Linked Table Manager?

 

[Papa_Bear1]

Have you been able to delete the linked table using the Linked Table Manager?

I'm not seeing any problems with deleting the table. It is when I try to delete the file that the table was linked to that I run into the permission issue. Access still has its hooks in it.

I've also tried refreshing Access's Tabledefs collection, after deleting the table (since I just discovered that Access apparently does not refresh it for you) but alas, it STILL has its hooks in the linked file and is blocking me from deleting the file.

I've even tried tricking Access into thinking it is connected to a different file, perhaps allowing me to delete the original file, but no, you can't do that once the linkage has been made. (That makes sense actually.)

 

[DickyP]

Could you explain the sequence of what you are trying to do in more detail and especially what processing you do on the temp file.

 

[The_Doc_Man]

In order to LINK to the file, you need to OPEN the file. Unlinking doesn't close the file and doesn't actually delete the table. It just "loses track" of the table so that you can't query it. The problem is that the file was opened via a file handle. The file won't close again until Access exits.

There is a rather complex and lengthy procedure involved in scanning the file handles in your system... complex because you need to use a Windows API call to find that information. Essentially, once you set it up, you scan the handles until you find the one pointing to the named file that you used for linking. If you can identify the file handle that was used to open the file, you can use another API call to close it.

Get the list of all open file in vba

Hi guys, How to get the list of all open file in excel using VBA? Which are currently open. Open file can be any file, it can be image, dox, pdf, xlsx, ai etc.

www.mrexcel.com

 

[ebs17]

A link to a text file should not be a problem if you want to delete the text file. However, you must not have ACTIVE access to the link/file via DLookup / Recordset / bound form.

I use a link via query for temporary access. When the query is no longer used, the text file is free.

SELECT T.*
FROM [Text;DSN=NameOfSpezification;FMT=Delimited;HDR=yes;IMEX=2;CharacterSet=850;DATABASE=C:\temp\].[file01.txt] AS T

 

[Papa_Bear1]

In order to LINK to the file, you need to OPEN the file. Unlinking doesn't close the file and doesn't actually delete the table. It just "loses track" of the table so that you can't query it. The problem is that the file was opened via a file handle. The file won't close again until Access exits.

Very interesting Doc_Man - thanks very much.
I think I may have some code around that I found before that forces an Excel file closed (when the user forgot to close it.) I'm betting it looks a lot like what you provided. I vaguely recall it looked for file handles etc. Not sure if there is anything unique about text files vs. Excel files in this respect. But I will pull this thread. Thanks again!

 

[Papa_Bear1]

A link to a text file should not be a problem if you want to delete the text file. However, you must not have ACTIVE access to the link/file via DLookup / Recordset / bound form.

I use a link via query for temporary access. When the query is no longer used, the text file is free.

Hmmmm - interesting. I've only recently seen/used this connect-string-in-a-query thing. I will experiment with this - as it is perhaps somewhat simpler than all the file-handle stuff - assuming this approach works. Thanks!

 

[LarryE]

Very interesting Doc_Man - thanks very much.
I think I may have some code around that I found before that forces an Excel file closed (when the user forgot to close it.) I'm betting it looks a lot like what you provided. I vaguely recall it looked for file handles etc. Not sure if there is anything unique about text files vs. Excel files in this respect. But I will pull this thread. Thanks again!

Here is the code you were referring to close open EXCEL files:

Public Function IsProcessRunning(Process As String) As Boolean
    Dim objList As Object
    Set objList = GetObject("winmgmts:").ExecQuery("select * from win32_process where name='" & Process & "'")
    IsProcessRunning = objList.Count > 0
End Function
Public Function IsExcelRunning()
    If IsProcessRunning("Excel.exe") = True Then
        MsgBox "Running"
        'Close EXCEL window
        If Not GetObject(, "Excel.Application") Is Nothing Then
            GetObject(, "Excel.Application").Quit
        End If
  Else
        MsgBox "Not Running"
    End If
End Function

I don't know if it will work if you replace EXCEL.exe with your file name, but you can try.

 

[Papa_Bear1]

Hmmmm - interesting. I've only recently seen/used this connect-string-in-a-query thing. I will experiment with this - as it is perhaps somewhat simpler than all the file-handle stuff - assuming this approach works. Thanks!

Upon trying this approach of using a query - the system STILL won't allow me to delete that file. So annoying. I don't have the query open or anything. I just don't get it. I may have to resort to forcing it closed after all. (This all seems so unnecessary. I can't think of any reason for Access to hold so tightly to something that I've obviously no interest in any more. Oh well...)

 

[ebs17]

I may have to resort to forcing it closed after all.

Describe what you mean by open. Only something that is open needs to be closed.

 

[LarryE]

I may have to resort to forcing it closed after all

Wait a sec. Are you trying to delete a file that is open in a window without closing it first? No wonder you are getting that message. How is ACCESS supposed to know what you do, or do not, have interest in?

Also, VBA does have a Kill command that deletes a file that is on your drive but is closed.

VBA Kill Statement - Explained with Examples

The Kill statement in VBA is used to delete one or more files from a specified path. Useful when working with large amounts of files

analysistabs.com

So you may be able to:

1. Import your data from a file
2. Find and close the file window (assuming it is open) using the code in post #9
3. Use the Kill statement to delete the file (assuming you can capture the path and file name)

I'd be interested to know if you are able to do that.

 

[The_Doc_Man]

Guys, some of you need to re-read post #1 more carefully. Papa_Bear1 links to a file to import it but then later unlinks the file. He wants to then delete that file because he's done with it. This last step is where he has issues. (@Papa_Bear1 - if I have misrepresented you, PLEASE correct me.)

What Access does to link that file is it has to OPEN the file before it can link to it... because that is the way Access determines the location of the file data blocks on the disk. So Access links to the file, does its thing, ... voila, you have a new (linked) table.

Eventually Papa_Bear1 is done with this table. So he unlinks it. The problem is that the FILE is still open even if the table is no longer linked. Unlinking a file DOES NOT CLOSE THE FILE because that doesn't involve the file handle. Maybe it should... but it doesn't. This is probably one of the sources of the errors about "not enough file handles" or "insufficient resources." The file handles stay open even after you are done with them, and they accumulate.

Some of the Excel code in this thread scans for open workbooks and closes them. But workbooks are opened by a different mechanism than was used for linked tables.

ebs17 seems to think that using the [IN ...] clause to provide a temporary source DOESN'T leave the handle open. There, I don't know. Should be worth a try, though.

 

[ebs17]

I suspect that the text file is opened separately in addition to the database access and is therefore causing problems.

Alternatively, if the linked text file always has the same path and name, you don't need to delete anything. You can then replace an existing file with a newer version. You would only have to suspend database access for this measure.

 

[Pat Hartman]

@ebs17 (or anyone else) Do you have a link to the documentation for the FROM clause below? Thanks.

SELECT T.*
FROM [Text;DSN=NameOfSpezification;FMT=Delimited;HDR=yes;IMEX=2;CharacterSet=850;DATABASE=C:\temp\].[file01.txt] AS T

 

[ebs17]

Connection Strings can sometimes help.
I wrote down the notation used above from an example many years ago, and it has never caused me any problems.
I think it was also available in earlier versions of Connection Strings.

 

[The_Doc_Man]

You can then replace an existing file with a newer version.

No, not if the file to be replaced is still open. If the file could be replaced, it could have been outright deleted.

@Papa_Bear1, there is a relatively simple experiment to try that would answer this question definitively. I'm assuming from your description of your process that the temp file is local to your PC. Which, if true, gives us a measurable artifact. (If I'm wrong, if the file is NOT on your PC when you use it, don't bother with the rest of this.)

Run your process up to but not including the point at which you would try to delete the file. However, DEFINITELY go past the point where you unlinked the file. If you know how to debug code, put a breakpoint to stop execution. You want it stopped or paused or debug/breakpointed at the point where you WOULD delete that file if you could - but you haven't YET tried that deletion. At that point, minimize Access.

With Access open but minimized (and not running anything on a timer or having some other process underway), start the Windows Task Manager. You can right-click the task bar for a long menu, or use CTRL-SHIFT-ESC, or use CTRL-ALT-DEL and select Task Manager from the resultant menu. Select the Performance Tab. Near the bottom of that screen, click on "Open Performance Monitor." You'll get a new multi-tabbed display. Select the "Disk" tab. (Be patient, you're almost there...)

You will see three horizontal divider bars with titles: "Processes with Disk Activity", "Disk Activity", and "Storage" What you want will be under "Disk Activity" (the center section.) You can use the mouse to "grab" the bars to make that center section taller (more entries). Now look at the column headers to find "File." Clicking in the header toggles between ascending and descending order of display sorted by file specification. You can also scroll through the list because there is a right-side scroll bar.

Scroll through that list looking for the name of the file that you just temporarily linked to. IF you find it in that list, it is open even if its statistics say 0 reads or writes at the moment. IF you cannot find the file then the experiment failed and the problem isn't that the file is open. HOWEVER, if you DO find it, we can go one step further. Once you have found the file in the disk activity list, look to the next column to the left, which will be PID (process ID). Note the PID number. Now look in the section "Processes with Disk Activity" to find the process with that PID. If it says "Access" then that is proof that Access is still holding onto the file (by having it still open).

If this test proves that Access still has the file open even though you have unlinked it, then the problem is that the handle has to be separately closed as previously discussed. Shouldn't take too long to test that, so let us know what happened.

 

[Papa_Bear1]

No, not if the file to be replaced is still open. If the file could be replaced, it could have been outright deleted.

@Papa_Bear1, there is a relatively simple experiment to try that would answer this question definitively. I'm assuming from your description of your process that the temp file is local to your PC. Which, if true, gives us a measurable artifact. (If I'm wrong, if the file is NOT on your PC when you use it, don't bother with the rest of this.)

...

Sorry I was out of pocket for a while!
Doc_Man - you represented my issue exactly right.
I assumed that if I deleted a 'table' that is actually a linked text file, I would then be able to delete that text file.
I later noticed that this table actually still was listed in the database's list of tabledefs. So I thought - OK - I'll tell it to refresh the tabledefs - but still - no joy.
I tried the query approach - which I expected to work since there is not even a linked table in that case - but again - no joy. I really can't believe that running a query would result in Access keeping it 'hooks' in the referenced file - but it seems that once it does - it doesn't give it up easily!

I need to read your responses thoroughly (first thing tomorrow) before I can say much more! I've run out of steam today.
Thanks everyone!

 

[Isaac]

I've had a similar experience, in that you can't delete the text file while it is linked - active or not.
But once you delete the linked table object and close the db, it should be released

 

[Pat Hartman]

I think it was also available in earlier versions of Connection Strings.

I really want to know where in the documentation this syntax is described. Otherwise, figuring out what the allowable values are is trial and error and it is very easy to totally miss potentially useful options.

The documentation for Connection strings only showed "Text;" It didn't show any of the other options. Does that mean that this "feature" is undocumented and therefore unsupported?