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

[deletedT]

While being working in this.accdb is it possible to check if another.accdb is open or not?

And if it's opened, is it possible to close it?


Many thanks for any kind of help.

 

[Gasman]

Not something I have even done (yet), but you could look for the lockfile .laccdb
If it is open, I can't see why you could not close it using code.

https://social.msdn.microsoft.com/Forums/office/en-US/40179893-fbd3-46cc-b3ac-ec8e20a59400/closing-another-database-in-vba?forum=accessdev

 

[isladogs]

As Gasman said the lock file is an important clue but also the issue

It is easy enough to open an external database, do something & close it again....
Its also easy to check for a lock file if you wish

Public Function RunExternalDatabase()
    
    Dim app As Access.Application, strPath As String, strLockPath As String
    
    'Start a new Access application
    Set app = New Access.Application
    
     strPath = "YourFullFilepathHere.accdb" 'enter your full file path

     strLockPath=Left(strPath, Len(strPath)-5) & "laccdb"

     If Len(strLockPath) > 0 Then MsgBox "File is in use" 'this won't detect if opened exclusively as no lock file is created
         
    'Open the remote database and (optionally) run a macro, then close the remote database
    With app
        'Syntax: .OpenCurrentDatabase(filepath, Exclusive, strPassword) - the last 2 items are optional
      
        .OpenCurrentDatabase strPath ', False ', "password"
        
        .Visible = True 'optional
    
     '  .DoCmd.RunMacro "mcrRefreshPersTable" 'run your macro (optional)
        .CloseCurrentDatabase 'closes external database as that is current
    End With
    
    'Quit the spawned app
    app.Quit acQuitSaveNone
    Set app = Nothing
    
    'Quit the current app - optional
    'Application.Quit acQuitSaveNone
    
End Function

However the problem comes if the external app is open as it already has a lock file and by definition you can't delete a lock file that is in use
So if the file is in use, the above code will work but the external app will stay open
If the external app is opened exclusively there is no lock file but you will get an error. As it is opened exclusively, you can't also open it!

I don't believe that using Shell or other methods of opening files will help either

The only real solution I can think of is to use 'kickout code' built into your other app to close it automatically after a period of inactivity

 

[deletedT]

It is easy enough to open an external database, do something & close it again....

I don't want to open it. I just want to close it. It's completely different.

The only real solution I can think of is to use 'kickout code' built into your other app to close it automatically after a period of inactivity

No, I can't use this. We have a lot of problems with forms' timer and can not use onTimer events. By WE I mean those countries who use IME. It's out of scope of this post, But if you're curious to know, I can explain it in a PM or another post.
Even though I find a way to do it, showing the user a message to close another.accdb manually is much more practical(for my situation).


Anyway thanks to both of you.
I got the idea to check for the lock file to see if the external database is open or not. I think that part is solved.
Now I have to see if I can close it.

I'll check how your code behaves, tomorrow morning as soon as I'm back to office.

Thanks again.


PS: Where can I ask several questions about this forum and how it works, may some suggestions and a very strange thing that I just found out?

 

[isladogs]

I don't want to open it. I just want to close it. It's completely different.

I was aware of that
Just pointing out that its easy to open an external db & to close it if nobody else is using it.
However the lock file serves to prevent you doing that if someone else is using it and for a good reason - to prevent likely corruption of data

Even though I find a way to do it, showing the user a message to close another.accdb manually is much more practical(for my situation).

There are ways of messaging people who are using a database.
For example: http://www.utteraccess.com/forum/index.php?showtopic=1169381

Where can I ask several questions about this forum and how it works, may some suggestions and a very strange thing that I just found out?

Try the Site Suggestions section of the forum

 

[The_Doc_Man]

If the other DB is open from the current app, there is a collection in Workspaces called .Databases that you can examine using collection-handling methods. The .Path string for each open database should be visible. If you can see it this way, you can close it.

If the other DB is open from a different app then you might have issues doing this interactively. Windows Resource Manager can show you a list of open file handles but that is hard to read from an Access app. There is also the command-line utility called OpenFiles but you need to do some registry things first to enable this. If you do, you might be able to write a shell script or issue some SHELL commands.

If the putative file is on another system in your network, OpenFiles might still be useful but requires elevated privileges. At the very least, perhaps Operator privilege to even see such a list, or Admin to do more than just see it. And at that level, there are some sys admin tools you could look up.

 

