Howto trap " Cannot open the file"?

[DataMiner]

How can I trap the error "The Microsoft Database Engine cannot open the file 'MyDb.mdb'. It is already opened exclusively by another user, or you need permission to view its data".

I have an error handler in my routine, but it does not get called at all when this happens. Instead, I get a msgbox with the text shown above, which of course just hangs up everything until I click OK.

 

[ghudson]

Does your error handler look simular to this? You need the runtime error number Then you can trap for that specific error number.

Public Sub OpenDatabase()
On Error GoTo Err_OpenDatabase
 
'your code here
 
Exit_OpenDatabase:
    Exit Sub
 
Err_OpenDatabase:
    If Err.Number = 1234 Then
        MsgBox "Database is in use.  Try again later."
        End Sub
    Else
        MsgBox Err.Number & " - " & Err.Description, vbCritical, "OpenDatabase()"
        Resume Exit_OpenDatabase
    End If
 
End Sub

 

[DataMiner]

Yes, I have an error handler. But it does not get activated, I just get the msgbox with the error description. If I then ask for an error number ("?err.number") I get nothing. So this seems like one of those "untrappable errors". Looking for a way to trap it.

 

[DataMiner]

Here is my code. I'm getting the error on the line

.Execute "AssemblyMaster_Refresh", dbFailOnError

Function ImportNewQAData() As Boolean
Dim DB As Database, varreturn As Variant
Dim WK As Workspace, Trans As Boolean
Dim y As Long, x As Long
Dim strResult As String, MyError As Error

If errorhandlingon Then On Error GoTo HandleError
ImportNewQAData = False
Set DB = CurrentDb
Trans = False
x = 0
Set WK = Workspaces(0)

logerror "starting ImportNewData"
'--------added 6/30/09 to get Kevin's data into SQL server
setstatus "Moving raw QA data into SQL Server"
With DB
WK.BeginTrans
x = 1
Trans = True
.Execute "delete * from DefectDataforTool", dbFailOnError + dbSeeChanges
.Execute "DefectDataforTool_Refresh", dbFailOnError
WK.CommitTrans
Trans = False

WK.BeginTrans
x = 2
Trans = True
.Execute "delete * from YieldDataAllforTool", dbFailOnError + dbSeeChanges
.Execute "YieldDataAllforTool_Refresh", dbFailOnError
WK.CommitTrans
Trans = False

WK.BeginTrans
x = 3
Trans = True
.Execute "delete * from productcodes", dbFailOnError
.Execute "ProductCodes_Refresh", dbFailOnError
WK.CommitTrans
Trans = False

AssymasterRefresh:
WK.BeginTrans
x = 4
Trans = True
.Execute "delete * from AssemblyMaster", dbFailOnError + dbSeeChanges
.Execute "AssemblyMaster_Refresh", dbFailOnError
WK.CommitTrans
Trans = False
Imports:
'--------end of 6/30/09 addition
x = 5 ' added 11/24/09 to prevent endless loop when importqadata fails!
'SQL SP:importqadata
setstatus "Processing QA data in SQL Server"

strResult = SQLTransaction("ImportQAData")
If strResult <> "OK" Then GoTo HandleError


setstatus "exporting data to PCA2k"



strResult = SQLTransaction("ImportQAData_FinalStep")
If strResult <> "OK" Then GoTo HandleError


strResult = SQLTransaction("DataIntegrityErrorsAppend")
If strResult <> "OK" Then GoTo HandleError
End With

logactionsqlserver "LastImportTry", CStr(DMax("date", "Attributeyields", "panelid>1000000000"))


clearstatus



DB.close
WK.close
ImportNewQAData = True
logerror "Completing ImportNewQAData"

GoTo CloseAll
'----------------------
HandleError:
'added 11/16/09
For Each MyError In DBEngine.Errors
strResult = strResult & " , " & MyError.Number & " " & MyError.Description
Next MyError

logerror "ImportNewQAData", strResult, Err.Number
'end of 11/16/09 addition

logerror "ImportNewData", Err.Description, Err.Number 'added 5/14/07

varreturn = SysCmd(acSysCmdClearStatus)



Select Case Err.Number

Case Else
If Trans = True Then WK.Rollback
MsgBoxAutoClose "Error while attempting to import new data" & vbCrLf & _
"PLEASE RECORD THE FOLLOWING INFO AND CALL VICKIE AT 5030" & vbCrLf & _
"Error #" & Err.Number & ": " & Err.Description, , 20000
If x = 3 Then GoTo AssymasterRefresh
If x = 4 Then GoTo Imports

End Select

CloseAll:
'Set RS = Nothing
Set DB = Nothing
Set WK = Nothing
'End Select
End Function

 

[NigelShaw]

Hi,

you might be able to find something in the attachment


regs


Nidge

 

[DataMiner]

No such luck. Several reasons:
1. It's not that I don't know the error code. It's that the error handler is not firing at all. I just get the Access-generated msgbox. THe code never gets to the "HandleError" section.
2. I am using Access 2002.

 

[DataMiner]

OK, I have to append my question. In the code shown above, I first get the error at

.Execute "ProductCodes_Refresh", dbFailOnError

with Error # 3051. My error handling routine works as expected and returns the code to the AssemblyMasterRefresh line. THen the code fails again, on

.Execute "AssemblyMaster_Refresh", dbFailOnError

But THIS time the error handler does not fire, I just get the Access generated msg box.
How do I get the error handler to fire also on this second error?

(BTW, I know what's causing these errors, it's a file permissions issue that I have little control over. I just want to have a graceful way of handling this intermittent problem)

 

[NigelShaw]

Hi

i havent looked at this much but i am going to a probably stupid question. you have a line

If errorhandlingon Then On Error GoTo HandleError

so,
is "errorhandlingon" well, on or is it turned off?

regs


Nidge

 

[gemma-the-husky]

i see now

after the first error you HAVE TO RESUME, not GOTO the label

error handlers are not re-entrant and do not reset UNTIL you issue a resume statement or exit the procedure.

 

[DataMiner]

Gemma / Dave--- THankyouthankyouthankyou! THis is one of those problems that I foggily remember having dealt with years ago. The OLD Vickie was so much smarter than the current one!

 

[gemma-the-husky]

i thought you were saying you werent seeing ANY errors, until I read it carefully

its easily done - using a goto instead of a resume, and hard to trace, as it compiles OK