Check for Table Lock

[Maker1]

I am using Access 2003, I have a make table query that creates a table in another database for that database to reference. Occasionally, I just get the table structure, not the data. I am assuming that the problem is that when the event to run the make table query is tripped, the table the data is coming from is locked. Is there a way to check ahead of time to see if the table is locked? Somthing I can put in VB so if it is locked I don't run the make table query?

 

[Shawn12]

You just need to do a Find in VBA for the .ldb in the folder where the .mdb is. And if it finds it then have it not run the Query. something like this

Set fs = Application.FileSearch
With fs
.LookIn = "C:\My Documents"
.FileName = "cmd*.*"
If .Execute > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

 

[Maker1]

Thanks for replying. Here is the deal. I am trying to make a table that has a list of all of our employees in another Database. So I run a make table query on OnClose of the form where data on the reps is changed. The problem I am finding is the data in the table I am making ocasionally comes up empty. I don't know what is causing that.

 

[OhioSteve]

You could use something like Shawn12's approach to learn whether an .mdb was open. If you didn't nuke the table when the .mdb was open, that would improve your situation somewhat.

However, I suspect that you are be overusing the "make table" feature. Perhaps you should consider linking the tables. That is the most common way to move data between two .mdbs.