Determine if File is open

[alex]

Determine if File is open

Using Access ’03…

I have an mde that copies another mde to a particular drive and then
opens the file.

The first thing it does, however, is look for an ldb to see if the
file is already open. This works fine, but sometimes Access is not
shut down properly (because of network issues, etc.) and the ldb still
exists even when the mde is not open.

Is there another way to check and see if the mde is open other than
looking for the presence of the ldb?

I suppose one creative way would be to look for the ldb; if it does
not exist, the file is not open. If it exits, try and kill it. If
you get an error, the mde is open. Not sure how to trap that error,
however.

Thanks,
alex

 

[Clifford Bass]

Hi Alex,

It would be kinder and gentler if you let Acces decide if it is in use.
Open and close the database in question. Something like this:

Public Sub DBInUse()

Dim boolOpen As Boolean
Dim db As DAO.Database
Dim fso As FileSystemObject
Dim strDatabase As String

strDatabase = "D:\ProgSrcs\Access\Database1"
Set fso = New FileSystemObject
boolOpen = fso.FileExists(strDatabase & ".ldb")
If boolOpen Then
Set db = DBEngine.OpenDatabase(strDatabase & ".mdb")
db.Close
Set db = Nothing
boolOpen = fso.FileExists(strDatabase & ".ldb")
End If
Set fso = Nothing
MsgBox strDatabase & IIf(boolOpen, "", " not") & " in use."

End Sub

Hope that helps,

Clifford Bass

 

[alex]

Hi Alex,

     It would be kinder and gentler if you let Acces decide if it is in use.
 Open and close the database in question.  Something like this:

Public Sub DBInUse()

    Dim boolOpen As Boolean
    Dim db As DAO.Database
    Dim fso As FileSystemObject
    Dim strDatabase As String

    strDatabase = "D:\ProgSrcs\Access\Database1"
    Set fso = New FileSystemObject
    boolOpen = fso.FileExists(strDatabase & ".ldb")
    If boolOpen Then
        Set db = DBEngine.OpenDatabase(strDatabase & ".mdb")
        db.Close
        Set db = Nothing
        boolOpen = fso.FileExists(strDatabase & ".ldb")
    End If
    Set fso = Nothing
    MsgBox strDatabase & IIf(boolOpen, "", " not") & " in use."

End Sub

            Hope that helps,

                    Clifford Bass











- Show quoted text -

Thanks for responding Clifford...
Question: Do I need a particular object library for your code above?
I have the "standard" ones, but I think I may need more for the code.
Also, with your code above, if the mde is actually open, will that
cause problems?
alex

 

[Clifford Bass]

Hi Alex,

Sorry, may have been assuming something. The FileSystemObject requires
the Microsoft Scripting Runtime reference. And you should have the latest
DAO referenced also.

The MDE being open should not cause a problem. All that happens in the
code is a check for the LDB file. If there, open/close the database. Access
will deal with the LDB depending on whether or not someone really does have
it open. If not, it will delete the LDB. Otherwise it will leave the LDB in
place. So a subsequent check and finding of the LDB tells you that the file
is actually in use by someone else. However, I did not test what would
happen if the MDE is opened exclusively by someone else. I will leave that
to you. I suspect you will get an error; which you could trap for. You may
also want to check for the actual existence of the MDE.

Clifford Bass

 

[Douglas J. Steele]

My recommendation would be to use Late Binding, rather than requiring a
reference be set to FSO.

Public Sub DBInUse()

Dim boolOpen As Boolean
Dim db As DAO.Database
Dim fso As Object
Dim strDatabase As String

strDatabase = "D:\ProgSrcs\Access\Database1"
Set fso = CreateObject("Scripting.FileSystemObject")
boolOpen = fso.FileExists(strDatabase & ".ldb")
If boolOpen Then
Set db = DBEngine.OpenDatabase(strDatabase & ".mdb")
db.Close
Set db = Nothing
boolOpen = fso.FileExists(strDatabase & ".ldb")
End If
Set fso = Nothing
MsgBox strDatabase & IIf(boolOpen, "", " not") & " in use."

End Sub

Of course, you don't really need FSO:

Public Sub DBInUse()

Dim boolOpen As Boolean
Dim db As DAO.Database
Dim strDatabase As String

strDatabase = "D:\ProgSrcs\Access\Database1"
boolOpen = (Len(Dir(strDatabase & ".ldb")) > 0)
If boolOpen Then
Set db = DBEngine.OpenDatabase(strDatabase & ".mdb")
db.Close
Set db = Nothing
boolOpen = (Len(Dir(strDatabase & ".ldb")) > 0)
End If
MsgBox strDatabase & IIf(boolOpen, "", " not") & " in use."

End Sub

 

[Clifford Bass]

Hi Doug,

I was trying to remember what the non-FSO "file exists" function was so
I could use that instead, but was pulling a blank. Must be Friday . So I
resorted to FSO.

Clifford Bass

 

[alex]

Hi Doug,

     I was trying to remember what the non-FSO "file exists" function was so
I could use that instead, but was pulling a blank.  Must be Friday . So I
resorted to FSO.

              Clifford Bass














- Show quoted text -

Thanks Doug/Clifford. I appreciate the help.
alex

 

[Ferlenciel Dowdell]

test

Hi Doug,

I was trying to remember what the non-FSO "file exists" function was so
I could use that instead, but was pulling a blank. Must be Friday . So
I
resorted to FSO.

Clifford Bass














- Show quoted text -

Thanks Doug/Clifford. I appreciate the help.
alex

 

[Clifford Bass]

Hi Alex,

You are welcome!

Clifford Bass