How to Check if a database is open and close it if it is?

[essaytee]

A record in local table could be updated or the date manipulated on a file.

Or, what I do is embed, hard code, a constant in the accde file.

 

[The_Doc_Man]

Yet another approach: In my case, the auto-updater violated certain rules arbitrarily, capriciously, and totally mistakenly imposed by the local security supervisor. (Of course, he saw it differently than I did. ) So instead of auto-updating, I put a versioning system in place and if the version the user launched wasn't "new enough" then it displayed a message for a few seconds and then aborted the DB. Refused to run at all. So they HAD to get a new version. The "master copy" in the shared folder knew where it was running and if you directly ran that master copy, it popped up a different nasty-gram and refused to run. So they HAD to get the new version to run the app.

I had TWO versions in the compatibility table - earliest compatible build number and latest (current) build number. If you were between (inclusive) the two, you could run, but if you were not on the most recent build, you got a reminder to download the newer version on next launch. Usually, it was possible to know ahead of time if the new version would be compatible or incompatible, so I would update the BE table that held the version compatibility info upon releasing the latest, greatest version.

This worked well for at least two or three years before I retired. Don't know that much was done with it later.

 

[deletedT]

A record in local table could be updated or the date manipulated on a file.

You could have the application check a hash of a local file to ensure it is unchanged. (Of course not the front end itself because it changes when used.)

And tell them that any changes detected will automatically be reported to the boss.

They're just enjoy teasing me. We are good friends. And the Boss is ONE of them too

The version I register in the table, is encrypted and even if they access the table, they don't understand the meaning. And even if they change a part of it, the update is done anyway. I'm using a specific method in versioning and while checking the latest version number against the last one, if I find something irregular, I know someone is fooling around and execute the backup anyway.

thanks

 

[deletedT]

Yet another approach: In my case, the auto-updater violated certain rules arbitrarily, capriciously, and totally mistakenly imposed by the local security supervisor. (Of course, he saw it differently than I did. ) So instead of auto-updating, I put a versioning system in place and if the version the user launched wasn't "new enough" then it displayed a message for a few seconds and then aborted the DB. Refused to run at all. So they HAD to get a new version. The "master copy" in the shared folder knew where it was running and if you directly ran that master copy, it popped up a different nasty-gram and refused to run. So they HAD to get the new version to run the app.

I had TWO versions in the compatibility table - earliest compatible build number and latest (current) build number. If you were between (inclusive) the two, you could run, but if you were not on the most recent build, you got a reminder to download the newer version on next launch. Usually, it was possible to know ahead of time if the new version would be compatible or incompatible, so I would update the BE table that held the version compatibility info upon releasing the latest, greatest version.

This worked well for at least two or three years before I retired. Don't know that much was done with it later.

I like your method.
I will think over it and may follow something like that.

At present in the SystemOption table in BE I have a Yes/No field called force.
If a new version is released and it's a critical update, I set it to Force=Yes.
In this case FE won't run and quits until the upgrade is done.

But having a lowest possible version and the latest, seems logical.
I'll think over it if it can help my situation.

Thanks for pointing it out.

 

[The_Doc_Man]

Just for further clarification: If the lowest compatible build was set to match the current build, then there was no lower compatible version. However, I tried to not do that too often. Maybe three times a year, a monthly update would be totally forced; the rest of the time I would allow one or two versions back. If I had to change a table structure, they needed the new version immediately. But if all I did was add a couple of new reports, or maybe added info to a form, I allowed older versions.

 

[deletedT]

I think I need a little help on this.
It's the steps I took:

1. Check if any update is available.
2. If Yes, show a message that contains new features and changes
3. fire up update file
4. Close current database to allow update file overwrite it.

If I do a step by step run (F8) it works great. but running the code simultaneously (F5) doesn't work. CurrentDb closes and update file never opens.
I thought the process is too fast. and before the shell command is sent to open the update file, current database is closed.

I added a DoEvents between opening the update and closing currentdb, it didn't work.
I added a 3 seconds wait time and it works just OK.

I used this to open the upgrade file:

Shell "cmd /c " & Chr(34) & DB & Chr(34), vbHide

