Creating Front end and Back end database

[sabareesh03]

Hello there!
I just joined my new job and the company is addicted to Excel though Access is efficient in few situations. I have 15 people reporting to me. They record their daily activity in an Excel sheet and mail it to me. These emails would spam my inbox. I am implementing Access and needs assistance from experts like you. I want to create a form for each individual i.e, 15 forms. An individual should have access only to his/her form (it would be better if he/she can see only their form rather than locking the other forms). In the front end I want the users to view only their related form and not the tables and reports. Can anyone help me in this regard? Please let me know if you were not able to understand my situation. Apologies for grammar.

Thank you in advance!

[colintis]

So are you saying each 15 users have different interface of the form? Do they actually store the same information at the end?

For splitting the front and back ends, you can first create all the tables and queries and forms you need. After testing the database to confirm its done, you can go to split the database, in Access 2007 its Database tools-->Move data--> Access database(a yellow database with 2 arrows pointing to 2 smaller yellow databases)

[NeoPa]

I fail to see how this is about FE/BE databases. It appears to me that you have design considerations as well as security ones. The idea of 15 separately designed forms seems an extraordinary concept, and won't give you any help with security if that's what you were thinking.

I would suggest sitting down first and giving close consideration to the expected structure and security issues that will necessarily be associated with this project. Don't expect a 5-minute job.

I'm sure we can help when you have a clearer idea what you are after, but I suspect without that your questions will be too ill-considered even to make much sense.

[sabareesh03]

thanks for the advice. Actually I have to create FE & BE database too but I wanted to clear with the forms first. Fifteen employees report to me about their daily activity in the form a Excel. I use it to evaluate their performance and company productivity. This is not a major responsibility so I update the master Excel sheet once in a week. This would spam or my inbox would be full by then. And also it is time consuming to copy the numbers from individual sheet to master workbook. A friend of mine suggested to implement Access by distributing individual form to each employee and it would automate the whole system.

Well as I said earlier I want to create a form for each employee i.e, 15 forms. They should be able to access just their form. If it is possible I want the front end to database to have only forms and all the related tables, reports in the back end.

Please let me know if this information is sufficient.

[Jerry Maiapu]

Hi, I see what you mean.
First you need to create the MS Database ie tables queries etc..+ plus at least one form say for the first user. Now Split the database. ( Remember the back-end location). Now for the front end, make 15 copies of ms access files and re-design each individual forms to meet the requirements for the front end user. (you can give the names of the employees for each of the files.
When finished put a copy of each of the front end in the corresponding officer's pc.

Note: that all of them (15 employees/front ends) a storing the data to the same back end.
Now for yourself, make another copy of the front end, redesign the form or create another form to view all the data entered by the 15 officers and so on.. . On your end you can create many forms to view the sort of records you want to view.

Hope this gives you an idea..

Passing by..


ask if any query

[munkee]

To be honest how different would every form be from the other? As you can save A LOT of time and be much more efficient in your design if they are quite similar to eachother with only a few text boxes or whatever difference. You would just need to create one file, and dependent on the logged on user hide/make visible certain controls on the form.

If you are able to can you post your excel file?

my 2p

[NeoPa]

sabareesh03:
A friend of mine suggested to implement Access by distributing individual form to each employee and it would automate the whole system.

I'm afraid it sounds like you may have misunderstood your friend somewhat (either that or they are not someone to take advice from). I would guess they were referring to multiple copies of the FE database being distributed rather than multiple forms designed specifically to match each user.

In truth, this whole project could be relatively straightforward (though I'm guessing Access is quite new territory for you) were it not for my suspicion that you would need some deeper level of security to ensure other users are unable to see the data. Normally such data is sensitive, and Access is generally quite easy to get into if all the data were stored together (as it would need to be of course).

I refer you back to post #3 where I suggest you sit down and give proper consideration to exactly what you want before proceeding. I don't think attempts to help you forward at this stage are any help to you at all (especially not related to designing multiple forms). They are more likely to lead you in an ill-considered, and for you inappropriate, direction.

[colintis]

Also some considerations you will need to make as well sabareesh03.

First off, if the 15 users originally don't have MS Access, you might need to think of installing a runtime version of Access for them, this can save cost for buying full version to every users as the run time is FREE. Second, make a MDE / ACCDE version of the database for the users' FE, this approach can prevent user modifying the original database you create (if user have a full version of Access).

Or....if you can create a database with nessary structure of tables, study some VBA and edit the excel file to store the stuffs into the database with just a button click ;)

[sabareesh03]

Well thanks all of you for your advices. I have created table, queries and forms for all 15 individuals. but i am not sure if this is the best way to save time and make work efficient. i also have issues with creating front end. I did create MDE/ACCDE version but how do I make changes to front end database so the individual can see only his form.

i did try to post my excel sheet but i dont see any option that I can use to attach the file.

I think thats the best idea to go through few books and decide. I have another issue now, I just took over new responsibility and the previous lady created a mess with V lookups which doesnt make sense to an important file. I feel Access would be better for that too. I would be happy if anyone could suggest me how to go ahead with these situations.

I may not choose right path, i am welcome for any suggestions like choosing a different application or any other method. Unfortunately I cannot post the original data because its confidential information. I could just give an example.

I will try to create the Access file from scratch considering all your suggestions and let you know if I have any issues.

[sabareesh03]

colintis:

Our company has Access so the cost may not be a issue. As you said, do you using VBA in excel would be better than Access?

[sabareesh03]

Sample

Here is the sample of my work sheet

[NeoPa]

Sabareesh03, I think you may be struggling to understand some of what I was saying within all the various comments.

I'll say the main point again on its own so it's clearer to understand :
Instead of various different forms you should just design one that can be used for all users.

This form would show only the data relevant to the individual when they run it, whoever runs it, but it's always the same form used. Does this most important point make sense?

[colintis]

Sabareesh03,

If so then you will need to make the second consideration I mentioned earlier, creating MDE / ACCDE version of your database to prevent unnecessary changes from users. You can search for related topic around this site, there are plenty of references in here.

I would not guarentee much on using Excel's VBA to perform data transfer as users may change something without notice, and since you are new to database design I would not suggest you step into this idea. Cause it will make you more confuse to understand how it work.


Here's a website that I think it may help you understand how a database can be design.
CLICK HERE. You don't need to read every single points but look into ones that explains with pictures.

From your design, as NeoPa said, you can just design one form in which all 15 users using the same structure and format. To different out 15 users, you can make something to identify the user, and the same to data table(s).