UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Detect If My Db Is Already Running And Bring To Front?, Office 2003    
 
   
ddaddy
post Sep 20 2011, 04:43 PM
Post #1

UtterAccess Addict
Posts: 293



When a user launches my db from a shortcut, what would be the best way (in the startup code) to detect if it's already running, and if so, maximise it and bring it to front?

I found some dll code to detect if MSAccess was running, but it couldn't tell the name of the DB running.
I think if I check for the .ldb file, and if found try deleting it. If I can't delete it, then its currently running.
But how can I make the running process return to focus?

Is this even possible. Is there a better way?

Thanks
Go to the top of the page
 
+
datAdrenaline
post Sep 20 2011, 11:07 PM
Post #2

UtterAccess Editor
Posts: 16,705
From: Northern Virginia, USA



What is the target of your Shortcut?

The reason I ask is that if the target of the shortcut is simply the mdb file, then dbl-clicking the shortcut will either open the target, or if the target is opened, activate the application that has the file opened.


--------------------
Brent Spaulding | datAdrenaline | Access MVP
It's all very well to tell us to forgive our enemies; our enemies can never hurt us very much. But oh, what about forgiving our friends? - Willa Cather; As always - Pay it Forward!
Go to the top of the page
 
+
ddaddy
post Sep 21 2011, 04:38 AM
Post #3

UtterAccess Addict
Posts: 293



Hi datAdrenaline, my shortcut target is this
"C:\Program Files (x86)\Microsoft Office\OFFICE11\MSACCESS.EXE" "C:\MYAPP\MYAPP.mde" /runtime

If run twice, it loads 2 instances of my app.
Should I write the target differently to not open a 2nd instance of my app?

Thanks
Go to the top of the page
 
+
DanielPineault
post Sep 21 2011, 04:57 AM
Post #4

UtterAccess VIP
Posts: 2,659



Using vbscript to launch your db, could you not check to see if the computer name already appears in the lock file, if so then cross-check the active process to maximize Access. But this still all seem a little odd, if your users can't tell that they already have an instance open of a db, then they probably shouldn't be using the db.

Actually, I think I found you an answer, please take a look at: http://access.mvps.org/access/api/api0041.htm


--------------------
Daniel Pineault
2010-2013 Microsoft MVP
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

Vir sapit qui pauca loquitur
Go to the top of the page
 
+
datAdrenaline
post Sep 21 2011, 10:08 AM
Post #5

UtterAccess Editor
Posts: 16,705
From: Northern Virginia, USA



>> If run twice, it loads 2 instances of my app. <<

While that is the end result, lets look deeper at what is happening ... you are creating two instance of Access, that load your app. Again, I know that is being a bit too detailed since you wind up with two instances of your app, but I wanted to make that distinction because of the way that a short cut works.

Do you really need the /runtime switch? If not, then you can drop the call to MSACCESS.EXE and have the target as simply: "C:\MYAPP\MYAPP.mde", then the shortcut will be smart enough to active your app.


--------------------
Brent Spaulding | datAdrenaline | Access MVP
It's all very well to tell us to forgive our enemies; our enemies can never hurt us very much. But oh, what about forgiving our friends? - Willa Cather; As always - Pay it Forward!
Go to the top of the page
 
+
ddaddy
post Sep 21 2011, 01:54 PM
Post #6

UtterAccess Addict
Posts: 293



I used the runtime switch while testing as my app may be run with a runtime install.

I've just tried changing the shortcut to just point to the mde and it still launches 2 instances if run twice.
Go to the top of the page
 
+
datAdrenaline
post Sep 21 2011, 02:23 PM
Post #7

UtterAccess Editor
Posts: 16,705
From: Northern Virginia, USA



>> I've just tried changing the shortcut to just point to the mde and it still launches 2 instances if run twice. <<

That's interesting ... I don't experience the same thing (Win7/64Bit; Office 2010/64Bit). I create a shortcut that has a target that has only the the db in it and only one instance dazed.gif

----

