How do I force a disconnection of linked tables from another DB

[KathyL]

Back on Nov. 12th I posted a thread on how to force all users out of a database. That was solved, but there is a new twist.



I've a new problem, how do I force a disconnection of another DB (database B) which has links into tables in my DB (database A) I want to compact upon closing?

Database A is scheduled by Windows scheduler to run several times a day. It runs, takes about 10 minutes to run action queries (and bloats), and then closes. Database B is open most of the 24 hour day, and has links into Database A tables (which is closed most of the time.)

[pkstormy]

If you want to compact/repair a specific mdb/accdb and user's have that mdb/accdb open (either directly or indirectly via a FrontEnd), I don't think you can really 'force' a disconnection on the backend tables (nor would you want to). This could cause data problems if you're disconnecting users from the backend tables or the backend db (and they happen to be updating data or have that table open.) A small little hiccup in the network connection could cause problems with any type of code to force users out of an mdb/accdb other than their normal exit and could also easily cause a corrupt *.ldb file. However I have "prompted" users to close a FE based upon certain circumstances. But I really wouldn't suggest automatically doing this.

You can use a utility to 'test' to see what users have that mdb/accdb open. This app: https://www.accessforums.net/code-re...lity-7546.html will allow you to see who's using the mdb/accdb. But I don't think that's what you're looking for (but maybe you can enhance upon this code).

Otherwise, there's no code I know of which will 'search' for any FrontEnds open which have linked tables to a specific backend. This type of code would be ideal but I've yet to see anything which does this (it would be nice code to have handy.)

"the final user close will not initiate compaction" - you're not automatically compacting on close are you? This isn't really a good idea for a multi-user environment. It can easily cause data corruption in different ways and the cons outweigh the pros on doing this.

My question would be why you need to compact upon close. Is the backend db often getting bloated? If so, you may need to look at why it's getting bloated. A mdb/accdb that bloats in size is usually the cause of other issues or due to importing routines/populating/deleting temp table data. I just wouldn't recommend automatically compacting on close in a multi-user environment. This seems like a work-around to another possible problem.

If you 'must' compact the mdb/accdb backend regularly, if possible, I'd set up a routine to do this during non-work hours at a scheduled time when I'm positive no one is in the db.

Have you considered SQL Server or another db server for the backend tables? This would be ideal.

[pkstormy]

