Closing out of fucus database

[InLaNoche]

I have a database form that is typically emailed out. The problem with opening something from an email is that it usually opens in temp space and is read only. I've been asked to make this form as simple and userfriendly as possible. That said, they are asking that if the file is opened from email, that it be re-saved into my documents.

Maybe not the correct approach, and I am open to ideas, but what I have done is have the file saved to mydocuments, then opened. That works fine, but the read-only instance of the database is still open as well. Is there a way to poll all the open database files? I know how to check if the current DB is read only, I need to check all, and close the instance that is not.

[jonh]

Add startup code that closes the database if it's read only and/or running from a temp folder?

[InLaNoche]

That was my first attempt. I have removed it from the code below but in the last "ELSE" clause I had it close the database. When I do that it closes both. Maybe I was using the wrong command

Private Sub Form_Load()
    Dim aPath
    Dim aFile
    Dim sTime As Date
    
    Dim objFolder As Object
    Dim aDocPath As String
    Dim accapp As Access.Application
     
     
    Set accapp = New Access.Application
         
         
         
    Set objFolder = CreateObject("WScript.shell").SpecialFolders
    'if this has been opened as ready only, the file needs to be
    'copied locally.  Use MyDocuments as default location
    aDocPath = objFolder("mydocuments") & "\ImageLibrary.accdb"
    
    'if the database is ready only need to resave it locally.
    If CurrentDb.Updatable Then
        'not read only, can be worked on
        Me.GetStartedButton.Visible = True
    Else
    
        'Database is Read-Only
        'copy the file to MyDocs and re-open
        MsgBox "File is open as read only.  " & _
            "Saving to Documents folder and re-opening..."
        
        'first check if file exists.  If so kill it
        If Dir(aDocPath) <> "" Then Kill aDocPath
        
        '2 second delay
        sTime = Now
        Do While DateDiff("s", sTime, Now) < 2
        Loop
        
        'make a copy into the myDocs folder, force default file name
        FileCopy CurrentProject.FullName, aDocPath
        
        MsgBox "now opening file " & aDocPath
        
        '2 second delay
        sTime = Now
        Do While DateDiff("s", sTime, Now) < 2
        Loop
        
        'open the new file for them
        accapp.OpenCurrentDatabase (aDocPath)
        accapp.Visible = True
        
        
    End If
    
End Sub

I guess my problem is I was using currentdb.close, but on opening the other db, it became the current db... From what I see, I need to be able to check on open access applications, check for the one that is read only and close it. Unless there is a better way to do it.

my other messy option is to leave it in the back with a "Close Me" button that is only visible when it is read only and hope it does not confuse the end user when they finish with the updateable forms...

[InLaNoche]

Ok, barring this idea, is it possible to open an access database as NOT current? So far I can only find ways to open files as the current database/project...

[jonh]

Not at a computer right now but I guess the method you are using to open the database is the problem., I would suggest using something like shell to open the file. Usually if you use Set the object is discarded when it's closed.

[InLaNoche]

Not at a computer right now but I guess the method you are using to open the database is the problem., I would suggest using something like shell to open the file. Usually if you use Set the object is discarded when it's closed.

Just to be on the same page, I need to close the first file that was open, not the second file that is called up through code.

[jonh]

Yeah, I know.

When you close an application the code tidies up after itself and goes round destroying any objects left open to free up memory.

You are creating an Access object in your code to open a second copy of your database, but when you quit the readonly copy the second copy is destroyed along with it.

Example...

test2.accdb is opened using SET and closes after DoCmd.Quit
test3.accdb is opened using SHELL and remains open after DoCmd.Quit

Private Sub test()
    Const aDocPath2 As String = "C:\test2.accdb"
    Const aDocPath3 As String = "C:\test3.accdb"

 'uses SET to create instance of access
 
    Dim accdb
    Set accdb = CreateObject("Access.Application")
    accdb.OpenCurrentDatabase aDocPath2
    accdb.Visible = True
    
'uses SHELL ...

    Shell """" & GetEXE(aDocPath3) & """ """ & aDocPath3 & """", vbMaximizedFocus
    
    DoCmd.Quit

    'aDocPath2 is closed
    'aDocPath3 remains open
     
End Sub

Private Declare Function FindExecutable Lib "shell32.dll" Alias "FindExecutableA" (ByVal lpFile As String, ByVal lpDirectory As String, ByVal lpResult As String) As Long
Private Function GetEXE(sfile As String) As String
   Dim i As Integer, s2 As String
   If sfile = "" Then Exit Function
   If Dir(sfile) = "" Then Exit Function
   s2 = String(260, 32)
   i = FindExecutable(sfile, vbNullString, s2)
   If i > 32 Then GetEXE = Left$(s2, InStr(s2, Chr$(0)) - 1)
End Function

[InLaNoche]

jonh,

Thanks for this. I have to admit that I am not an expert in VBA, and the second bit of code is giving me an error when I try and use it. I get the error that only comments may appear after End Sub, End Fuction or End Property. This comes on the first line of the second code bit. I put that bit of code in a module, but it's erroring out.

Fixed that, sorry, changed the function to public. But it does not seem to like this line:

Shell """" & GetEXE(aDocPath) & """ """ & aDocPath & """", vbMaximizedFocus

I did change the name of the var to match my code. aDocPath works for the OpenCurrentDB, so I'm not sure that is the issue. I get Invalid procedure call or argument...

[jonh]

GetExe just tries to get the path to Access.exe.

Try this instead.
Shell "MSACCESS """ & aDocPath & """", vbMaximizedFocus

[InLaNoche]

That did the trick!

Thanks for the insite on the shell command in VBA. Worked like a charm!