You can go the API route. I personally like to avoid API's if I can. Please note that I do use them frequently, I just don't go to them as my first choice. So, my first choice in this circumstance is to simply prevent a second instance. You can do that by adding the following line to your start up code.

CurrentProject.Connection.Properties("Jet OLEDB:Connection Control") = 1



By setting this property value to 1, the Jet/ACE database engine will prevent any new connections to the database file, and since this will be set upon opening the db file, any subsequent instance attempts will fail. This may not be the "prettiest" way, but it will indeed prevent multiple instances of your db application.

If you want to get fancy, you can create an AppLaucher database that uses startup code that attempts to open the target database, and if it fails, then present a nice message to the user. However, I have found that when I have been in the circumstance of preventing multiple instances, setting the Connection Control property was sufficient. When my users saw the message that a violation of Connection Control produced they just knew they had the app opened and found it on their task bar and went along there merry way.

Hope that helps!

PS> Here are a couple of articles on Connection Control (aka: Passive Shutdown)
http://support.microsoft.com/kb/198756
http://msdn.microsoft.com/en-us/library/aa...office.10).aspx


--------------------
Brent Spaulding | datAdrenaline | Access MVP
It's all very well to tell us to forgive our enemies; our enemies can never hurt us very much. But oh, what about forgiving our friends? - Willa Cather; As always - Pay it Forward!
Go to the top of the page
 
+
ddaddy
post Sep 22 2011, 02:31 AM
Post #8

UtterAccess Addict
Posts: 293



I've just tested a couple of shortcuts, and it seems a shortcut to a .mdb acts as you say it will and only launches 1 instance. However a shortcut to a .mde keeps launching new instances of access.

The connection control stops the app being launched twice like you said, so thats a good step in the right direction. Thanks.
Do you know if it's possible to trap the message it gives to change the msgbox? I don't think it actually registers as an error, so not sure it's possible.

Many Thanks
Go to the top of the page
 
+
datAdrenaline
post Sep 22 2011, 09:18 AM
Post #9

UtterAccess Editor
Posts: 16,705
From: Northern Virginia, USA



>> Do you know if it's possible to trap the message it gives to change the msgbox? I don't think it actually registers as an error, so not sure it's possible. <<

It does register an error, but you have to use a "launcher" to launch the db from code in order to trap for the error that is thrown. To lauch a db via code, I use one of two options depending on what I am trying to accomplish. The first option is a VBScript (a text file with a .vbs as the extention). The second option is a completely different .MDB/.ACCDB (or .MDE/.ACCDE) that contains a small bit of code to open the true file you wish to open.

With the first option of VBScript, you create text file with an extension of .VBS, and the code would look like this:

CODE
Dim cnn
Dim strFilename

strFilename = "fullPathAndNameOfFile.mde"

On Error Resume Next
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFilename

Select Case Err.Number
        
    Case 0
        On Error GoTo 0
        cnn.Close
        Set cnn = Nothing
        With CreateObject("WScript.Shell")
            .Run """MSACCESS.EXE"" """ & strFilename & """", 3
        End With
        WScript.Sleep 100
        
    Case -2147467259
        MsgBox "The application you are trying to launch cannot be opened"
        Err.Clear
            
    Case Else
        MsgBox Err.Description
          
End Select


{Note: you may need to change the Provider property of the connection string to Microsoft.Jet.OLEDB.4.0}

The drawback to VBScripts is that if your script is local, your users could tinker with it. When I have used scripts to launch my db's, I place them on a network resource that prevents edits.

The next option is to create a small database file with two objects ... and AutoExec macro and a module that contains a function that launches your app. The code in the module would look something like this:

CODE
Option Compare Database
Option Explicit

Public Function LaunchDbApp(strFilename As String)
    
    Dim db As DAO.Database
    
    On Error Resume Next
    Set db = OpenDatabase(strFilename)
    
    Select Case Err.Number
            
        Case 0
            On Error GoTo 0
            db.Close
            Set db = Nothing
            Shell """MSACCESS.EXE"" """ & strFilename & """", vbMaximizedFocus
            
        Case 3734
            MsgBox "The application you are trying to launch cannot be opened"
            Err.Clear
            
        Case Else
            MsgBox Err.Description
            
    End Select
    
    DoCmd.Quit 'Quit the launcherdb and Access instance.
    
