Records are Locked

[Alireza355]

I have developed an accounting database, with a lot of tables, forms, queries, reports, etc.

I have a form, which collects some information from the user, runs lots of queries, updates, etc in the main table of information, and fills another table by the selected records after deleting everything in this new table. The records in the table are then shown in a subform within another form.

My problem is that when a user is inside the second form, actually seeing the records in the newly filled table, other users on my local network can not do the same thing, because access gives an error, saying someone else already is using that particular table in exclusive mode (or something like that)...

The subform does not allow any edits or deletions.

Is there anything I can do so that different users can have access to that table at the same time?

[DonRayner]

Hello Alireza;

This is what I'm getting from your post.

1. User opens form 1 and enters some data.
2. User initiates VBA code to run queries and open form 2
3. Queries use data from form ???1 to create or update a table
4. Form 2 opens and within it is a subform with the data from the new table.

Let me know if I'm correct in my assumptions and could you answer these questions for me.

What exactly do you mean by?

and fills another table by the selected records after deleting everything in this new table.

Is the new table being created or updated?
Is your database split into a frontend and backend?
Are you using Access built in security?
Are you using optimistic or pessimistic record locking?

Don Rayner

[Alireza355]

Dear Don,

Thank you for your time.

Let me explain what is happening in my accounting project.

The user first enters RAW (uncalculated, mixed) data in the main table of information (Table1 for example) using a form (Form1 or MainForm for example). This is a huge table with a lot of fields (Table1).

Now, it is time for the user to select what accounts he wants to look at, and opens a form (Form2 or frmSELECT for example) to specify what he wants. He fills some comboboxes, and there is a command button in this form, that runs tens of queries, including delete queries which first delete all the previous information in Table2 (somehow a temporary table), then other queries run to make a lot of calculations, updates, sortings, groupings and so forth.

So until now, the purified information are appended to table2 from Table1. Now it is time for another form (frmFINAL for example) to show the contents of table2 in a subform, so that the user can see and if he wishes, print a report by a command button in this form.

Now, the point is, that if another user in my office network wants to look at his own desired information by opening the frmSELECT and clicking on the said command button, he gets an error message, indicating that another user has the same table or recordset open. This is perhaps because the information in Table2, are already been looked at in the subform of frmFINAL of the first user's computer screen.

The information in Table2 will or should, according to my consumptions, be now deleted, and filled up by the results of the queries that the second user initiates. but microsoft access prevents this by some sort of "Lock"ing.

Is there anything that can be done, so that the information shown in the subform of frmFINAL are threated as a clone of what was in Table2 at the time frmFINAL was opened? so that if other users try to delete all the information in Table2 and fill it with their own information and see it in their own frmFINAL, nothing prevents this?

I hope I could make myself undrestood.

Thanx a lot.
Alireza355

[DonRayner]

Your best bet would be to split your database into a front-end(fe) and back-end(be). Have your forms, queries, reports, modules and Table2 in the fe and the remainder of your tables in the be.

Leave the be on your server and install a copy of the fe on each workstation that will need access to your database.

Access has a tool for splitting your database into fe and be. It is located at Tools-Database Utilities-Database Splitter. Make sure to backup your database before running the utility.

See This Post by NeoPa for further information on splitting a database.

[Alireza355]

Dear Don,

Thank you for your kind help.

I will check to see how to split the database.

Will ask you if any help needed.

Thank you so much.