Problems with Saving permissions

[PsYiOn]

The project i am currently working on is going to be used by multiple people as the same time. before adding a new entrie i have the workbook save to update the records already there and then save again after a modification.

here is the code i currently use

[vba]
Sub SaveUpdate()
Dim Wkb As Workbook
On Error Resume Next
With Application
.ScreenUpdating = False
For Each Wkb In Workbooks
With Wkb
If Not Wkb.ReadOnly Then
On Error Resume Next
.Save
If Err.Number > 0 Then

End If
If Err.Number > 0 Then
SaveUpdate
End If
End If
End With

Next Wkb
.ScreenUpdating = True
End With
End Sub
[/vba]

The problem iv been having is that not all the user entries are being added. It seems they are going into ready only mode or somthing and when the save/update is done the new entries from other users are not being shown...

Does anyone have an idea for a sub i can call before and after modifying data so i make sure the copy on the server is always up to date? Its possible 2 entries may be added at the same time so can a check be done to see if the file is writable and if not wait a few seconds uintil it is?

[mdmackillop]

If the shared workbook is for data storage, this will always be a problem. Have you considered saving to a database file?

[PsYiOn]

hmm would i need any other software? what type of database file did you have in mind?

[mdmackillop]

Do you have Access?

[PsYiOn]

I have access at home but on teh workstations that will be using the project they only have normal office apps eg excel,word,powerpoint. If i build the db at home can i use vb to interact with it? Can it handle multiple users at once?

[Bob Phillips]

You don't need Access, Excel can read and write to an mdb file without ACcess being installed.

[Bob Phillips]

Be aware that whilst Access databases can manage a (limited) number of multiple at the same time, there are other considerations. The most imporatnt is probably concurrent updates. Consider this sequence:
- user A retrieves record #1
- user B retrieves record #2
- user A changes the name and saves record #1
- user B changes the rate and saves record #1

The name change is lost and user A is none the wiser. You could lock the record when user A first retrieves it, but that means that no other user can access it, which is not good for queries. You could hold a timestamp that is written when it is saved, and the code then checks that the timestamp has not changed since the record was retrieved, and either abandons the update or telss the user to re-retrieve.

These are design considerations for you.

[marshybid]

Hi there, I don't know if it is an option for you, but if you are running Office 2007 (as we do here at work) then we use the MS Sharepoint service, you can share/edit documents across multiple users.

Essentially when one user is viewing/editing a document if anyone tries to access it they are advised that it is currently locked by user## and they can be notified when the document is available.

Also allows for version control to be stored automatically.

Not sure if this would be relevant for you.

Marshybid

[PsYiOn]

Actually all my users will be doing is adding entries, all the data collected will be then dumped in excel sheets tobe sent to the project team.

Will an access db have any issues with everyone adding entries at the same time? There will be about 20 people suing it. Adding an entry at most every 5 mins with the ocasional overlap...

[Bob Phillips]

20 usesr should just about be manageable by Access. Don't forget, SQL Server Express is free.

[Bob Phillips]

Hi there, I don't know if it is an option for you, but if you are running Office 2007 (as we do here at work) then we use the MS Sharepoint service, you can share/edit documents across multiple users.

Essentially when one user is viewing/editing a document if anyone tries to access it they are advised that it is currently locked by user## and they can be notified when the document is available.

Also allows for version control to be stored automatically.

Sharepoint is great, but the price is a bit rich for most small enterprises.

[PsYiOn]

20 usesr should just about be manageable by Access. Don't forget, SQL Server Express is free.

I had thought of using the free sql server but id need to get all sorts of permissions from the IT guys and a server too. This way i should be able to do it with a folder on the fileserver.

further to your post, what would you say is the absolute max user for an access database?