Microsoft access

[Skillachi]

I wasnt sure as to where to post this so i decided to place it in this section as i thought it was the only one that looked sorta suitable.
I am doing a levels and i have to create a database and i have chosen to create a inventory database for wheels & wheels auto (car dealer).
I want to know first. How can i use a picture in a specific field so that if a customer wants to view the car he is buying a report will come up showing all details on the car and the cars specifications and picture(s).
Another thing i want to know is how do i implement a username and password into the system so that as soon as you start up the db you are prompted for username and password and depending on your username and password you are only limited to certain areas of the system. So for example an admin will be able to see and edit all areas but a clerk may only be able to enter information or view information.

Please help

[CKnight]

For your picture thing. The easiest way of showing the car pictures is porbably to just store the location of the file in a text field in a database table. The geek way of doing it would be to store the actual image in the database as binary data, however I'm unsure as to how that's done in Access (if it can be done), I've only done it in MySQL.

If you choose to store the file location (which I suggest), then it's a simple matter of reading the address from the table when you load all the other information, then update the location property of the image control you're using and refresh it (if a refresh is required).

As for your password thing, I'm not sure at all how that's done in Access. I seem to recall you being able to password protect the whole database, but that scheme didn't lend itself to permissions for individual database entities. You can however, do this with code. A permissions module in any application is pretty straight forward. Disable all options in the database by default, create a table of users, a table of options, and a third table which maps users to the options they have permission to access. The first thing your application should then do is to prompt for a username and password. Once that's received and the user is validated, you go through the permissions table and enable each option that the user has permission to access.

Theoretically it's that simple. You can add another degree of dificulty by doing a Roles based permissions system, where instead of giving the users access to individual options, you assign them to groups that have predifined permissions. A little harder to code, but makes life much easier for the administrator of the system.


Anyhow, Access may very well have the password protection option for entities, lets see if anyone else can help.

[Skillachi]

Wow... that was all blurry for me Cknight. Oh well. I have not located any password option for entities and i am getting fedup of looking. This might be too much of a bother but since u suggested using code, could you perhaps tell me this code.... ;D pleeeeeeze
Anybody else know a way how?
Thanx btw Cknight

[ramesh]

You might want to look at TOOLS > SECURITY from the Access menu. You should be able to set up all your user accounts, passwords, permissions and encryption there.

[Skillachi]

I dont remember seeing a permissions option in the tools security menu but i guess i will have to look again. Thanx for suggestions thus far though. I will try both suggestions

[ramesh]

First thing is to set up the Administrator's account and password then the groups and finally the users in each group. Permissions should be set before while the groups and users are being created.

[Skillachi]

YOWWWW!!!! DAT WORKEDDDD TUFFFFFFF THANK YOU VERY VERY MUCH.
Wow, you dont understand how much strain that took off ma total work load, i was planning to get some code written and ish. Thanx man. ;D ;D i am now happy

[Xenocrates]

A bit late, but just for anyone else who might've read this, Access doesn't store BLOB (Binary Large OBject) data types. That means that the database doesn't store Pictures. You can only store text links to the picture file (as CKnight has already said). If you want to store BLOB data types, you need another type of database. But that means you'll require a different front end...

If I were you, I'd use a Visual Basic front end to code the module, and an ADO (Active Data Object) placed on a form, and link it to the Access database file (i.e., your *.mdb file). The ADO already has browse buttons. All you'd need to do is place fields onto the form and bind each of them to a specific field in the database. When you browse the database by clicking on the browse buttons on the ADO object, the fields in your form are automatically updated.

Additionally, Visual Basic allows you to place an ImageObject onto your form. All you need to do is set it's DataSource property to the field in your access database which has the text link to the picture files on your hard drive. Whenever you browse to different records, you will see a different picture come up for each record.

However, I'm assuming that you're doing the Cambridge A-Level course which requires you to submit a program. I'm also assuming that you don't know jack about Visual Basic, ergo you may not have the time to play with the application to do just that. You may be able to work some quick magic with Access, but it will never match the possibilities that you can achieve with a Visual Basic front end. It's ideal for the kind of project you're dealing with.

