Check for LDB file in linked database

[Ringhal]

Hello VBA Express

We have a program built using Access that links to other databases, one at a time. This program runs a bit of VBA code to link to the database (a "back end") and after we make changes it will unlink when the program is closed. Basically, the way this works is, you select a file to link to and then click a button. The button does various things and eventually links to the database. I need a piece of code that will check whether the back-end database is being accessed before linking to the database.

We use cloud storage to collaborate changes to the databases and I want a function like this to avoid conflicts. The easiest way I thought of doing this is, by checking for the LDB file created by the database (as it's automatically and instantly synced across all users/devices). Also, I need a function to check that the LDB file is then deleted after the database is unlinked.

Unfortunately I cannot upload a sample as I don't know exactly how the linking is done, as I didn't build the program. I understand that checking for the LDB file is neither the best way nor foolproof, but I am also open to other suggestions.

Any help is greatly appreciated.

[jonh]

Use Dir() to find a file and kill() to delete it.

If the ldb exists try to delete it. If the delete fails (trap the error) it's being used.

[Ringhal]

Thank you. This is the code I came up with based on your advise, but it doesn't work as expected.


Private Sub Command51_Click()
Dim Client

Client = Me!mdbname 'Textbox containing path to linked database

On Error Resume Next
Kill (Dir(Replace(Client, ".mdb", ".ldb")))
If Error(53) Then MsgBox "File Not Found" 'File doesn't exist, continue with procedure
If Error(70) Then MsgBox "Permission Denied" 'File exist, deny access to the database

End Sub

I worked out, to get the path name, I can use the above string that's in the textbox and replace ".mdb" with ".ldb". This works when I look for the file, if I have already linked to the database.
For instance,

debug.print Replace(Client, ".mdb", ".ldb")

After linking to the database and clicking the button to run the code. Both Error(53) and Error(70) come up and this isn't expected. If I delete the Error(53) line in my code, it runs the way I want. I need the Error(53) line because it tells me the database isn't linked and the rest of the procedure can run.

[jonh]

error(number) returns a string not a boolean.

You also don't need the dir if you are passing in the path.



on error resume next
Kill Replace(currentproject.fullname, ".mdb", ".ldb")
Select Case Err.Number
Case 0 'No Error
Case 53 'File doesn't exist, continue with procedure
MsgBox "File Not Found"
Case 70 'File exist, deny access to the database
MsgBox "Permission Denied"
End Select

[Ringhal]

Thanks the code above works as I want to.
I will add the following in case none of the above errors occur.

Case Else ' Another error has occurred.
MsgBox "Error # " & Err & " : " & Error(Err) ' Display the error number and the error text.