In regards to automatically closing, one way I've set it up (which might be similar to your method) is to have code which looks at the value in a linked table (ie. a yes/no field in a table that is called something like: PromptUserToClose.) I'll set this value to yes when I want users to prompt users to close (and then I have code in the FE which periodically checks this value on a form's timer or other event). If the value is yes, the code will prompt a msgbox("The dbAdmin is requesting you close this mdb. Do you want to close now?",vbyesno) This works great if you have a form (such as a 'background' type of form to do the timer on) which is always open in the FE.

But I also utilize a vb script the users execute to clone/open the FE from a source mdb/mde (along with SQL Server as the backend) and I rarely need to ever prompt users to close the FE (most of the db's need to run 24x7). Using the vb script though, I can easily see all user's who have the FE open so I never need to hunt down who's in the FE. The script has really, really made my life 100 times easier.

If you're interested: https://www.accessforums.net/code-re...sier-7572.html

[KathyL]

I don't think you can really 'force' a disconnection on the backend tables (nor would you want to). This could cause data problems if you're disconnecting users from the backend tables or the backend db (and they happen to be updating data or have that table open.) A small little hiccup in the network connection could cause problems with any type of code to force users out of an mdb/accdb other than their normal exit and could also easily cause a corrupt *.ldb file.

I don’t see how you could corrupt the .ldb file. I use network admin tools all the time to disconnect users, it never once has corrupted the .ldb file. When you get all disconnected, you can easily delete this .ldb file with no harm. (so that I can get exclusive access)

"the final user close will not initiate compaction" - you're not automatically compacting on close are you? This isn't really a good idea for a multi-user environment. It can easily cause data corruption in different ways and the cons outweigh the pros on doing this.

Yes, I do automatically compact upon close. Access doesn’t compact if anyone is using the database, it will only compact of the last user out. Why wouldn’t this be a good idea? Why would Microsoft design anything to cause data corruption? That doesn’t make sense to say that. I’ve yet to experience a user’s/close and compaction to corrupt the DB.

My question would be why you need to compact upon close. Is the backend db often getting bloated? If so, you may need to look at why it's getting bloated. A mdb/accdb that bloats in size is usually the cause of other issues or due to importing routines/populating/deleting temp table data. I just wouldn't recommend automatically compacting on close in a multi-user environment. This seems like a work-around to another possible problem.”

Yes, the database is getting bloated. That was one of my earlier posts, how to stop a database from bloating. The only thing I have found, is to compact it… regularly.
Someone had suggested that I convert Access action queries to VB/SQL coded queries, and that this would stop the bloat. I converted the queries to SQL code, tested, and there was no change. The bloat still occurs.

If you 'must' compact the mdb/accdb backend regularly, if possible, I'd set up a routine to do this during non-work hours at a scheduled time when I'm positive no one is in the db.

It (Database B) is scheduled… to compact… on the last person quitting the database. I have designed an activity counter which will do a shutdown on any PC with about 60 minutes of inactivity… and that usually occurs at nighttime when the late shifts are inactive.

Have you considered SQL Server or another db server for the backend tables? This would be ideal.

Not an option.

However, I really, really appreciate all of your thoughtful comments.

[KathyL]

In regards to automatically closing, one way I've set it up (which might be similar to your method) is to have code which looks at the value in a linked table (ie. a yes/no field in a table that is called something like: PromptUserToClose.) I'll set this value to yes when I want users to prompt users to close (and then I have code in the FE which periodically checks this value on a form's timer or other event). If the value is yes, the code will prompt a msgbox("The dbAdmin is requesting you close this mdb. Do you want to close now?",vbyesno) This works great if you have a form (such as a 'background' type of form to do the timer on) which is always open in the FE.

But I also utilize a vb script the users execute to clone/open the FE from a source mdb/mde (along with SQL Server as the backend) and I rarely need to ever prompt users to close the FE (most of the db's need to run 24x7). Using the vb script though, I can easily see all user's who have the FE open so I never need to hunt down who's in the FE. The script has really, really made my life 100 times easier.

If you're interested: https://www.accessforums.net/code-re...sier-7572.html

I am doing this in Database B. I have two things: 1) and inactivity counter, where any client/PC will automatically log off at a specified length of inactivity, and 2) an administrative form where I can toggle a Yes/No field, and every client/PC checks that Yes/No field every 30 seconds, and a shutdown in 3 minutes will proceed if turned on.

So I can get users out... and the DB will close, but if another DB is linking to tables, it will close, but just not compact.

PLEASE NOTE that I modified my initial post description just a little bit.

[pkstormy]

KathyL,

Good call on the inactivity counter. This would be a must for the automating as you've done.

If you've never dealt with a corrupt *.ldb file, you will sooner or later. You may not recognize this error (since it's a vague error) but if you ever get a "Cannot open MSAccess file. It is locked by another user" whenever a user tries to open an mdb/accdb, this is an indication of a corrupt *.ldb file (contrary to those who believe it's might be due to a locked record.) I'm guessing you've probably already encountered this error at some point in the past.

A corrupt *.ldb file is when one user is in the mdb/accdb and that user loses their network connection or they've closed out of the mdb/accdb inproperly. I've never seen a 'perfect' network that doesn't experience some kind of hiccup now and then (or freezes up on a user) and they are in an MSAccess file. Or a user closes out of the mdb/accdb inproperly (ie. they turn off the computer while they are in it.)

*.ldb files are easily corrupted with any hiccups or inproper closing of an mdb/accdb. But a corrupt *.ldb file only prevents other users from opening the same mdb/accdb and doesn't affect data. It's the automating the compacting of data combined with an *.ldb problem which would be concerning.

[pkstormy]

The reason it isn't good to compact on close is when you have multiple users in an mdb/accdb, as you've seen, MSAccess isn't the best at detecting when the last user closes out of the mdb/accdb (which again, is kept track of in the corresponding *.ldb file). If you write code to try to force a compact and there is a hanging *.ldb file, this could be problematic. Keep in mind that when MSAccess closes an mdb/accdb, it also tries to 'clean up' the *.ldb file refreshing who's in/out of the mdb/accdb as well as closing recordsets. I would worry about any coding which bypasses this process or tries to automatically 'disconnect' any linked tables.

Still though, some developers will link the tables in their startup form or design their FE's so that it only opens recordsets in code versus using linked tables. Designing data forms in an 'unbound' fashion (ie. there is no recordsource for the form) is ideal in this scenario as you then don't have to deal with user's who are 'constantly' connected to the tables via the forms. 'Unbound' forms are time-consuming though to design since you have to write code/functions which open recordsets and update all the fields. But you do end up with an ideal situation when designing unbound forms since you then break that tie with user's constantly connected to the table(s) via a form in the FE.

One of the reasons MSAccess is not the best at multi-users in the same mdb/accdb is because whenever a user opens the mdb/accdb, MSAccess again, records who the users are in the *.ldb file. It will try to create the *.ldb file if it doesn't exist or modify it if it does and any routine which interrupts this process (such as in the middle of compacting the backend tables) would have undesirable effects.

You may have seen that Microsoft recommends no more than 3 users in the same mdb/accdb. This is because the *.ldb file can easily get confused on who has actually closed the mdb/accdb (hence, if a user loses their network connection while in the mdb/accdb, you end up with a 'hanging' *.ldb file and the error "...locked by another user" when another user tries to open the mdb/accdb.) This has been an issue that's plagued MSAccess since the first version and MS has never really produced a viable solution in all their MSAccess versions. It's similar to having 4 or 5 users trying to open/edit the same Word or Excel file. There's too much room for problems with multiple users in the same file regardless of any precaution type coding.

Hence, I personally would worry about any routines such as the compact on close which would try to run depending on the *.ldb file and who has/has not closed out of the mdb/accdb. Even with an inactivity counter as you've wisely designed, I'd still have concerns.

If you have another user who opens the FE while it's compacting (or the timing happens to be at the wrong moment), it could have potential to cause problems (not just with the *.ldb file but also with the data tables themselves.) Feasibly, MSAccess wouldn't let the user open the FE while the BE is compacting but to me, this is just to risky of a process to warrant doing something that should only really need to be done periodically. I've seen many MSAccess errors that theoretically should never have happened (along with times that I would've assumed I'd have a problem but didn't). Some were due to timing of who's opening/closing the mdb/accdb at 'just' the wrong moment and others have been due to network problems. Network hiccups are the plague of any MSAccess file and again, I've never seen a 'perfect' network that doesn't experience some type of hiccup now and then (even at the city/police dept and Hospital where you'd think the networks would be completely problem-free.) One little hiccup though at just the wrong time and while you're disconnecting users/compacting the db could be the sign of some long hours fixing db issues.)