Am I missing anything? or is it what it should be?


thanks.

 

[Gasman]

I took the versioning method when I had to automatically update the front end. I used Bob Larsen's code I think. I've posted here enough times to help others.

However you situation has raised an issue, in that it does rely on checking version when it starts. If the user never shuts the app down, then that would not happen.?

I suppose a timer event for every 12 hours could overcome that, though you said you have issues with timer events.?

Some people even copy the latest version on every start of the app, but again, not shutting down can negate that approach.?

 

[essaytee]

I think I need a little help on this.

If I do a step by step run (F8) it works great. but running the code simultaneously (F5) doesn't work. CurrentDb closes and update file never opens.
I thought the process is too fast. and before the shell command is sent to open the update file, current database is closed.

I added a DoEvents between opening the update and closing currentdb, it didn't work.
I added a 3 seconds wait time and it works just OK.

I used this to open the upgrade file:

Shell "cmd /c " & Chr(34) & DB & Chr(34), vbHide

Am I missing anything? or is it what it should be?

thanks.

Maybe post the actual code snippet you are using, that should help us.

 

[Minty]

I have had issues with a locally generated batch file called from an access app not completing copies before trying to open, apparently it can't happen, But it does!
If you run the batch / cmd file directly it works every time. Go figure?

I added some code to the batch creation script that checks if the file has copied, this seemed to cure the issue for most users. See the modified output file creation below.

    If Right(CurrentProject.Name, 3) = "mdb" Then
        sBak = "_bak.mdb"
        iLeng = 4
    Else
        sBak = "_bak.accde"
        iLeng = 6
    End If

   g_strFilePath = CurrentProject.Path
   g_strCopyLocation = PutYOurGLobalMasterLocationInHere

   sBak = g_strFilePath & "\Previous_Version_" & CurrentProject.Name



    ' sets the file name and location for the file to delete
    strKillFile = g_strFilePath & "\" & CurrentProject.Name
    ' sets the file name and location for the file to copy
    strReplFile = g_strCopyLocation & "\" & CurrentProject.Name
    sOldBack = g_strCopyLocation & "\" & "Previous_Version_" & CurrentProject.Name
    ' sets the file name of the backup file to create
    strBackUp = strKillFile
    strBackUp = Left(strBackUp, (Len(strKillFile) - iLeng)) & sBak
    strTarget = Left(CurrentProject.Name, (Len(CurrentProject.Name) - iLeng)) & sBak
    'debug.print strBackUp
    ' sets the file name of the batch file to create
    TestFile = CurrentProject.Path & "\UpdateDbFE.cmd"
    ' sets the restart file name
    strRestart = """" & strKillFile & """"
    ' creates the batch file        -- The 9 after the ping -n is the delay for FS it needs to be this large
    Open TestFile For Output As #1
    Print #1, "Echo Off"
    Print #1, "ECHO Deleting old file"
    Print #1, ""
    Print #1, "if exist """ & strBackUp & """ del """ & strBackUp & """"
    Print #1, ""
    Print #1, "ECHO Copying backup file"
    Print #1, "Echo Off"
    Print #1, "ping -n 14 127.0.0.1 > nul"
    Print #1, ""
    Print #1, ":Fileready1"
    Print #1, "DEL """ & strKillFile & """ "
    Print #1, "ECHO Copying New file"
    Print #1, "Echo Off"
    Print #1, "Copy /Y """ & strReplFile & """ """ & strKillFile & """"
    Print #1, ""
    Print #1, ":checkfilecreation"
    Print #1, "REM Now that a .mov file has been added to the folder check to see if the file has "
    Print #1, "REM completed the conversion or copying to the folder before starting the app "
    Print #1, "Copy /Y """ & sOldBack & """ """ & sBak & """"
    Print #1, ""
    Print #1, "CLS"
    Print #1, ""
    Print #1, "for %%I in (""" & strKillFile & """) do ( (call ) >>%%I ) 2>nul && (cls && set b=%%I && @echo !b! is completed and transfer is ready && GOTO :fileready"
    Print #1, ") || (cls && echo %%I is still being created"
    Print #1, ")"
    Print #1, ""
    Print #1, ":ContinueCheck"
    Print #1, "REM Go back to checkfilecreation module"
    Print #1, "GOTO :checkfilecreation"
    Print #1, ""
    Print #1, ":Fileready"
    Print #1, "Echo CLICK ANY KEY TO RESTART THE ACCESS PROGRAM"
    Print #1, "START /I " & """MSAccess.exe"" " & strRestart
    Close #1

