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

[Papa_Bear1]

My emphasis added...

It doesn't matter whether you unlink or not. If the DB is closed, so are its handles. That file IS released.

I'm pretty sure closing the DB frees it up - but I'm trying to run code - creating/connecting/deleting/recreating/reconnecting, etc. So closing the DB is just not really an option for the goal of automation - or at least - shouldn't be necessary - ha.

 

[Isaac]

I'm pretty sure closing the DB frees it up - but I'm trying to run code - creating/connecting/deleting/recreating/reconnecting, etc. So closing the DB is just not really an option for the goal of automation - or at least - shouldn't be necessary - ha.

You may want to switch to TransferText. The various gotchas for linking text files makes me not really use that method at all , FWIW.
TransferText gives you a lot more control over important things like datatypes.

 

[Papa_Bear1]

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.

Indeed! It is the kill command that is failing.
I'm trying to delete the file (using the kill command), but can't due to permissions.
Interestingly - even if I try to delete it in Windows Explorer - I still can't - and it even confirms that Access is what has its hooks in it - so I think that answers the one question - which is - "what actually has its hooks in it?". It is indeed Access.

I thought maybe using the query method to get at the data, and actually deleting the query and reconstructing it might solve this - but that didn't work either. Good grief.

 

[Isaac]

Is it just a matter of a pause?
Close the db some seconds before trying to kill the file, and close any queries or recordsources bound to anything relating to it.

if you want to kill the file after closing the db, just have the db's last action be executing a VBS script that kills it 5 seconds after db close, I used to do this with linked excel's, before i decided linking was an endless pitfall and stopped doing it

 

[Papa_Bear1]

You may want to switch to TransferText. The various gotchas for linking text files makes me not really use that method at all , FWIW.
TransferText gives you a lot more control over important things like datatypes.

So, TransferText is exactly one of the ways I was trying to do this --- using acLinkDelim. It is the linking that I want, and which establishes a lock on the file I can't seem to unlock. As I noted earlier, I can do an acImportDelim just fine - but I really don't want to import MB of data, just to turn right around and drop it. I'd rather connect, pull what I want and move on.

 

[Papa_Bear1]

Is it just a matter of a pause?
Close the db some seconds before trying to kill the file, and close any queries or recordsources bound to anything relating to it.

if you want to kill the file after closing the db, just have the db's last action be executing a VBS script that kills it 5 seconds after db close, I used to do this with linked excel's, before i decided linking was an endless pitfall and stopped doing it

I'm pretty sure it is not a matter of timing - and I need all of this to happen while looping through code. If I close the DB, I can indeed delete the file then, because Access if finally/truly done with it. Unfortunately, as long as my code is running - it just won't let go.

I'm back to trying to see if I get all that [voluminous] code to close a file to work. It seems it came from Excel, and it has a segment that won't compile in Access VBA - as it makes use of an Application.Match function which is apparently only in Excel. So I need to figure out what it does and see if I can mimic it. It's a bit messy - passing arrays around and no inline documentation whatsoever... Good grief. All because I want to delete a file. Ha!

 

[Isaac]

So, TransferText is exactly one of the ways I was trying to do this --- using acLinkDelim. It is the linking that I want, and which establishes a lock on the file I can't seem to unlock. As I noted earlier, I can do an acImportDelim just fine - but I really don't want to import MB of data, just to turn right around and drop it. I'd rather connect, pull what I want and move on.

Sorry, I should have clarified I meant with the import argument, not linking.

What's the tangible benefit of linking over importing, given the fact that nobody can edit the text file while linked anyway? If in any sort of modicum of use

 

[Papa_Bear1]

Just wanted to add something.
I simply could not get anything to work while looping through input files. So, I gave up on trying to reuse the same temporary file, and instead created a new/separate temporary file for each input file. Seems silly - but for some weird reason, if I do that and wait until much later to delete all of those temporary files - it lets me. Really weird.

 

[Papa_Bear1]

Sorry, I should have clarified I meant with the import argument, not linking.

What's the tangible benefit of linking over importing, given the fact that nobody can edit the text file while linked anyway? If in any sort of modicum of use

Mainly - the only reason I'm trying to see if there is a way to avoid importing - is that Access bloats with every import/delete. I can certainly check the box to "Compact On Close", but that seems more bull-in-china-shop than preventing the bloat to begin with. Ha!

 

[Isaac]

If you only imported them instead of linking to them, you'd have no deleting trouble

 

[Isaac]

Mainly - the only reason I'm trying to see if there is a way to avoid importing - is that Access bloats with every import/delete. I can certainly check the box to "Compact On Close", but that seems more bull-in-china-shop than preventing the bloat to begin with. Ha!

I see. Have you considered a deployment method which gives your users a fresh copy of the front end DB every time they open it?
This is what I switched to (mostly) in later years. It solves a lot of problems all at once, that users create with their FE db. Including bloat at least to a large extent.

<20 lines of code in a VBScript file will do the trick.

 

[ebs17]

As already described above, I experience something different in my environments than @The_Doc_Man describes, I don't have any unwanted locks on linked text files.

The devil is in the details. I pointed out above that you describe EXACTLY what you are doing. Such a description could be presented as a complete code sequence from linking to deleting the text file, possibly enriched by an example database with the target table and one or two text files.

 

[The_Doc_Man]

So - I don't see "Open Performance Monitor" but I do see "Open Resource Monitor" but our IT dept. has us locked down such that I can't even use it.

Whoops. My bad. You had the right name, and I am not surprised that IT has it locked down.