how can I handle forms and databases in a network with vba

[filotei]

Hello to everyone
I have just discovered the site I am glad to find that there is a place where you can get advice from experts. I am quite new in VBA Excel programming and I feel that there are a lot to learn...
I try to develop an application but I face some problems and I would much appreciate your help. I have two workbooks: book1 and book2. The both books are located on a public server. Book1 is shared for evey user in the network and contains a form destinated to gather some data in a database in book2. The user opens book1, fills in the form, click on submit and automaticaly the book2 is open, write down the data in the database, save the changes and then close the book2.
As the application will function in a network there is a possibility that two users will click "submit" button in the same time. That's why I created a function that first checks if the book2 is open and when the book2 is not open runs the rest of the code. If the book2 is open somewhere in the network, and the "submit" button was clicked by an users the programs wait until the book2 is closed. Very fine! But if the user makes some modifications in the book2 and saves the changes while "submit" button is pressed waiting the closing of book2, then after the closing of the book2, the book2 can not be open anymore - error 1004, it seems that the book2 has another path or name. I can not understand why because it functions very well when there are no changes in the book2.

I send bellow the piece of code:

Private Sub Submit_Click()
 
Do
counter = counter + 1
Application.StatusBar = counter
Loop Until Not FileAlreadyOpen("book2path") 'loop until the book2 is closed


Application.ScreenUpdating = False ' turn off the screen updating

Workbooks.Open FileName:="book2path", Password:="x", WriteResPassword:="x" 'in this line, there is the error 1004 - the path is the same, the program understand the book is closed and then can not open it anymore

'do somehing
End sub
 
Function FileAlreadyOpen(FullFileName As String) As Boolean
Dim f As Integer
    f = FreeFile
    On Error Resume Next
    Open FullFileName For Binary Access Read Write Lock Read Write As #f
    Close #f
    ' If an error occurs, the document is currently open.
    If Err.Number <> 0 Then
        FileAlreadyOpen = True
        Err.Clear
    Else:
        FileAlreadyOpen = False
     End If
End Function

Any suggestion or advice will by higly appreciated.

Thanks.
Florian