Shell TestFIle

Application.Quit

 

[deletedT]

Maybe post the actual code snippet you are using, that should help us.

It's late at night now in Japan, and tomorrow I'll be on a business trip. I won't be at my desk until Friday. I'll post the actual code as soon as I'm back.

Thanks for trying to help.

 

[deletedT]

I have had issues with a locally generated batch file called from an access app not completing copies before trying to open, apparently it can't happen, But it does!
If you run the batch / cmd file directly it works every time. Go figure?

I added some code to the batch creation script that checks if the file has copied, this seemed to cure the issue for most users. See the modified output file creation below.

Thanks for your help.
I'll give it a go as soon as I'm back to my office.

 

[deletedT]

Maybe post the actual code snippet you are using, that should help us.

I used a remote control software to access my PC at work and copy the code:

Public Sub CheckForNewVersion(Optional ForceUpdate As Boolean = False)
    
    Dim CurrentVersion As String
    Dim LatestVersion As String
    
    Dim Msg As String
    Dim Style As String
    Dim ttl As String
    Dim Response As Integer
    
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim sql As String
    Dim fltr As String
    
    Dim Updates As String
    Dim MoreUpdates As String
    Dim Counter As Integer
    Dim DB As String
    
    Const ShowThisNoOfUpdates = 3
    Dim App As String
    Dim index as Integer
    
    App = GetAppName
    CurrentVersion = GetVersion
    fltr = "Release=1 AND " & App & "=1"
    ttl = CurrentVersion
    
    LatestVersion = ELookup("Version", "tblVersion", "Version_ID=" & DMax("Version_ID", "tblVersion", fltr))
    If DCount("*", "tblVersion", fltr & " AND Version>'" & CurrentVersion & "' And ForceUpdate=1") Then
        ForceUpdate = True
    End If
    
    ' since update is changed to be an automatic precedure, the following option is deleted
' =========================================================================
    ' version check is fun from startup. so if user has set not to check for versions at start up
    ' and there's no force option exit sub
'    If fGetOption("User", "CheckForNewVersion", False) = True _
'            And ForceUpdate = False 
'        Exit Sub
'    End If
' =========================================================================
    
    If Not CurrentVersion = LatestVersion 
        Set cn = CurrentProject.Connection
        rs.CursorLocation = adUseClient
        
        fltr = "Version>'" & CurrentVersion & "' AND " & App & "=1 AND Release=1"
        sql = "SELECT * FROM tblVersion WHERE " & fltr & " AND Release=1 ORDER BY Version_ID ASC"
        rs.Open sql, cn, adOpenDynamic, adLockOptimistic
        
        With rs
            If .EOF Then
                Msg = "Current Version is the latest version."
                Style = vbOKOnly + vbInformation
                MsgBox Msg, Style, ttl
                Exit Sub
            End If

            Counter = 0
            Updates = ""
            MoreUpdates = ""
            Msg = ""
            
            Msg = Msg & "Current Version：" & CurrentVersion
            Msg = Msg & vbCrLf & vbCrLf
            Msg = Msg & "A new version(" & LatestVersion & ") is available. "
            Msg = Msg & "Clicking OK button bellow will update your copy."
            Msg = Msg & "Changes and Updates are as following："
            Msg = Msg & vbCrLf
            
            Do
                Counter = Counter + 1
                If Counter <= ShowThisNoOfUpdates Then
                    Updates = Updates & "● " & !Updates
                    Updates = Updates & vbCrLf
                    
                Else
                    MoreUpdates = MoreUpdates & "● " & !Updates
                    MoreUpdates = MoreUpdates & vbCrLf

                End If
                
                .MoveNext
            Loop Until .EOF
        End With
        