But this doesn't help you in your question in your original post. Sorry - I don't have the answer to this question or know if it's even possible. In all my years of coding (since the beginning of MSAccess), I guess I've never really found the need or a good reason to risk compacting on close.

Note though, running action (I'm assuming update or append queries) shouldn't really cause that much bloating (unless you are updating/appending a LOT of data/memo fields/etc...) If the db is bloating extremely each time these queries are run, you may want to look at the queries themselves or look at the table structure to see why such bloating occurs. I can understand bloating to some extent upon running append queries, but you shouldn't see an extreme amount of bloating unless again, you're appending a lot of records. I have some mdb's which have about 50,000 records and run update/append queries on 5,000-10,000 records weekly and I only need to compact the mdb once a month or so.

One thing you could try though, is to write some code which searches each folder that has an mdb/accdb FE with the linked tables and see if a corresponding *.ldb file exists. If it does, you would then know a user is in the mdb/accdb (or the *.ldb is corrupt). To me, this would seem like a better approach versus trying to disconnect a linked table. There's probably an easy way to disconnect linked tables in a FE but I'd still be worried about doing it.

[KathyL]

You may have seen that Microsoft recommends no more than 3 users in the same mdb/accdb.

You're joking, yes? At multiple clients, I've easily seen 20 or more. My current largest client has 25 or more. I've associate programmers, who like me, think Access is powerful enough to handle this.

I have some mdb's which have about 50,000 records and run update/append queries on 5,000-10,000 records weekly and I only need to compact the mdb once a month or so.

.

Just one table in my client's DB has nearly 500,000 records. Probably 60 active tables. I've a process which has to purge 3 tables, and then appends 10,000 records 3 times a day to each table... it's adding 40MB bloat each time.

[pkstormy]

I'm not sure if this helps, but to search for certain file types within a folder, you could use coding similar to this:

Directory = vDirectory '(folder to search through)
FileSpec = vFileSpec '(ie. *.ldb file)

With Application.FileSearch

.NewSearch
.LookIn = Directory
.FileName = FileSpec

If .Execute() > 0 Then

'do something here if found...
'or to loop through the files found...
For i = 1 To .FoundFiles.Count
'do something here with found file...
Next i

End If

End With

[KathyL]

I've a new problem, how do I force a disconnection of another DB (database B) which has links into tables in my DB (database A) I want to compact upon closing?

Latest Update:
I have discovered that I've pretty much been successful on resolving my problem. Part of my problem was that I was doing faulty testing. In my first test of having Database B open with links into Database A, I had an actual form open with the table open. When I realized a day later what I had been doing, I re-did my testing, and left the form closed. So the database did have linked tables defined, just no form had them open.

In this last test senerio, database A closed and compacted upon closure.

So far results are looking good and I'll make a note that this post has been solved.

[pkstormy]

You may want to simulate this scenario in your testing:

1. Have a user open the mdb.
2. Then to simulate a 'hanging' *.ldb file, shutdown that user's computer while they are in the mdb (simulating a scenario where the network hiccups or loses connection.) (ideally you'd do this by having someone else pick a user so you don't know which user it happened to.)

Not sure if you have a lot of users but if so, testing this might help if it ever comes up as a problem and you need to get users back up and running quickly.

A good network setup might also close the mdb nicely versus leaving a hanging *.ldb file but trust me that it will happen at some point.

[pkstormy]

KathyL,

I found this code you may be interested in:

Function RepairMDB(aName As String) As Integer
Dim ws As Workspace
Dim db As Database
Dim nname As String
nname = Left(aName, Len(aName) - 3) & "NEW"
DBEngine.RepairDatabase aName
DBEngine.CompactDatabase aName, nname
Kill aName
Name nname As aName
RepairMDB = True
End Function

You may be able to use it in some way.

[pkstormy]

Another option to see if the *.ldb file exists (and then do your compact)

Sub testIt()
If Chk("C:\myfolder\myMdbApp.ldb") Then
MsgBox "File exists.", vbOKOnly, "TITLE"
Else
MsgBox "File does NOT exist.", vbCritical, "TITLE"
End If
End Sub

Function Chk(myFile As String) As Boolean
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Chk = fso.FileExists(myFile)
Set fso = Nothing
End Function