Sharing Access file (1 Viewer)

[Rakesh935]

Hello,

Well I want to create a access file which would happen to be one of the database for my organization.

My question is which way would be the best way to share the access file among multiple users.

I tried doing split database but unfortunately i figured out, that same record is reflecting for update when multiple users are working at the same time.

Requesting your help in creating the database.

Thank you,
Rakesh

 

[Pat Hartman]

Splitting the database is the correct solution. Each user should have his own personal copy of the FE which is stored on his local CL drive. The shared BE is stored on a server and all the FE's connect to it. It is very unusualy that everyone would need to update the same record at the same time. Can you explain to us what the application does?

 

[Rakesh935]

An access table having more than 70,000 records (line items), needs to get processed. In order eliminate scrolling of data I executed a SQL query which will show only 1 record (line item) at a time, once after processing the record and after clicking refresh the next new record will reflect.
Apart from the data in the table I have added another two more new columns in the table (i.e. User name – which is the system name and Date of process - system date) which will be capturing automatically. Additionally, considering huge volume the access file will be shared between at least 20 people simultaneously to be worked.
Hence, the requirement is to capture who has worked in what record and on what date plus all people working in the file must see one record only (any 2 or more persons mustn’t see the same record) and after processing the record the user must click the refresh button to get a fresh record for process.
Requesting to guide me in order to create the tool.
Note:
The new record showing criteria would be – show next record for which User name is blank.

Hope this clarifies.....

Thanks,
Rakesh

 

[Pat Hartman]

I had to implement something similar. There are a few pieces to the process.
1. You need to add an "edit" button to your form. This button would modify the LockedBy and LockedTime and force the record to be saved immediately. This essentially locks the record. You can lock/unlock all the controls or use the AllowEdits property to control this or you can trap the update in the form's BeforeUpdate event and disallow it if the user didn't press the "edit" button first.
2. Queries that select records for the work queue need to look for null in the "LockedBy" field.
3. You need an update query that will clear all locks by setting the two fields to null or clear all locks older than some date. This will handle the situation where the user starts editing a record but never actually saves it.
4. In the form's BeforeUpdate event, you need to clear the LockedBy and LockedTime fields.
5. In the form's AfterUpdate event, you should reset the "edit" button and relock the form if that is the method you choose..

 

[Rakesh935]

Hello,

Been trying to get the force save done....but so far been a good looser in doing it.....i am really confused how to proceed further......

Attached is the access file which i have done so far, requesting to please have a view of it and guide me for what else do i need in order to get it work.

Thanks
rakesh

 

[Pat Hartman]

I don't recall saying anything about putting code in the .dirty event of a control. The point of the edit button is that the user "tells" you if he wants to edit the record and then you lock it. If you prefer to not use the button, then you could use the FORM's Dirty event which will fire if he dirties any control. Your method will require code in EVERY control since you have no idea which control he might decide to start typing in.

 

[Rakesh935]

Oh God!!!

Before starting up this tool i thought this would be a cake walk but now m realizing its getting more and more complicated :banghead:.

Anyways, one last help....is it possible.....to make multiple users see one unique record i meant if 10 users are using the copy of the tool at the same time then they must see only one unique record for process and subsequently two or more users mustn't see the same record for e.g. if "A" is processing a record then "B" shouldn't see the same record for process which is "A" is processing or vice-versa.

In other words the records must get locked under the particular user name.

If this can be possible then what should be the VBA code for it and where it should get executed.

Please help me for the above requirement.

Thanks
Rakesh

 

[Pat Hartman]

Go back and read post #4 again.