Creating access database

[Mohnishkumbhar]

Hi..I have a team of 150 associates and I want to create a access database where I will get input from all of them and it should get collated in one access file on real time basis.. Please assist

[June7]

Review http://www.rogersaccesslibrary.com/

Split the database. All users are on same network? Data backend on server. User interface frontend copied to each user workstation, just like any application software.

[Mohnishkumbhar]

Hi June7.. yes all users are on same network.. but I am very new to the ms access.. didn't know how to split and create user interface

[June7]

Splitting is just two files. The tables are in one (the backend) and the other (frontend) has links to backend tables. Also has queries, forms, reports, code.

The backend can be Access, SQL, Oracle, etc.

[Mohnishkumbhar]

Thanks June7... I was able to split the database. But now I want to protect the frontend database so that no can delete it. And one more question.. at time 150 users can enter data in the frontend database right..

[CJ_London]

But now I want to protect the frontend database so that no can delete it

each user has their own copy on their machine. Since it is a file, it can be deleted, but it will only affect that one user. There are things you can do to ensure that a deleted file is replaced but that relies on a shortcut and the user could delete that as well.

And one more question.. at time 150 users can enter data in the frontend database right..

Technically yes, the maximum number of concurrent users is 255. But as stated above you should have each user have their own front end. If you are referring to the backend, all your users would be accessing that so that is where you should be considering. However whether this will provide an acceptable level of performance is another matter. Among other things, it will depend on the number of users connected at the same time, whether they are all trying to view/amend the same tables/records at the same time and how well the db is designed. Personally I have db's running perfectly well with circa 50 concurrent users, but would consider using a SQL Server or MySQL backend for significantly larger numbers of users.

[Mohnishkumbhar]

Thanks Ajax.. as you said "each user have their own front end" means I can create copy of the same frontend file and give it to the all users.. that will work..correct. My frontend and backend file are both Access. At the same time they will be entering new data or new records... please assist..

[CJ_London]

what do you want to know?

In general:

1. ensure your table designs and relationships are properly normalised and defined

2. ensure you have indexes in place for all fields used in relationships and fields which will commonly be sorted or filtered on.

3. try to avoid query criteria that require use of an initial '*' - e.g. Like '*something' or Like '*something*' these wont use indexes so are slow - train your users to enter the initial '*' if required. Proper table design should also help - e.g. don't have a field 'customer name' which is populated with say 'Harry Jones', have two separate fields for firstname and lastname.

4. In your form designs - ensure you have these set to be as 'focused' as possible for the task in hand - e.g. if users are only viewing data, ensure the recordset type is set to snapshot, and allow additions, allowedits and allowdeletions is set to false. If they are adding data, recordset type is set to dynaset, allowedits and dataentry are set to true etc.

5. design the recordsources for your forms to get the minimum amount of data both in terms of 'width' (the columns you bring through) and 'depth' (the number of rows). So forms with just a table as a recordsource or a query like 'SELECT * FROM myTable' are a big no no. Use a query like SELECT fld1, fld2, fld5 FROM myTable WHERE ID=1". 'SELECT fld1, fld2, fld5' keeps the width tight and 'WHERE ID=1' keeps the depth tight

6. in table design, don't use lookup or multivalue fields - these won't be indexed, impact on the data volume being brought through and have a severe effect on performance. - with 150 users I guess you have a lot of data

7. ensure backend is compacted on a regular basis

8. ensure network is optimised for your application (talk to your IT people)

9. ensure each front end maintains an open connection which actions are being taken - i.e. if the front end is idle for a period of time (say 5 minutes, but depends on what the app is doing), close the connection and reopen when the front end becomes active again

10. As you implement the above test the system in a realistic simulation (i.e. backend on the network)

Other things - distribute the front end as an accde and hide navigation options- users can still mess with queries but cannot touch the code

If you need anything more specific, please provide a full description of what the application is required to do - also please be aware I'm busy with other things so will not be able to always respond quickly.

[orange]

More info and tips (FMS)
http://www.fmsinc.com/tpapers/faster/
http://www.fmsinc.com/microsoftacces...ddatabase.html

Good luck.

[Mohnishkumbhar]

Hi Ajax.. Thanks for sharing detailed overview. Below is the full description of what I need to do.

I have a team of 150 associates and on a daily basis they send their work status manually in a excel sheet or in a image form. Their status have only 5 columns. So What I need to do instead of sending status manually they should enter their details in the Access database and I should get the collated data in the backend sheet. So I have created on split database with that 5 columns.
So wanted to check if this is enough to get their daily work status without any interruption. Please advice. Thanks

[June7]

Why 5 columns? If each record can have only one status then should be one column with 5 choices. If this is to document status of various stages of progress, then are these date/time type?

[Mohnishkumbhar]

Hi June7. Let me explain in more detail and the data they are going to enter..
So for example.. one associate is sending his work status having more than 20 records and this 20 records have their sub details and this is fall in other four column. Below is the snapshot what they send usaually manually and now I want them to update in the front end access database.

Sr.No	Emp ID	Case Id	Status	Date
1	414181	8485785478	Done	Current date
2	414181	8485785478	Done	Current date
3	414181	8485785478	Done	Current date
4	414181	8485785478	Done	Current date
5	414181	8485785478	Done	Current date
6	414181	8485785478	Done	Current date
7	414181	8485785478	Done	Current date
8	414181	8485785478	WIP	Current date
9	414181	8485785478	WIP	Current date
10	414181	8485785478	WIP	Current date
11	414181	8485785478	WIP	Current date
12	414181	8485785478	WIP	Current date
13	414181	8485785478	WIP	Current date
14	414181	8485785478	Done	Current date
15	414181	8485785478	Done	Current date
16	414181	8485785478	Done	Current date
17	414181	8485785478	Done	Current date
18	414181	8485785478	Done	Current date
19	414181	8485785478	Done	Current date
20	414181	8485785478	Done	Current date

Case ID will differ on every row. So like this there will be 150 associates who will be entering there status on real time basis in front end database. Let me know if this answers your questions

[June7]

That does clarify and the structure looks good. Access should be fine as a user interface for data submittal.

[Mohnishkumbhar]

Hi June7..I want to know that this will not get crashed or messed right, if all associates enter data on the same time or real time basis. So I have splitted the database and backend is on my personal drive and frontend is on the network drive and all associates will be going to update their data in that file saved on the network.

Let me know if Anything else I can do for more smoother process.

[June7]

The backend should be on network.

A multi-user split database frontend should be treated like an app. Each user should run their own copy of the frontend, not a shared copy. Just like each user runs their own installation of Word, Excel, etc.