File Access denied on mdb, log off needed

[The_Doc_Man]

You miss the point. It can be an MS Access app issue if the PREVIOUS activation of the app was faulty and left behind a locked file. The fact that MS Access is not running when you get the error message is not fully relevant. The file lock can be a hold-over from something else.

The error message is like when you discover dog poop on your lawn, but the dog that left it is long gone. (Yeah, I know,... a colorful metaphor - but it conveys the concept, I think.) You don't presume that the sky is raining dog poop. You assume the presence of a dog.

The reason I am taking this viewpoint is based on a simple question. What else are you running that would have locked THAT SPECIFIC FILE? What would touch that file BESIDES Access? This is not a trivial or flippant question. If you can't open an Access app file, WHAT OTHER program would have touched it in order to lock it?

That's why I'm not looking at faulty O/S issues. Trust me, if this were a flaw in the way Windows manages folder security, there would be articles all over the Internet about it, because world-wide, hundreds of thousands of users would be bitching on-line about a mess left behind by such an O/S bug. But that's not happening and I haven't even sniffed out one article on such a flaw.

So I'm still telling you to look to your application.

 

[HiTechCoach]

Doc, there is no hidden form as msaccess is not running at all. It has to be a file system issue as it does not allow anything to do with the .mdb (only the particular one & not all).

At times a log off don't work, i need to do a shut down.
Also a chkdsk on the drive holding the mdb shows no problem

May be it is all about a faulty OS, mainly xpwin.sp3. In win7 this may occur if there is no sp1, i hope that is the cause.

I doubt it is the OS.

If you are truly using Office Access 2002 then that is part of the issue.

Does the front end have any VBA code that opens a recordset?

If yes, then the issue you are having is normally cause by a bug in your code hat is causing the JET database engine to open a second instance that adds a lock to the back end. That is why you are not seeing a "ghost" instance of Access.

I know from experience this is difficult to track down. It requires lots of testing and debugging to isolate the code that is causing the issue.

Good luck.

 

[FuzMic]

Good guys, from your repeated careful explaination i do trust the experts on these
1 OS can't be the culprit.
2 No .ldb in the folder or no msaccess running does not rule out JET is still left with a 'ghost' instance of Access caused by my codes.

If my reading of what you say is correct, then i have to look at my codes particularly relating to recordset. Hope i finally got your advice right and the right step to track it down is my codes?

 

[HiTechCoach]

II think yu got what I was suggestion.

Looking at eh VBA code for recordsets is what I would do if it were my project.

I would suggest making a new blank front end and linking to a test copy of the back end. I would import a single object at a time that has VBA code hat deals with a recordset. Test to see if you can duplicate the issue. Import the next and test. Repeat until you find the VBA code that causes the issue.

 

[The_Doc_Man]

then i have to look at my codes particularly relating to recordset

I certainly would look there. In another set of threads we have an ongoing discussion about whether it is necessary to close things explicitly. While the verdict is out as to specific items, I still have a general rule that I suggest to everyone. There was a book that came out some years ago titled "Everything I Needed to Know I Learned in Kindergarten." The basic rules are

(1) If you opened it, close it when done.
(2) If you took it out, put it back when done.
(3) Share with others graciously.

So... if you open a recordset, be sure that you close it. If you create an application object, be sure that you tell IT to close whatever you told it to open and THEN be sure to tell it to quit. When setting up lock parameters, be sure to set them for either "Optimistic" or "No Locks."