[deletedT]

If the other DB is open from the current app, there is a collection in Workspaces called .Databases that you can examine using collection-handling methods. The .Path string for each open database should be visible. If you can see it this way, you can close it.

If the other DB is open from a different app then you might have issues doing this interactively. Windows Resource Manager can show you a list of open file handles but that is hard to read from an Access app. There is also the command-line utility called OpenFiles but you need to do some registry things first to enable this. If you do, you might be able to write a shell script or issue some SHELL commands.

If the putative file is on another system in your network, OpenFiles might still be useful but requires elevated privileges. At the very least, perhaps Operator privilege to even see such a list, or Admin to do more than just see it. And at that level, there are some sys admin tools you could look up.

The another.accdb has been opened separately by itself. (double clicking the shortcut on desktop). So .Databases collection can't help.

Both databases are local and none of them is located on a shared folder.
The user(s) have admin rights.

I will search to see what I find about OpenFiles and if can help.
I will also search to see if I can write a batch file to close the mentioned DB and trigger a shell to run the batch file from access.

Thanks for the advice.

By the way, I love your sig.

 

[Gasman]

Task Manager shows Access and then below it will show a process of Access and the db name?

 

[isladogs]

The only issue with forcibly closing any database file via Task Manager is that it can cause corruption. That's why I didn't mention it before.
It is best to only use that method as a last resort e.g. when a lock file isn't deleted on normal closedown.

If the databases are local, why can't the users just check in the taskbar and close anything that is open 'manually' and safely

 

[The_Doc_Man]

I'm with Colin on this one. Forcibly closing a database externally can leave tables partly updated and thus inconsistent. If your database cannot close itself, there is a SERIOUS risk in forcing it closed. Serious - as in potentially unrecoverable.

 

[deletedT]

OK. I admit it. It's my fault. Again.
I should have explained what I'm trying to do.
I hate long posts, that's why I keep my post as short as possible. And it gives you wrong ideas.
I'm sorry.

It's not that the database doesn't close and I want to forcibly close it or anything in that line.
The database that I want to close, is a FrontEnd database that is linked to a SQL server database on our server.
Every now and then, I add a new feature and put a copy of the updated FE on a shared folder on our server. On every login to DB, the version is checked and if a new version is released, users receive a message to update and they have to simply over write their copies with this new version.

But they keep asking me, if their front end file is wise enough to understand a new version has been release, why I don't update it automatically. and why they have to do it manually?

Now it's what I've done.
I made a schedule in our server to open a database at 12:30 (lunch time) every other day. This database has an autoexec that fires a function. This function checks to see if a new version of the FE is released.
If NO, it closes itself.
If YES, then it overwrites their copies with the new version of frontend. and closes itself.

Everything is fine and users are using the latest version. The only problem is when the users leave their front end open during lunch time.
I'm trying to close their FE to be able to over write it.


But I think I found a way to do it.
I'm testing it and if I succeed I will report it here.

thanks again for all replies.

 

[isladogs]

It would be appreciated if you gave the correct explanation from the start.
It would have saved us all a lot of time!

The 'kick-out routine' I mentioned for a period of user inactivity would of course solve that issue and I use that for another purpose with my own apps

However I use a different method to achieve the same result as you want here.
When users click on the desktop shortcut to my apps, they are actually launching a small 'starter app'. This checks for a newer version in an upgrade folder on the network as you are doing .
If users already have the latest version, the starter app closes & launches the main app
If a newer version is found, the required files are downloaded to the user's PC and then the main app is launched. As APIs are used this is VERY fast. The process can also be used to update the starter app itself if a newer version is available

So the update occurs the first time each user runs the app after it has been updated on the network.
On the rare occasions, I need everyone to update 'immediately', I use the kick-out routine to close all front ends properly after a few minutes warning.

Attached is a brief explanation of how my approach works in case its any help to you

 

[Cronk]

When users click on the desktop shortcut to my apps, they are actually launching a small 'starter app'. This checks for a newer version

I take a different approach.
Rather than a starter app, on open I perform a check on the version number stored in a local file in the FE and compare it with the version stored in the BE. Then start an 'upgrader app'.
Intra day checks could be implemented using code in a timer event in a hidden continuously open form on the FE.
Either way avoids the need to close FE's from the server.

 

[Deleted member 147267]

why I don't update it automatically.

