Loop to detect when .ldb closed within a time limit (1 Viewer)

wiklendt

i recommend chocolate
Local time
Today, 20:05
Joined
Mar 10, 2008
Messages
1,746
Hi all, long time no see.

I have cobbled together a Side-end (SE) which is triggered by Front-end (FE) to Compact/Repair (C/R) the Back-end (BE) in an automated way when users are not connected.

Basically, I have the SE "wait" for the FE to do its own C/R on close, and I've chosen a very safe 10sec, before checking for BE .ldb. What I would like to do is to better utilise that 10sec by checking for the .ldb at the same time but only up to 10s - rather than check for .ldb after the 10s has passed.

I figure to put this in a loop of some sort and to exit the loop if one of two criteria are met:

- the .ldb file is not present. (in which case will then continue to then C/R BE)
- the maximum of 10s is reached. (in which case will then quit SE).

existing code that I have is the two separately and was thinking i'd need to somehow put them together in a clever way. unfortunately i'm not very clever myself! Assistance/advice appreciated.

Wait code:
Code:
Public Function Pause(NumberOfSeconds As Variant)
[COLOR="SeaGreen"]'---------------------------------------------------------------------------------------
' Procedure : Pause
' Author    : DACrosby (?)
' Date      : 07/08/2017
' Purpose   : Call this function to pause the application
' From      : https://stackoverflow.com/questions/6960434/timing-delays-in-vba
'---------------------------------------------------------------------------------------
'
[/COLOR]On Error GoTo Error_GoTo

    Dim PauseTime As Variant
    Dim Start As Variant
    Dim Elapsed As Variant

    PauseTime = NumberOfSeconds
    Start = Timer
    Elapsed = 0
    Do While Timer < Start + PauseTime
        Elapsed = Elapsed + 1
        If Timer = 0 Then
            ' Crossing midnight
            PauseTime = PauseTime - Elapsed
            Start = 0
            Elapsed = 0
        End If
        DoEvents
    Loop

Exit_GoTo:
    On Error GoTo 0
    Exit Function
Error_GoTo:
    Debug.Print Err.Number, Err.Description, Erl
    GoTo Exit_GoTo
End Function

.ldb check code (Pause function usage highlighted for clarity):
Code:
Function Compact_DB()
[COLOR="seagreen"]'---------------------------------------------------------------------------------------
' Module    : modCRdatabase
' Author    : original written by ? from "CompactRepair.mdb"
' Modified  : Agnieszka Wiklendt
' Date      : 07/08/2017
' Called by : The "on unload" event of login form in FE calls this file only after
'             ensuring that Compact/Repair (C/R) is required (an interval determined by
'             a text file writen on the last C/R event).
' Purpose   : This code checks that the BE is not locked with a .ldb file, then:
'             Copies a system file to prevent new users to open/lock the BE
'             C/Rs the BE to a temporary file
'             Deletes original BE and renames temp file to real file name for BE
'             Writes new date to 'system text file' so FE can determine when to next C/R
'---------------------------------------------------------------------------------------
[/COLOR]On Error GoTo Err_Compact_DB

    Dim strMasterPath As String
    Dim strDeveloperTestPath As String
    Dim strLiveServerPath As String
    
    Dim strBEpath As String
    Dim strBEfile As String
    Dim strLDBfile As String
    Dim strLOCKfile As String
        
    Dim objFileSystem As Object                                     [COLOR="SeaGreen"]'Initiates FileSystem Object
[/COLOR]
    Set objFileSystem = CreateObject("Scripting.FileSystemObject")  [COLOR="seagreen"]'Initiates FileSystem Object
[/COLOR]    
    [COLOR="seagreen"]'SET PATHs[/COLOR]
    strDeveloperTestPath = "C:\Users\Agnieszka\Documents\Computers\Programming\Database Work\Projects\Molecular\Ordering DB (Terry)\"
    strLiveServerPath = "\\wm-icpmr\Data2\SHARED\Cidmls\Ordering\Data\"
    
    strBEfile = "CIDMLS_Ordering_BE.mdb"
    strLDBfile = "CIDMLS_Ordering_BE.ldb"
    strLOCKfile = "SysMain.bak" [COLOR="seagreen"]'just a text file that access can read/write (named so to scare colleagues to leave it alone).
    
    'comment out appropriate line.
'    strBEpath = strLiveServerPath
[/COLOR]    strBEpath = strDeveloperTestPath
    
  [COLOR="seagreen"]  'check if .ldb file for BE exists[/COLOR]
    If objFileSystem.FileExists(strBEpath & strLDBfile) Then 'it does
        [COLOR="seagreen"]'so exit[/COLOR]
        DoCmd.Quit
    Else
        [COLOR="seagreen"]'it does not
        'then copy lock system file to working folder
