1

I was asked to created a program to calculate commissions for employees and supervisors (% of employees commission) based on sale logs that the employees would input into a "form". Should I use Access to build this? Multiple users will be using the same form during the day (inputting their sales) at the same time. I also need to make sure that they can't see any other form, report or table than the ones I want them to see. Each user needs to have their own log in.

Side questions as I work with this terminal: Access 2007 is extremely slow when I'm in anything else other then design view. Why would that be?

Michael J. Barber
  • 24,518
  • 9
  • 68
  • 88
Tolure
  • 859
  • 1
  • 14
  • 34

5 Answers5

3

Yes it could be done in Access, but what you really need is better management - at least management that understands the scope and scale of what they are asking you to do.

Managing a private spread sheet vs a database application where various users have different authorizations is a whole different kind of thing. Just tell your management: No - it shouldn't be done.

All you need is one smart salesman with an eye to gaming the system and I am willing to bet the whole thing will go up in smoke (possibly taking you with it)

Until you understant the following cartoon plus a few other things, don't take this project on: enter image description here

NealB
  • 16,670
  • 2
  • 39
  • 60
  • I like the cartoon. XKCD is great. This is a small time company and I'm hoping this would be a great place to learn how to work with Databases in the real world. This isn't for more then 20 people. I just need to know where to start looking for information. I'll gladly do input validation but I need to know where to start. I think I've built a simple architectture but without knowing how to deploy parts of it and request passwords without them having to open the development part of Access. I hope this makes a little sense. – Tolure Dec 05 '11 at 22:31
  • Cute, but I am not aware of ANY example code of a SQL injection for Access, and one reason for this is JET cannot accept multiple line sql statements. – Albert D. Kallal Dec 08 '11 at 00:36
  • @Albert Maybe not quite like the cartoon but Jet is still open to some forms of SQL injection [see this SO answer](http://stackoverflow.com/a/522382/192510) (call it predicate injection) – NealB Dec 08 '11 at 03:00
  • Sure, ok Fair Enough ;-) SQL "injection" usually refers to that of a user injecting SQL code to be run. I suppose this an issue of semantics and I see that example as different issue. Simple asking a user for what city and allowing * or the user typing in some other wild card like Edmon% is often allowed and even encouraged. I have lots of prompts that allow * as wild card choice. So wild card conditions IMHO is not an true SQL injection in the true sense. Some may see this different but you cannot inject NEW sql statements into Access applications that use JET like one can with SQL server. – Albert D. Kallal Dec 09 '11 at 00:05
3

My first programming job about 18 years ago was writing a visits booking system for the prison service, this was all done in ms access. I used it for over a decade after, and only recently moved to c#/.net.

Access is fine for your requirements. I suggest you learn at least the following:

  • the basics of database normalisation, and how to design related tables
  • how to split data and front end in msaccess with linked tables
  • securing both access databases with the workgroup information file and user level security
  • creating an encrypted .mde file to distribute the front end to your users
  • keeping a copy that isn't an encrypted mde!
  • basic form design
  • filtering records using the CurrentUser function, and using the same as a field default.

A good book or two will cover most of these.

Your big problem is that you are dealing with confidential data while on your learning curve. Your management must understand that it takes a lot of experience to properly secure something, and very little to get through a hole that has been left open. They need to accept this risk.

As to your side question, no idea. Make it a separate question and provide a lot more detail, and you might get an answer.

Stephen Turner
  • 7,125
  • 4
  • 51
  • 68
1

You can do this in Access. Whether it's the best solution really depends on a lot more specific details of your infrastructure, skills, budget, etc.

Robert Levy
  • 28,747
  • 6
  • 62
  • 94
  • What would your recomend I go with. I've been able to build something that works using pivot tables and veryhidden sheets in excel but it seems it would work better as a Database. I've got (what I believe to be) a database architecture for the program I just don't know how to limit the end users from accessing everything. Skills: I know Excel vba very well Budget: They have ms Office and aren't willing to spend more That is it – Tolure Dec 05 '11 at 22:19
1

Surely daily sales are being entered into some other system? Wny bother sales people and managers with duplicate entry just to calculate a commission.

Work on getting some export from your accounting system. Put it into separate spreadsheets with the calculations, so everyone can review their numbers and question any discrepancies.

Access is the tool to do these types of tasks, but you need to know what you're doing before you mess around with other people's money. Although there are performance considerations, it doesn't matter if you have 2 employees or 20, individual user data security isn't going to be handled automatically by Access out of the box.

Start with a prototype and read a few books on the subject. You could learn to build this eventually.

JeffO
  • 7,957
  • 3
  • 44
  • 53
0

If security is important, you are better off with a Oracle or Sql-Server backend (the DB). The frontend (the UI) can still be Access.

Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188