Now, the easy part. To find all your recordset stuff, you can search your "entire project" for "OpenRecordset" (but don't worry about "match case"). That will find every one of your functions and subroutines where you explicitly opened a recordset. You can trace the lifetime of each recordset once you find where they are opened.

A simple test of whether you have a "dangler" is to put a breakpoint at the point where you are about to quit your application. Type the following command:

Debug.Print CurrentDB.Recordsets.Count

If that ain't zero, you have a dangler.

 

[FuzMic]

Thanks thanks guys next week I may have something to report as I am already setting up a test with a simple front.. Because I already found a file being denied access that have nothing to do with MS Access

Sent from my HTC_PN071 using Tapatalk

 

[The_Doc_Man]

Remember my earlier question: What ELSE would touch your file?

If you have an unrelated file lock in another folder, you have a lead on a non-trivial answer to that question because now you can see what else touches files.

By any chance does your system have a periodic backup schedule? Backups are a type of operation that will touch a lot of files.

 

[FuzMic]

Doc just found that Debug.Print CurrentDB.Recordsets.Count will not show an opened adodb.recordset, while the .ldb will, did i get you wrong?

 

[The_Doc_Man]

I'm surprised but it is good to know that at least the .LDB will show you something. That test worked for me, but I was using DAO recordsets when I discovered that trick. It appears that you are using ADO. The two formats are not the same so that might account for why one works and the other doesn't. You should know that by default (and also dependent on the order of appearance of the ADO and DAO references at the time of database creation), CurrentDB will probably be DAO.

 

[FuzMic]

Hi gurus

I did a test wherein the Front mdb does not use any DAO or ADODB recordset. It is a simple form with recordsource to a linked table from the BackEnd \\MainDat.

If the link table is already within the front .mdb, any changes from the form to the linked table will not cause the no-File-access to the BEnd .mdb after closure of the form and closure of the FEnd.

If there is no linked table yet and I create the link before opening the form using

Set tdf = CDb.CreateTableDef(“TblName”)
tdf.Connect = ";DATABASE=’DbName’"
tdf.SourceTableName = “TblName”
CDb.TableDefs.Append tdf

The form works, BUT … any changes to the Bend table from the form, the Bend mdb becomes no-File-access. There is nothing to tell me that it is so until I try to do some file function with the mdb ie no ldb or a phantom instance of msaccess

Do bear in mind that this phenomena only happen on 1 PC & always that have both the Front & Back Ends. However if another PC with another instance of a similar Fend accessing BEnd of this problem PC, there is no such problem.

Other Pcs with similar setup does not manifest this problem, that is why I came to the conclusion that it is a defective OS unless that is some other test I can do.

 

[The_Doc_Man]

I still maintain that this is less likely to be an O/S problem and FAR more likely to be a procedural error or oversight in your code. However, you are doing something that I don't recall ever doing on my own. I always used static (early) linking. You are doing late linking. However, I still offer my standard advice for something I see in your snippet.

I find this statement highly relevant:

If the link table is already within the front .mdb, any changes from the form to the linked table will not cause the no-File-access to the BEnd .mdb after closure of the form and closure of the FEnd.

You are creating an object called "tdf" as a table link. It is opened by your actions -more specifically, the .CreateTableDef operation. Once you do the .Append a couple of instructions later, you create your table "permanently" via the .TableDefs.Append operation. Until then, it is just an independent application object... an ACCESS application object (as opposed to Word or some other utility.) But do you ever release "tdf" by setting it to "Nothing" before your code exits?

You see, THIS is an explicit open operation that wasn't performed automatically by Access. It was manually performed by you - and thus falls under the scope of my usual advice about cleaning up after yourself: If you opened it, close it.

Access will automatically close any open table links when exiting, but "tdf" isn't an element in the TableDefs collection. It is a stand-alone link that was COPIED to the TableDefs collection. COPIED - not moved in - so "tdf" is still there after the .Append operation is finished.

For the networked case (BE is on another PC), the rules for networking cause the connection to drop when EITHER SIDE closes the connection socket. Therefore, your observation regarding PC #2 not having the problem can be explained by TCP/IP rules regarding SMB protocols, which are NOT "connectionless" like UDP, but have an explicit connection like TCP.

For the LOCAL case (BE and FE on same machine) there IS no socket. It is NOT a network-based connection that gets cleaned up by closing a network socket. So that makes "tdf" act as a local file link that certainly qualifies for our earlier discussion about "leaving something behind."

The reason that a logoff works is because the lock is something YOU owned. When you log off, Windows closes anything you had that was still open because it knows you are no longer there. Again, cleanup occurs automatically behind the scenes. A system shutdown has the same effect, an automatic (but usually silent) cleanup of whatever dynamic structures were still in memory at the time.

Since I've not played that much with late table binding, I can't swear on a stack of "Dean's Guide to Electronics" that I'm right - but it is a simple test to put in a statement to "Set tdf = Nothing" (after the .Append operation) and see if that fixes it.

 

[FuzMic]

Doc i replied some time ago that i need to digest the above & is still trying to fix looking at all angles including yours. Thanks for the time being for the care to teach.

Meanwhile what fox me is the "before or after" does not give any problem except in one pc. I will certainly look at the tdf point that you brought out to learn what i have caused.

 

[FuzMic]

My guides

I have exhausted finding the cause of 1 pc that require the log off. I have Doc's set tdf = nothing in my script immediately after appending the table. Moreover i ever delete all the table links when i exit the FE.

I am sure it is not some unclosed recordset or form.recordset, as it is quite easy to note these unclosed as we can see the ldb when they are opened. So i can safely exclude these from the cause leaving me with no way to go.

THANKS away.