Finally, I would manually implement access permissions using a relational database as CKnight described. THEN I would add database permissions at the surface level so that nobody can open the file without going through the program. It's really quite simple Skillo. While Ramesh's solution will work, it doesn't give you as much control over the database as you'd like. However, that's subjective. It really depends on how far you're willing to go to impress your examiner. A'Level examiners mark VERY hard and are not easily impressed. You only get grades above 'C' for truly stunning submissions. Automated solutions will work just fine. But the examiners like to see students literally 'show off' their skillz.

I used Visual Basic to do my A level Computing exam submissions. It really helped kill the project in record time. Best of luck Skillo. Knock 'em dead.

[Xenocrates]

CKnight's Solution:

Although you may not end up using this solution in the end, I think it's the best way to go. What CKnight is suggesting is the use of a Relational Database architecture to implement your Access Rights system. I will try to elaborate in more detail as I think CKnight touched on a VERY important concept here. To demonstrate what CKnight was trying to say, I will create three tables here:

Table 1 - Database_Users

ID_Num	Username	Name	Age	Address
001	tom	Tom Jones	63	Miami
002	dick	Dick Tracy	75	Chicago
003	harry	Dirty Harry	27	Detroit

Table 2 - Access_Rights

UserNo	Read	Create	Change	Delete
001	Yes	Yes	No	Yes
002	Yes	No	Yes	Yes
003	Yes	No	No	No

Table 3 - Program_Rights

User#	Car_Data	Auto_Parts	Admin
001	No	No	Yes
002	Yes	Yes	No
003	Yes	No	Yes

Ok. Now you can place the access permissions from the FILE LEVEL as Ramesh has shown you. But what about individual users who log into the system with a specific username you have created for them in the database?

In table 1, we have Tom Jones, Dick Tracy and Dirty Harry's records. Table 1 merely serves as a human resource database. Kinda like your Employee Records table.

In table 2, we see Database Rights for each person. We see that Tom can read and create records, but he can't modify them. Dick can read and change records, but he can't create them. Harry can only read the data in the table. But he can neither create nor change records.

In table 3, we assume that we have three programs: Car_Data, Auto_Parts and Admin. Tom can only access the Admin program. Dick can only access the Car_Data and Auto_Parts programs. Harry however, can only access the Car_Data and Admin programs.

[Xenocrates]

CKnight's Solution (Cont'd)

Each of the three tables has the very first field as the key field (that is the field that is underlined). These tables are also in Third Normal Form - that's an advanced concept I doubt you'll do at the A-Level. You'll probably do this in university. But essentially it means that the tables only consist of fields which are either key fields, potential key fields or fields directly related to the key field.

In Microsoft Access, when you've created these three tables, you can link them to each other by their key fields to create a ONE - To - ONE table link between the three tables. That means, when you browse to Tom's record, you will only see what changes he can make to the database and only what programs he can access. Same thing for Dick and Harry. This is the essence of what is called a Relational Database. This is what CKnight was describing to you.

Now in your program, let's say that Dick logs into the system. You should code your password entry module so that it looks into the database for all of the permissions that Dick has. So if Dick tries to create a new record in the Auto_Parts module, an error message could pop up saying "You don't have sufficient rights to create a record in this module". Or better yet, if your modules use a Toolbar object, whenever Dick logs in, you can gray out the "New Record" button (if you've made one) so that Dick can't click on it when he enters the Auto_Parts module.

In fact, if you implement this system, make sure that before the user logs in, all programs and buttons are disabled. When the user logs in successfully, only enable those buttons for which they have access rights to. This you will determine by causing your program to look into the access rights tables for the particular user who logged in. When you design your system around a security module like this, you can easily adapt it to be accessible across a network.

This is essentially what CKnight suggested that you implement. It's a good way to showoff to your A'Level examiner that you know what you're doing and it's sure to net you some bonus points, and maybe even an 'A' for your project. Remember, the power is in the design

Whatever solution you eventually roll with, I wish you best of luck!