Opening tables for important database

[SemiAuto40]

Somewhere I vaguely remember reading about using VBA to open my tables for an ACCESS and manage all of the datasets with VBA so to control record locking and preventing problems. It is easier to let ACCESS do it by following the wizards but this project is going to be important for my career. I can't remember where I read this, or even if it is true... but if 100+ users all over start to use this DB and it crashes - then I'm toast! What do you guys think that have working databases with this many users? Should I rely on VBA and record lock and prevent crashes by code OR use ACCESS built in Forms, Tables?

[SemiAuto40]

Is this a dumb question or does it make no difference?

[pbaldy]

It's not a dumb question, and it can make a difference. I assume you're talking about using bound vs unbound forms. I've never had to worry about that many users, but from what I've heard it could be pushing the limits of what Access can handle. What are they all doing? Running reports is easy, editing data provides much more opportunity for conflict. Data entry isn't too bad. You might consider a more robust back end for the data. There is a free version of SQL Server (Express), though I don't know its limitations.

[AccessMSSQL]

You should store your data in a separate database (either Access or SQL Server) and have a separate front-end database with linked tables.

Here is a good article that might help:
http://www.opengatesw.net/ms-access-...-Practices.htm

Other tips:

To deploy the database to 100+ users if using Access you should create an ACCDE (compiled version) (if using Access 2007 or 2010) and release to each individual user. Users should not access the same front-end. They should have their own local copy of the front-end application. You can control version releases through a batch file on users desktop or have their login script automatically grab the latest version each time they login to windows.

You can use temporary tables in your database if you are concerned about record locking. Have all user forms set up as read-only with an EDIT button. When the user clicks Edit you can use VBA to load the live record into a temporary table, change the allowedits of the form to true and set the recordsource of the form to the temporary table. If the user clicks SAVE you save it back to the database and set the form back to read-only while changing the recordsource back to the live table. Temporary table should have exactly the same columns as your live table - except for your primary key. Make sure any integer-autonumber primary keys are just set to integer in the temporary table. You will need to load the actual ID of the live record in that field so you have a reference when saving back to live table.

You can also have a field set up in the live table called "CheckedOut" or something and when any user EDITS a record the flag will be set. Don't let other users edit a record that is checked out. When user saves the record the flag is un-set. You will need to write some VBA to do this kind of validation.

Hope this helps. If you need specific examples let me know.

Also SQL Server Express data limit for one database is 10GB (I think it's only 4GB if using SQL Express 2005 and previous)

[SemiAuto40]

"Hope this helps. If you need specific examples let me know."

Yes it does help greatly! If by specific examples you mean sample DBs where this is being done - then YES, it would be beneficial to me to be able to look at the structure and coding behind the curtain.

Thank you all for very important help.

[AccessMSSQL]

Give me a bit of time and I will post a sample database for you with one form that does the above mentioned types of operations.