Check if db file already open

[JP]

Hello,

I normally program in Excel/Outlook but I need some Access help. I am
trying to import some spreadsheets into an Access database so I am
using "DoCmd.TransferSpreadsheet" but I need to check if the file is
already open. Does anyone know how to do this programmatically? I've
already tried googling on this but didn't find anything.

Here is the (Excel) code so far:

Sub ImportData()
Dim X As Access.Application
Set X = New Access.Application

X.OpenCurrentDatabase "filename"
X.DoCmd.TransferSpreadsheet acImport, , "myTable", "C:\filename.xls",
True

End Sub

How would I check if "filename" is already in use?


Thx all,
JP

 

[Alex Dybenko]

Hi,
in general - you can try to rename it, or say open # exclusively.


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[JP]

Hey Alex, thanks for the reply. I figured it out, I just used the DIR
function to check if the "locked" version of the file exists.

If Dir("database.ldb") <> "" Then
Exit sub
else
' run code here
End If

I knew it was something simple I was overlooking.

Thx,
JP

 

[Alex Dybenko]

Hi,
yes, I also thought about the same, but it could happen that database was
crashed, then ldb file still there, but file it not opened

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Hey Alex, thanks for the reply. I figured it out, I just used the DIR
function to check if the "locked" version of the file exists.

If Dir("database.ldb") <> "" Then
Exit sub
else
' run code here
End If

I knew it was something simple I was overlooking.

Thx,
JP

 

[DaveT]

Another way, is use OpenDatabase method. If you can open exclusively, then
proceed with updates. Note that if you attain exclusive lock, the lock is
released when you close the database object.
'====================
Dim ws As DAO.Workspace
Dim dbs As DAO.Database
Dim strConnectMDB As String

On Error GoTo TrapIT

strConnectMDB = "C:\xyz\MyDatabase.mdb"

Set ws = DBEngine.CreateWorkspace("", "Admin", "")

Set dbs = ws.OpenDatabase(strConnectMDB, True, True)

MsgBox "Open and Locked"


EnterHere:

On Error Resume Next
Set dbs = Nothing
ws.Close
Set ws = Nothing

Exit Sub
TrapIT:
MsgBox Err.Number & vbCrLf & Err.Description
Resume EnterHere
'=======================