Test for Table Locked or Open

[cavscout]

I have put simple error trapping into a module that performs some updates to access tables from Excel spreadsheets. I have trapped if the table doesn't exist but now I need to trap if the table is locked or open. Can some one share the syntax for this Test.

Someting like this is what I'm trying to do:

If

locked or

Open then
Email - "Update failed due to

Locked or Open
Else
Resume Exit Function
End If

If I've been unclear about anything please advise

Thanks in Advance

 

[The_Doc_Man]

Sadly, there is no easy way to know that a table is locked at the moment. You find out a table is locked only when you get kicked in the butt by the error that blocked something you tried to do.

There is an OnError event that applies to forms that might be useful, or if you are exclusively in VBA code you can do an On Error GOTO [statement-label]

In the error handler (because that is what you are writing when you do this), you need to look at the error that came back. So look at the help on the Error object, or look at the Err() function that tells you which error you just got when you tried to do something. Also look at the help files for error handling routines in general.

Basically, once you trap the error, look at the error number. If it is a number you want to handle, do whatever you needed to do and resume the program. If it is a number you cannot handle, you have to resignal the error and let Access notify you using the normal handler routine.

Structurally, it kind of looks like this....

Error_Holder = 0
On Error GOTO MyBad 'protect the following code

'do your thing

On Error GOTO 0 'release protection
GOTO Protected_Thing_Done

MyBad:

'get the error code from the Error object

Error_Holder = Error.Number 'this is one possible issue

Resume Protected_Thing_Done

Protected_Thing_Done:

If Error_Holder = 0 then
' do this if it worked
Else
' do this if it did not work
End If