End Function


Then your AutoExec macro would have one Action ...

Action: RunCode
Function name: =LaunchDbApp("fullPathAndNameOfFile.mde")

A drawback to an AppLauncher database is that is consumes the resources to launch Access twice (typically not an issue these days), but an advantage is that you can create a much more flexible lauching scheme, for example integrate AutoUpdating (which you can do with a VBScript too, but typically more intuitive in the Access/VBA environment). In addition, with the AppLauncher database you can make it into an MDE and protect yourself from curious onlookers.


--------------------
Brent Spaulding | datAdrenaline | Access MVP
It's all very well to tell us to forgive our enemies; our enemies can never hurt us very much. But oh, what about forgiving our friends? - Willa Cather; As always - Pay it Forward!
Go to the top of the page
 
+
ddaddy
post Sep 22 2011, 09:30 AM
Post #10

UtterAccess Addict
Posts: 293



Many thanks for your help, your code will enter my library :-)
I have just found another bit of code that seems to do a good job of detecting if the db is already running or not.
You simply call winCheckMultipleInstances from the autoexec or startup form.

CODE
Option Compare Database
Option Explicit

'******************** Code Start ********************
' Module mdlCheckMultipleInstances
' © Graham Mandeno, Alpha Solutions, Auckland, NZ
' graham@alpha.co.nz
' This code may be used and distributed freely on the condition
' that the above credit is included unchanged.

Private Const cMaxBuffer = 255