You need to prompt them in their own fe as in "Update? y/n". If no, just quit their db (assuming you don't want them to work in an outdated version). If yes, call the code that over-writes their copy by passing the CurrentProject.Path to it then Quit. Now the over-write can occur because their db is closed and all they had to do was one click for "yes". The upgrading code should be able to re-launch their updgraded version based on the path you passed to it. Optionally, you can store a copy of their fe if it has user level tables with data that needs to be kept in case the upgrade fails. Then on successful startup after the upload, kill the backup version - or not.
EDIT should mention that this means they only upgrade next time they open their fe. On one hand, it means you don't need to worry about upgrades en masse every time you release a new version. On the other, it means they can work in an outdated version for as long as they keep their copy open. Since I don't know your IT policies, can't say if that would ever be an issue. Maybe IT terminates all connections in the middle of the night. The suggested method would mean form timer events aren't required to check for upgrades. You'd have to get drastic seeing as how you can't force a user to close down upon a timer event without risking data loss or corruption. You could prevent them from opening any new forms/reports though, pretty much forcing them to shut down.

 

[deletedT]

It would be appreciated if you gave the correct explanation from the start.
It would have saved us all a lot of time!

Yes I know. And am really sorry.

When users click on the desktop shortcut to my apps, they are actually launching a small 'starter app'. This checks for a newer version in an upgrade folder on the network as you are doing .
If users already have the latest version, the starter app closes & launches the main app
If a newer version is found, the required files are downloaded to the user's PC and then the main app is launched. As APIs are used this is VERY fast. The process can also be used to update the starter app itself if a newer version is available

It's somehow what I explained I'm testing. I took a different approach.
In my FE I check for a new version. If No, it continues normally.
If YES, the FE opens the upgrade file and closes itself. The upgrade file, upgrades the FE, launches it and closes itself.

I will test both your way and mine to see which one is faster.
Once again, thanks for sharing your wisdom.

 

[deletedT]

I take a different approach.
Rather than a starter app, on open I perform a check on the version number stored in a local file in the FE and compare it with the version stored in the BE. Then start an 'upgrader app'.
Intra day checks could be implemented using code in a timer event in a hidden continuously open form on the FE.
Either way avoids the need to close FE's from the server.

Yes, as I explained above, It's just what I'm testing.
Anyhow, I will test Isladoge's solution as well to see which one suits me better.

Thanks for your time and advice.

 

[isladogs]

FWIW my starter utility checks the version number against a small version.txt file in the upgrade folder.
It also checks the date modified on the app itself in that folder.
If the version number is higher or the network file is newer it copies across the files.

It also has provision to force an upgrade just in case anything ever goes wrong.

 

[deletedT]

FWIW my starter utility checks the version number against a small version.txt file in the upgrade folder.
It also checks the date modified on the app itself in that folder.
If the version number is higher or the network file is newer it copies across the files.

It also has provision to force an upgrade just in case anything ever goes wrong.

Thanks for additional info.
I can't trust a local file, specially a text file. These users are crazy and like to play tricks on me. If I leave a local file, they simply delete it or change its contents to something that their copy will never be upgraded.

Since I use SQL server as backend, I save the latest version number in a SystemOption table in SQL server. And I have a local table that keeps the current version of FE. I simply compare these two values.
I also have a table to keep track of changes with each version. And users can see a version history, if they go to their help tab in the ribbon.

 

[isladogs]

Thanks for additional info.
I can't trust a local file, specially a text file. These users are crazy and like to play tricks on me. If I leave a local file, they simply delete it or change its contents to something that their copy will never be upgraded.

Since I use SQL server as backend, I save the latest version number in a SystemOption table in SQL server. And I have a local table that keeps the current version of FE. I simply compare these two values.
I also have a table to keep track of changes with each version. And users can see a version history, if they go to their help tab in the ribbon.

LOL
I used to have similar problems with a small number of users who deliberately did everything they could to prevent updates...but the logic we used solved the problem completely.
I also check the version number in the app itself - just in case
Having said that, the updater file copies all files across that are newer ...so if any file is missing it gets copied anyway.

 

[Galaxiom]

I can't trust a local file, specially a text file. These users are crazy and like to play tricks on me. If I leave a local file, they simply delete it or change its contents to something that their copy will never be upgraded.

Since I use SQL server as backend, I save the latest version number in a SystemOption table in SQL server. And I have a local table that keeps the current version of FE. I simply compare these two values.

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.