General Ideas for my database

[bking]

Hello everyone I am new to Microsoft access and have to make a database for a report. The report is all of the current jobs for the company and have various information about each on. There will be many users who have access to the information and depending on the position be able to edit certain parts of the table. I already know that I need to create a BE and a separate FE for each type of user, now I am getting into the functionality of the database. So here is what I am looking for. I need to be able to restrict what people can see and I am looking for a way to time stamp every time a user edits data and log it along with there initials. I have already hidden the panes in the navigation bar so that only the forms are visible and I have no idea how to timestamp when new data in entered.

Any help would be greatly appreciated

[ranman256]

If you are tracking users you would need a tLogs table to track all this ,
at the forms AFTER UPDATE event:
you would run an append query

sSql = "INSERT into tLogs ([user],[form],  [editDate]) values('" & CurrentUser() & "','" & me.name & "',#" & now() & "#")
docmd.runSql sSql

[bking]

ranman

I am using Access 2010 and from what I read online the ability to set up users is no longer availible

[John_G]

Hi -

The old method of defining users and setting up permissions using a Workgroup file is gone, yes, but you can still do much the same thing within your database by having a User table and a login form. It does take some VBA programming to implement, though, especially if you have different requirements for different users.

You stated that you wanted a different FE for each type of user. My suggestion - Don't ! You will quickly find yourself in a maintenance nightmare. Instead, use the user ID and VBA to determine and control who can do what.

Time-stamping and activity tracking can be built in, but it isn't automatic and does require additional fields and/or tables.

HTH

John

[bking]

John

So would I need to still create a BE and one FE so that multiple users can access the database at the same time? As far as tracking the changes go I do not want a separate table for each job as the amount of data would be too much, so my question is could I make one table called job updates and have the jobs in column one and then any changes in the row of that job and then use a lookup function for the job history?

Thanks

[John_G]

As for the update log, a job update table as you suggested is certainly one way to go. You could easily include the timestamp and user ID in it as well. A DLookup would not get you the job history, because it only returns one value (not even a whole record), but a Select query with the job number in the WHERE clause would work fine.

John

[Xipooo]

A User Table with access levels is the way to go. You could even go so far as a "Groups" table with different access levels which your users belong to. Then in your forms build each one of your forms and controls to check for the user access level to determine if the control or form should be displayed. By doing it this way, you do not have to use separate FE databased for each user. This will become a logistical nightmare.

As for logging, I need to caution you about using logging with Access. Due to the file size restrictions, logging can be the worst thing you can do if it is not done carefully. Do NOT try to log every event. Determine which events are the most critical to track and put those into a single log table.

There is an alternative to logging within an Access table and that would be an FSO object that writes to a text file. This is probably the most common form of logging you find in other programs. If you format it as a CSV file then you can even use it as a table in your application to be read and examined.