Private Declare Function apiGetClassName Lib "User32" Alias "GetClassNameA" _
    (ByVal hwnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long

Private Declare Function apiGetDesktopWindow Lib "User32" Alias "GetDesktopWindow" () As Long

Private Declare Function apiGetWindow Lib "User32" Alias "GetWindow" (ByVal hwnd As Long, ByVal wCmd As Long) As Long

Private Const GW_CHILD = 5
Private Const GW_HWNDNEXT = 2

Private Declare Function apiGetWindowText Lib "User32" Alias "GetWindowTextA" _
    (ByVal hwnd As Long, ByVal lpString As String, ByVal aint As Long) As Long

Private Declare Function apiSetActiveWindow Lib "User32" Alias "SetActiveWindow" (ByVal hwnd As Long) As Long

Private Declare Function apiIsIconic Lib "User32" Alias "IsIconic" (ByVal hwnd As Long) As Long

Private Declare Function apiShowWindowAsync Lib "User32" _
    Alias "ShowWindowAsync" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long

Private Const SW_SHOW = 5
Private Const SW_RESTORE = 9

Public Function winGetClassName(hwnd As Long) As String
Dim sBuffer As String, iLen As Integer

sBuffer = String$(cMaxBuffer - 1, 0)
iLen = apiGetClassName(hwnd, sBuffer, cMaxBuffer)
If iLen > 0 Then
    winGetClassName = Left$(sBuffer, iLen)
End If

End Function

Public Function winGetTitle(hwnd As Long) As String
Dim sBuffer As String, iLen As Integer

sBuffer = String$(cMaxBuffer - 1, 0)
iLen = apiGetWindowText(hwnd, sBuffer, cMaxBuffer)
If iLen > 0 Then
    winGetTitle = Left$(sBuffer, iLen)
End If

End Function

Public Function winGetHWndDB(Optional hWndApp As Long) As Long
Dim hwnd As Long

winGetHWndDB = 0
If hWndApp <> 0 Then
    If winGetClassName(hWndApp) <> "OMain" Then Exit Function
End If

hwnd = winGetHWndMDI(hWndApp)
If hwnd = 0 Then Exit Function
hwnd = apiGetWindow(hwnd, GW_CHILD)
Do Until hwnd = 0
    If winGetClassName(hwnd) = "ODb" Then
        winGetHWndDB = hwnd
        Exit Do
    End If
    hwnd = apiGetWindow(hwnd, GW_HWNDNEXT)
Loop

End Function

Public Function winGetHWndMDI(Optional hWndApp As Long) As Long
Dim hwnd As Long

winGetHWndMDI = 0
If hWndApp = 0 Then hWndApp = Application.hWndAccessApp
hwnd = apiGetWindow(hWndApp, GW_CHILD)
Do Until hwnd = 0
    If winGetClassName(hwnd) = "MDIClient" Then
        winGetHWndMDI = hwnd
        Exit Do
    End If
    hwnd = apiGetWindow(hwnd, GW_HWNDNEXT)
Loop

End Function

Public Function winCheckMultipleInstances(Optional fConfirm As Boolean = True) As Boolean
Dim fSwitch As Boolean, sMyCaption As String
Dim hWndApp As Long, hWndDb As Long
On Error GoTo ProcErr

sMyCaption = winGetTitle(winGetHWndDB())
hWndApp = apiGetWindow(apiGetDesktopWindow(), GW_CHILD)

Do Until hWndApp = 0
    If hWndApp <> Application.hWndAccessApp Then
        hWndDb = winGetHWndDB(hWndApp)
        If hWndDb <> 0 Then
            If sMyCaption = winGetTitle(hWndDb) Then Exit Do
        End If
    End If
    hWndApp = apiGetWindow(hWndApp, GW_HWNDNEXT)
Loop

If hWndApp = 0 Then Exit Function

If fConfirm Then
    If MsgBox(sMyCaption & " is already open" & vbCr _
    & "Do you want to open a second instance of this database?", _
    vbYesNo Or vbQuestion Or vbDefaultButton2) = vbYes Then Exit Function
End If

apiSetActiveWindow hWndApp

If apiIsIconic(hWndApp) Then
    apiShowWindowAsync hWndApp, SW_RESTORE
Else
    apiShowWindowAsync hWndApp, SW_SHOW
End If
Application.Quit

ProcEnd:
    Exit Function
ProcErr:
    MsgBox Err.Description
    Resume ProcEnd
    
End Function
Go to the top of the page
 
+
datAdrenaline
post Sep 22 2011, 11:03 AM
Post #11

UtterAccess Editor
Posts: 16,705
From: Northern Virginia, USA



>> I have just found another bit of code that seems to do a good job of detecting if the db is already running or not. <<

smirk.gif

I hate to point this out, but that is the code that DanielPineault linked you to. I personally like to avoid API calls if I can (not that they are bad -- its just a personal thing), but if that is the solution that works for you -- then by all means use it!!

Good Luck on your project, I am definately glad that you have been able to implement a solution that works for you. thumbup.gif


--------------------
Brent Spaulding | datAdrenaline | Access MVP
It's all very well to tell us to forgive our enemies; our enemies can never hurt us very much. But oh, what about forgiving our friends? - Willa Cather; As always - Pay it Forward!
Go to the top of the page
 
+
ddaddy
post Sep 22 2011, 11:21 AM
Post #12

UtterAccess Addict
Posts: 293



Oh my god, so it is.
My apologies DanielPineault.

I did try that code after it was posted, I can't remember the reason why I initially discounted it. I think I thought it only detected that Access was running, ad not the specific App. How wrong I was.

It's working OK, not 100% though.
If I could find a way to determine the hwnd of the topmost form that is currently open, then I could use that hwnd to make sure the form is maximised and at the front (My db hides the access container on startup)
Go to the top of the page
 
+
datAdrenaline
post Sep 22 2011, 12:57 PM
Post #13

UtterAccess Editor
Posts: 16,705
From: Northern Virginia, USA



>> If I could find a way to determine the hwnd of the topmost form that is currently open <<

You can likely utilize the .Hwnd propery of the Access Form object somehow.


--------------------
Brent Spaulding | datAdrenaline | Access MVP
It's all very well to tell us to forgive our enemies; our enemies can never hurt us very much. But oh, what about forgiving our friends? - Willa Cather; As always - Pay it Forward!
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 28th July 2014 - 03:27 PM