' *****************************************************************************
' *****************************************************************************
        If DCount("Version", "tblVersion", "ForceUpdate=1 AND Release=1 AND " & App & "=1") > 0 Then
            Msg = Msg & Updates & "and several more changes."
            Style = vbOKOnly + vbInformation
            MsgBox Msg, Style, ttl  
        End If

            Msg = vbCrLf & vbCrLf
            Msg = Msg & "A complete list of changes and updates：" & vbCrLf & vbCrLf
            Msg = Msg & Updates & MoreUpdates
            CreateTextFile "temp", Msg
    
        DB = CurrentProject.Path & "\UpdateReceiption.accdb"
            If fFolderExists(DB) = False Then
                FormattedMsgBox "Update file not found.", "Contact your admin to update."
                Application.Quit
                End
            End If

            Shell "cmd /c " & Chr(34) & DB & Chr(34), vbHide
            WaitSeconds (3)
            DoCmd.Quit

End Sub

 

[The_Doc_Man]

The way I used, the test was performed by the app's Opening Form .OnOpen routine, which CAN see data - it just can't do anything with controls on that form.

I put a string constant equal to the current build number in Class module of the opening form. I put a table of historical build numbers in the BE table that was my build history. I kept it there because the boss wanted accountability of build history. I put a flag in the build history table that said "Mandatory" and set that when something in that build was going to make all earlier builds fail. Then it was two DLookups to find the build with the most recent date (current build) and (2nd lookup) the build with the Mandatory flag = TRUE and the most recent date (last compatible build). If the FE's build constant was the same as the current build, no message and no hesitation. If the FE's build was greater than the last compatible build, pop up a message with an OKOnly message box. If the FE's build was lower than the last compatible build, pop up a different message box (again, OKOnly) and cancel the form's Open event and run a DoCmd.Application.Quit and get out of there. The message boxes froze the code until the user acknowledged the box AND they used the Modal Dialog option so there was no way for the user to work without acknowledging the box.

 

[essaytee]

I used a remote control software to access my PC at work and copy the code:

        DB = CurrentProject.Path & "\UpdateReceiption.accdb"
            If fFolderExists(DB) = False Then
                FormattedMsgBox "Update file not found.", "Contact your admin to update."
                Application.Quit
                End
            End If

            Shell "cmd /c " & Chr(34) & DB & Chr(34), vbHide
            WaitSeconds (3)
            DoCmd.Quit

End Sub

I don't think the WaitSeconds(3) is required. You are running a batch file, that will be deleting the application, therefore you want the application to be closed, why keep it open longer. In your batch file, you could incorporate a delay of 5 or 10 seconds, just to make sure that the file about to be deleted has in fact been deleted.

 

[deletedT]

I don't think the WaitSeconds(3) is required. You are running a batch file, that will be deleting the application, therefore you want the application to be closed, why keep it open longer. In your batch file, you could incorporate a delay of 5 or 10 seconds, just to make sure that the file about to be deleted has in fact been deleted.

I’m not running a batch file. It’s another access database.

 DB = CurrentProject.Path & "\UpdateReceiption.accdb"

As I explained in my previous post,If I delete that wait time it doesn’t work and I’m wondering why.

 

[essaytee]

I’m not running a batch file. It’s another access database.

 DB = CurrentProject.Path & "\UpdateReceiption.accdb"

As I explained in my previous post,If I delete that wait time it doesn’t work and I’m wondering why.

I'm not sure if this is an issue, but change your DoCmd.Quit to Application.Quit. They effectively are the same thing, but who knows, worth a try.

Ok, I said batch file, and yes it's another Accces file being opened, but in that Access file, you are still deleting the old Access file (copying new file from the server to the user's folder).

I can't explain why removing the Wait causes a problem.

 

[deletedT]

I'm not sure if this is an issue, but change your DoCmd.Quit to Application.Quit. They effectively are the same thing, but who knows, worth a try.

Ok, I said batch file, and yes it's another Accces file being opened, but in that Access file, you are still deleting the old Access file (copying new file from the server to the user's folder).

I can't explain why removing the Wait causes a problem.

Thanks. I’ll give it a try as soon as I’m back to work.