[/COLOR]        DoCmd.Hourglass True
       [COLOR="Red"] Pause (10)[/COLOR] [COLOR="seagreen"]'hate wasting time, but not sure how to make it do both count and check.
[/COLOR]        FileCopy strBEpath & "SystemFolder\" & strLOCKfile, strBEpath & strLOCKfile
    End If
    
    [COLOR="seagreen"]'COMPACT CHOSEN DATABASE, TO TEMPORARY DATABASE NAME[/COLOR]
    DBEngine.CompactDatabase strBEpath & strBEfile, strBEpath & "CR_" & strBEfile
    
    [COLOR="seagreen"]'DELETE OLD DATABASE[/COLOR]
    Kill strBEpath & strBEfile
    
    [COLOR="seagreen"]'RENAME TEMPORARY DATABASE TO ORIGINAL NAME
[/COLOR]    Name strBEpath & "CR_" & strBEfile As strBEpath & strBEfile
    
    [COLOR="seagreen"]'Write today's date in the system C/R date file
[/COLOR]    Dim strCRdate As Date
    
    strCRdate = Date
    
    Open strBEpath & "SystemFolder\CRdate.bak" For Output As #1
    Print #1, strCRdate
    Close #1
    
    [COLOR="seagreen"]'DELETE the copy of the system lock file[/COLOR]
    Kill strBEpath & strLOCKfile
    
    [COLOR="seagreen"]'...aaaaaand quit.[/COLOR]
    DoCmd.Hourglass False
    DoCmd.Quit

Exit_Compact_DB:
    Exit Function

Err_Compact_DB:
    DoCmd.Hourglass False
    strErrorNum = Str(Err.Number)
    strErrorDsc = Err.Description
    Msg = "Error # " & strErrorNum & Chr(13) & " (" & strErrorDsc & ")" & _
    Chr(13) & " in: " & cstrModule & " Compact_DB"
    MsgBox Msg, vbMsgBoxHelpButton, "Side-end: Error"
    Resume Exit_Compact_DB

End Function
 

isladogs

MVP / VIP
Local time
Today, 09:05
Joined
Jan 14, 2017
Messages
18,186
Hi

I've not studied the code but this seems to be over complicated

You can compact the BE using code from the FE.
The SE seems to me to be unnecessary

If you only want to compact when no user users are logged on, you can check for other users using VBA.

Alternatively consider running the Compact using a scheduled task at a quiet time e.g. midnight
 
Last edited:

wiklendt

i recommend chocolate
Local time
Today, 20:05
Joined
Mar 10, 2008
Messages
1,746
Thanks for your quick reply ridders. I have thought about other approches and I was under the impression that a compact is risky for BE data integrity when there's an .ldb on the BE. Does the BE even compact when .ldb present?

Also, BE.ldb from FE will always be present because the mere linking of FE to BE creates this .ldb - unless you have unlinking code and i believe that is as complicated, if not more (?), than my approach.

How do you do it, if i may ask?

Sent from my GT-I9505 using Tapatalk
 

wiklendt

i recommend chocolate
Local time
Today, 20:05
Joined
Mar 10, 2008
Messages
1,746
Scheduled task is tricky as the db is in a networked environment where our IT dept has made it impossible to use anything remotely useful.

Sent from my GT-I9505 using Tapatalk
 

moke123

AWF VIP
Local time
Today, 05:05
Joined
Jan 11, 2013
Messages
3,852
Also, BE.ldb from FE will always be present because the mere linking of FE to BE creates this .ldb - unless you have unlinking code and i believe that is as complicated,
If you dont have any bound forms or any other type of connection to the backend there will not be a lockfile for the backend.
 

isladogs

MVP / VIP
Local time
Today, 09:05
Joined
Jan 14, 2017
Messages
18,186
Scheduled task is tricky as the db is in a networked environment where our IT dept has made it impossible to use anything remotely useful.

How unusual! :rolleyes:

I was under the impression that a compact is risky for BE data integrity when there's an .ldb on the BE. Does the BE even compact when .ldb present?

Also, BE.ldb from FE will always be present because the mere linking of FE to BE creates this .ldb - unless you have unlinking code and i believe that is as complicated, if not more (?), than my approach.

How do you do it, if i may ask?

Compacting?
For the past 10 years, all my BE files have been SQL server datafiles so I haven't tested this with Access for a while. I run code to backup the SQL BE from Access but I always 'compact' (shrink) it from SQL Server.

However I have old code to compact Access BE files from the FE whilst connected to the Access BE and I don't recall it being an issue.

You mentioned .ldb files - the code used to compact Access db dates back to around 2006 when we still used Access 2003 mdb files

