Restricting Multi-Users in tables 1

[jp1]

This is a general question that may have been answered previously, but, I have 8 users in my Access Database. I can split my tables so that only 2 users have access to a table and thereby limit the errors encountered when the records are locked. I do have 2 users that need access to all the data and one routine that requires access to all data. Can I write my code so that the forms only pull one record from the table and then close it on exit? This would eliminate the Write errors and limit access to the entire file. The one routine that requires access to all data is simply to verify the 4 basic fields in the record for indentification. Is it possible to create a second table that is a summary of all entries in the Main tables (4 basic fields) and then have them updated automatically as new data is entered the the main tables? This program is for Time tracking on a number of jobs (over 2000) and needs to be accurate and fast as we try to keep current on posting time entries for 75 employees. How about a few pointers. I have not written any code as yet, since I got this far with just Wizards and Autoforms. The only code I have is for Command buttons, but I do have a basic understanding of VBA. [sig][/sig]

 

[Phooey]

Why not just use record locking as opposed to table locking?? For data consistency, you could try Pessimistic locking..

[sig]<p>Phooey<br><a href=mailto:Andrew.j.harrison@capgemini.co.uk>Andrew.j.harrison@capgemini.co.uk</a><br>Otherwise known as Windy Bottom.[/sig]

 

[MichaelRed]

jp1,

There are a number of ways to help with your problem. First, look at:

Why AutoNumber shouldn't be used in MultiUser databases (And How to ge the Unique Number)

in The Access Tables and Queries FAQ. Pay particular attention to the part where the user system does the Re-Tries on the locked record. Doing this (Automatic Re-
Try) should allow you to have access to your data tables by all users w/o any other restrictions.

Second, Take a look at your datatables and see what size they are. Some literature &quot;suggests&quot; that you artifically make the record sizes of critical tables more than 1/2 of the block sizs (2048 bytes? ). This previents Ms. Access from locking more than one record, and thus alleviates the problem (if you use record level locking).

Thrid, Have a local table in the front end data base. Have the Forms place data in this local table and - at the completion of the data entry process, do any validation on the Local table. If/When the validation is passed, do an append from the local table to the backend table and delete the entry inthe local table.

There are other techniques, but this should get you started.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]