I would like your opinion on this; I have a ms access database that will be used in a multi-user environment, across different Teams. Data will be loaded from text files several times a day in a MS Access table, users will retrieve the data from the tables and displayed in a form. Their job will be to add comments and select drop down boxes for actions they have taken. Since the data is downloaded in the tables and retrieved from the user instead of the user keying in the data from scratch I would like to know how to best design this. I’m thinking of having a query run to display the data in the user form but is there a way that I can have the data refreshed every so often so that users can be presented with the new records that are being downloaded. What best practice should I use to avoid conflicts like example splitting the database or using record locking options
Are you thinking about using Access as the front end (presentation layer,) as well as the back end (data storage layer) ?
By all means, if multiple users will be accessing the data, at the very least, split the database. IMO, it could make a lot of sense to use an MSDE or SQL Express database as the back end, and link the multiple Access front ends to the server-based back end.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert
There is two ways:
1-create user level security which means after completion of database for each user you define user name and password and also define the access level for it, then you should put the database on server and share it for users.
each user open it with its user name and password and has limited access to which is defined.
2-use front-end , back-end method, in this method after you complete your database then MS-Access splits the db to two parts, first part is front-end which contains the codes,forms,queries,ect and the second part is Back-end which only contains Tables.
in this method you can define for each user a different front-end to limit his/her access to data.
the back-end will be installed on server and each user will get his own front-end which is linked to the only back-end on server.
I recommend to use the second method as it doesn't affect the server speed as the main part of database (front-end) is on the user computer and it doesn't use server for loading as well as data communication is very faster.
for your information User-level security method is no longer supported in MS-Access 2007 version.