Other users?
Assuming all users login to the FE, I assume you keep a log of who is currently logged in.
You could run code just before the FE is closed to detect if any other users are logged in.
If there are other users, close without compacting the BE
If not, compact the BE

Better still check the size of the BE & only compact if its larger than a specified size

Corruption?
Any compacting routine runs a small risk of causing data corruption so I always recommend making a backup before compacting

If its any use to you, the attached file contains several routines used to backup/compact datafiles (as well as lots of other routines from the same module.
The routines include:
- CopyCurrentDatabase (Access FE)
- CompactDB (Access FE)
- CompactDatafile (Access BE)
- CreateSQLBackup (SQL BE to network)
- CreateLocalSQLBackup (SQL BE to local drive)
and more ...

They will all need adapting to your systems but you are welcome to any that may be of use.
Remove the .txt extension and import into Access as a standard module

Unlinking code
Yes I do have that as well but not in the attached file.
I don't think you need it for this purpose
 

Attachments

  • modDBBackup.bas.txt
    32.6 KB · Views: 187

wiklendt

i recommend chocolate
Local time
Today, 20:05
Joined
Mar 10, 2008
Messages
1,746
If you dont have any bound forms or any other type of connection to the backend there will not be a lockfile for the backend.
Hi moke123, yes i have connections from FE to BE. unsure how I'd access the BE data otherwise? To ensure no connections to BE for compacting i have created the SE.

Is that a second vote to disconnect FE/BE and just do conpact/repair from FE?

Sent from my GT-I9505 using Tapatalk
 

wiklendt

i recommend chocolate
Local time
Today, 20:05
Joined
Mar 10, 2008
Messages
1,746
Compacting?
For the past 10 years, all my BE files have been SQL server datafiles so I haven't tested this with Access for a while. I run code to backup the SQL BE from Access but I always 'compact' (shrink) it from SQL Server.

Id love to know how to convert my Access.mdb databases to SQL server but I lack both knowledge and time (maybe also motivation?) at the moment.

You mentioned .ldb files - the code used to compact Access db dates back to around 2006 when we still used Access 2003 mdb files

Yes, my database is mdb - the ".ldb" is the record lock file that is created when FE.mdb links with BE.mdb

Other users?
Assuming all users login to the FE, I assume you keep a log of who is currently logged in.
You could run code just before the FE is closed to detect if any other users are logged in.
If there are other users, close without compacting the BE
If not, compact the BE
LOL, you assume I know what i'm doing, which I don't necessarily! This is a good idea, i'll look into this as it could be handy for a number of purposes.

Better still check the size of the BE & only compact if its larger than a specified size

Good idea, I can look into this also.
[edit: ah, excellent, it's included in your module code, thanks!]

Corruption?
Any compacting routine runs a small risk of causing data corruption so I always recommend making a backup before compacting

This is another thing i'd like to try out - to copy into a zip backup. My motivation for this is small as our IT dept make daily backups of server files so I theoretically have access to backups from them - I do understand that it would be much quicker and easier for me to access my own backups, though.

If its any use to you, the attached file contains several routines used to backup/compact datafiles (as well as lots of other routines from the same module.
The routines include:
- CopyCurrentDatabase (Access FE)
- CompactDB (Access FE)
- CompactDatafile (Access BE)
- CreateSQLBackup (SQL BE to network)
- CreateLocalSQLBackup (SQL BE to local drive)
and more ...

They will all need adapting to your systems but you are welcome to any that may be of use.
Remove the .txt extension and import into Access as a standard module
Thanks! I will peruse it with interest!
 

isladogs

MVP / VIP
Local time
Today, 09:05
Joined
Jan 14, 2017
Messages
18,186
You may find this old post by David Crake useful: View users logged into database

As written it only works for MDB files but that should be OK for you.
However it could easily be adapted for ACCDB files as well

EDIT: I'm hoping to adapt it later today as another user has just requested exactly that!

FURTHER EDIT:
I've tested the MDB version & it works for external DBs on the local PC
However it doesn't seem to work on a DB opened on another PC on the network

I'd appreciate it if you could test it for me and report back
 
Last edited:

wiklendt

i recommend chocolate
Local time
Today, 20:05
Joined
Mar 10, 2008
Messages
1,746
You may find this old post by David Crake useful: View users logged into database

As written it only works for MDB files but that should be OK for you.
However it could easily be adapted for ACCDB files as well

EDIT: I'm hoping to adapt it later today as another user has just requested exactly that!

FURTHER EDIT:
I've tested the MDB version & it works for external DBs on the local PC
However it doesn't seem to work on a DB opened on another PC on the network

I'd appreciate it if you could test it for me and report back

Hi, yes I have seen DCrake's db and had actually modelled some of my code on it. I think I will have to implement more of it in my database and try to run everything from the FE - when I last used it as standalone (I don't know how old the version I have is - have had it a while) at work, the db failed to run, not sure why. I will have to try the new iteration!

time to ditch the SE, though: i'm not as close to a solution as I had originally thought: my opening of the SE and closing of the FE is not as smooth as I had imagined as i'd been testing the two codes separately. when I run it all together it leaves an ugly empty Access Application window - when I try to change the code to prevent that empty window it closes the SE prematurely as well.

as I understand, BE file corruption in MDB is only really a risk if there is data being written to the tables at the time that another process (such as C/R) is invoked.... so if I test connections to the BE and there is only one (the one testing the connections) then C/R should only have a 'normal' amount of risk?
 

static

Registered User.
Local time
Today, 09:05
Joined
Nov 2, 2015
Messages
823
A C/R wont happen if somebody is using it.

Don't worry about compacting too much it won't make that much difference.

You don't need to know who is using the database. Try to delete the lock file. If you can, nobody is using it so you can do as you like with the db.
 

isladogs

MVP / VIP
Local time
Today, 09:05
Joined
Jan 14, 2017
Messages
18,186
@wiklendt
I've now completed updating David Crake's utility to check users logged into a selected database

Changes made include:
1. It now works with ACCDB or MDB files.
2. Fixed issues monitoring databases on remote computers (the original version failed for DBs on networked PCs
3. List of users is updated automatically every 10 seconds
4. Improvements to the Lock Database feature

For more info & a link to the file, see this thread:
https://www.access-programmers.co.uk/forums/showpost.php?p=1542365&postcount=5

As a possible future update, I may add code to create a balloon tooltip alert on other users computers when the database is locked by system admins

NOTE:
I've also just discovered another version of the same idea by datAdrenaline in sample databases: https://access-programmers.co.uk/for...d.php?t=223434

Slightly different features but the same idea
Use whichever you prefer... :cool:

@static
Don't worry about compacting too much it won't make that much difference.

I disagree...especially where we're talking about multi-user BE databases
That very much depends on what actions have been taken with the db.
As a rule of thumb, if the datafile has doubled in size its worth compacting it.
However, it's always a good idea to make a backup first

You don't need to know who is using the database. Try to delete the lock file. If you can, nobody is using it so you can do as you like with the db.

True in terms of compacting ...
BUT its useful to know for other reasons
e.g. sysadmin wants to close the db for maintenance.
Knowing how many are logged on & on which computer makes that much easier
 

static

Registered User.
Local time
Today, 09:05
Joined
Nov 2, 2015
Messages
823
Obviously if the file is large and you have a slow network it will make some difference because there is more file to copy over the network, but that's it.

The file will have doubled in size for a reason. You'd be better off working out why and fixing that issue. You've added a lot of data to a temp table and deleted it? Put your temp tables in a temp db. Temp tables shouldn't be in the back end anyway...

The only way to know who is using an Access db AFAIK is through the lock file and even that isn't very accurate.
 

isladogs

MVP / VIP
Local time
Today, 09:05
Joined
Jan 14, 2017
Messages
18,186
The file will have doubled in size for a reason.
Over a period of time in a multi-user environment, it will significantly increase.
The OP may well want to compact each day though I wouldn't normally do so.
My preferred method is to compact when the file size exceeds a specified value

The only way to know who is using an Access db AFAIK is through the lock file and even that isn't very accurate.

The lock file will accurately tell you which computers are using the database.
It won't tell you the name of the user (normally logged as Admin)
If you poll it at fairly regular intervals, you can see "who" (i.e. which computers) have joined / left
I agree that it can sometimes gives false positives however if a user doesn't leave the db 'tidily'

There is an easy way of monitoring additional info on logged on users for databases requiring users to login with a user name / password.

Store the user name, workstation, logged on time, logged off time in a table e.g. tblWhoseOn
Also have a table e.g. tblKickout with a boolean field Kickout with default value = false

Whilst a user is logged on, the value of the Kickout field is monitored, say every 30 seconds
If true, users have a specified time to finish work and close down - I allow 5 minutes

A system admin uses a 'master' program linking to all programs containing the 2 tables and can therefore monitor the login details as necessary and 'lock' one or more databases from there

Once locked, users trying to lock into a database see a screen saying 'closed for maintenance' for a few seconds before the db is closed automatically.

Once maintenance work is completed, sysadmin unlocks the db and (optionally) sends an email to all users to inform them it is available again

I have had this system in place for several years and it works perfectly.

The WhoseOn table also gives me info on logins over a period of time so patterns of usage can be tracked e.g. who / when / how long / how many times / peak times / quiet times etc
 

Users who are viewing this thread

Top Bottom