Swoop
09-22-2011, 09:03 AM
I have a excel workbook acting as user interface to another excel workbook acting as a database. Multiple users will open the interface workbook as a read only xls and write to the database xls (reason not using access is long and complicated but logical).
It does this like so:
Sub saveDB(testno As Integer, systemno As Integer)
ActiveWorkbook.Unprotect
Application.ScreenUpdating = False
Dim user As Integer, path As String, countryscore As Integer
Sheets("system").Visible = True
Sheets("System").Select
Cells(18, systemno).Value = Cells(18, systemno).Value + 1
user = Range("D18").Value
path = Range("D14").Value
Sheets("system").Visible = False
' IsFileLocked function, see below
Do Until IsFileLocked(path) = False
Loop
Workbooks.Open (path), Password:="password"
Sheets("MAPSdb").Select
Cells(user, testno).Value = countryscore
Application.DisplayAlerts = False
ActiveWorkbook.Close savechanges:=True
Application.DisplayAlerts = True
End Sub
And I have a function to test if another user is writing to the database workbook:
Function IsFileLocked(path As String) As Boolean
On Error Resume Next
Open path For Binary Access Read Write Lock Read Write As #9
Close #9
If Err.Number <> 0 Then
IsFileLocked = True
Err.Clear
Else
IsFileLocked = False
End If
On Error GoTo 0
End Function
This works perfectly in testing. However, in a real environment, with 15 or so users running the app, very occasionally (unable to replicate) the database workbook somehow gets deleted. I have absolutely no idea why though cos, according to Google, no-one else in the history of ever has had this problem.
Current thinking is either: Could it be something to do with DHCP leases expiring at the wrong moment? I have no access to the operational environment in question but other things that have happened lead me to believe the DHCP lease time is set at 1 hour.
Or.....some joker is deleting the workbook manually. Although that would be a neat trick as the users are aged between 6 and 11 and it's a hidden file.
It does this like so:
Sub saveDB(testno As Integer, systemno As Integer)
ActiveWorkbook.Unprotect
Application.ScreenUpdating = False
Dim user As Integer, path As String, countryscore As Integer
Sheets("system").Visible = True
Sheets("System").Select
Cells(18, systemno).Value = Cells(18, systemno).Value + 1
user = Range("D18").Value
path = Range("D14").Value
Sheets("system").Visible = False
' IsFileLocked function, see below
Do Until IsFileLocked(path) = False
Loop
Workbooks.Open (path), Password:="password"
Sheets("MAPSdb").Select
Cells(user, testno).Value = countryscore
Application.DisplayAlerts = False
ActiveWorkbook.Close savechanges:=True
Application.DisplayAlerts = True
End Sub
And I have a function to test if another user is writing to the database workbook:
Function IsFileLocked(path As String) As Boolean
On Error Resume Next
Open path For Binary Access Read Write Lock Read Write As #9
Close #9
If Err.Number <> 0 Then
IsFileLocked = True
Err.Clear
Else
IsFileLocked = False
End If
On Error GoTo 0
End Function
This works perfectly in testing. However, in a real environment, with 15 or so users running the app, very occasionally (unable to replicate) the database workbook somehow gets deleted. I have absolutely no idea why though cos, according to Google, no-one else in the history of ever has had this problem.
Current thinking is either: Could it be something to do with DHCP leases expiring at the wrong moment? I have no access to the operational environment in question but other things that have happened lead me to believe the DHCP lease time is set at 1 hour.
Or.....some joker is deleting the workbook manually. Although that would be a neat trick as the users are aged between 6 and 11 and it's a hidden file.