Forums New posts Search forums. Media New media New comments Search media. Resources Latest reviews New resources Search resources. Attachment List. Log in Register.
Thread starter estrangedred Start date Oct 19, Thread Attachment browser. Hi all, I was searching this site for an Access Database for Document Control and I found one that was good and proceeded to modify it to my needs due to the complicated nature of our business. I have brought is so far that I have just creativley drawn a blank. Maybe I have over complicated things.
Either way I like to share what I have done. Maybe this will be of use to somebody 2.
Maybe somebody can alter it and make amendments and repost. As people here have helped me with all the great posts and opinion, I'd like to reciprocate. NGH Quality Hub 5. Marc Captain Nice Staff member. Oct 19, Very much appreciated! An update on my endeavours to make a decent database.
Database planning in Access 2010: audit/quality checklist
I had assumed that others would help develop this for me, but I persevered on myself and want to share with you where I have got. It is fit for purpose. The only thing I am missing is a search function in the Documents tab. I will get to this eventually, but time is a factor here as I have other pressing stuff to be at. I also intend to add audit, scar and training functions to this access database but for now. Here it what I have gor. NGH Quality Hub. Nov 15, I don't really do anything with Access now, but I posted a training database in this thread a few years ago.
If anything in there is useful to you, please feel free to use it. TandemUser Registered Jul 8, This is a very good basis for a doc control database, and I support your efforts with this!
You have utilized some approaches in your database that I had not thought of, so keep it up. If you want another look at it, or some development help on some other aspects, let me know as I would be interested in helping you out!Forums New posts Search forums. Media New media New comments Search media. Resources Latest reviews New resources Search resources. Attachment List. Log in Register. Search titles only. Search Advanced search…. New posts. Search forums. Log in.
Burpee Aug 16, I recently started working on an Access 97 database for Internal Auditing yes Access 97 my company hasn't made the switch yet. Does anyone have a copy of a database that they have implemented and would be willing to share for comparison?
Same thing goes for any other databases i. Docu Control, Engineering Change Control, etc I think I'm reinventing the wheel. I have an ECO tracking database in Access Please read the following. Instructions on downloading the database are at the end. The database is intended as an example to show how a risk and audit database can be compiled. It should not be used without modifying it to fit the needs and actual risks of the organization.
It will not be supported by www. Read the books available, free, from www. The purpose of the database is to record the risks of an almshouse housing charity 58 homes of which I was a trustee. The charity was established inalthough the current homes were built inand later, on a site outside Nottingham, UK. The charity provides homes for single people and married couples who are over 65 and on housing benefit.
Although the charity is relatively small, it has to produce accounts for publication and declare it has determined its risks. Thus the trustees have to determine risks, not only because they have a duty of care to the residents but because the law requires it. The charity drew up its original list of risks in and updated it annually.
After 5 years, we needed to have a major re- examination and set up a more formal structure. The database is built around the record of a risk. There are four main sections:. Risk identification - includes details of the risk, the objective it threatens, any legislation which caused the risk or instructs how it should be managed, and the consequences of the risk. Risk assessment - scoring the consequence and likelihood for the risk, before inherent risk and after residual risk controls.
Risk management - how the risk is to be brought down to an acceptable level or not, if that is applicable. Risk assurance - what checking has been carried out, or will be carried out, to make sure that the risk is being managed as intended. All these details are in one database table.
This does result in some duplication if one control manages several risks. However, I have worked with a commercial database which puts risks, controls and assurance in different tables and found the matching of them to be confusing.
For a small organization, I believe this database structure to be the best. Other details about the construction of the database for example: tables, reports, macros are outlined in the database. Processes are the activities which need to take place to achieve the objectives of the organization. Processes might be considered as objectives and I will be changing the terminology when I update the database.
The important point to make about processes is that they are the theoretical tasks required to achieve the objectives of the organization, not the actual systems. The other point to make is that risks come first. How the database was populated with risks and processes. I used the standards to prompt me for the risks inherent in operating almshouses and used the structure of the standards to define the processes for example; governance, administration, health and safety.
access 2016 database templates
I reworded some the wording of some risks is poor. This does result in some duplication. I also input the risks the charity had previously identified, but these are not in the version of the database on the web. Whatever type of organization you are, you will need to determine the risks which hinder the achievement of your objectives.
If you are a housing charity, the risks set up in the database will help you, although they will be incomplete for your organization. If you are not a housing charity, delete all the records and input your own risks.
You will need to decide on the hierarchy of the objectives of your organization. You will also need to amend the database structure, adding fields for data which you wish to record.
You will probably want to set up more reports.Keep in touch and stay productive with Teams and Officeeven when you're working remotely. This sample database template demonstrates how Access can manage small business customers, orders, inventory, purchasing, suppliers, shipping, and employees.
The database can generate 15 different reports and is a great showcase for learning and customizing Access databases. Create and maintain a comprehensive database of your customers partners and vendors using this popular Access template. In addition to maintaining details on company, job title, and contact information, you can create all manner of queries, add contacts from Outlook, create mailing labels, and generate reports such as directories and phone books.
Create and maintain a comprehensive database of your school's students and their guardians, using this robust Access template. In addition to maintaining student ID, level, room, special circumstances, and contact details on each student and guardian, you can take advantage of numerous queries such as Attendance, as well as generate all manner of useful reports, from Allergies and Medications to Emergency Contact Information.
Event Management. Create and maintain an events database to manage upcoming events using this Access database.
Powerful built-in and customizable event navigation and filtering let you track events by time for example, Current Events, Events by Week, and Today's Events as well as type, as you collect data in real time via email and generate and distribute useful and timely reports. Task Management. Create a task tracking database to track multiple tasks, including, owners, start and due dates, and percent complete values using this popular Access template.
Query by contacts, open tasks, and overdue tasks, keep an eye on task details, priorities, and status, and assign tasks, while producing useful reports such as Tasks Due Today. Asset tracking. Create and maintain a robust database to manage your organization's assets using this popular Access template. Keep up-to-date information on asset details including ownership, condition, location, purchase price and current value, as well as take advantage of the powerful query and data collection functionality to generate comprehensive reports.
The dark mode beta is finally here. Change your preferences any time. Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information.
I've drawn up most of the plans but am having a hard time figuring out a way to handle the auditing system. Since it is being used internally only and you won't even be able to touch the db from outside the building we are not using a login system as the program will only be used once a user has already logged in to our internal network via Active Directory. Knowing this, we're using a system to detect automatically the name of the Active Directory user and with their permissions in one of the DB tables, deciding what they can or cannot do.
My question is how should I be handling this. Ideally I know I should be using a trigger so that it is impossible for the database to be updated without an audit being logged, however I don't know how I could grab the Active Directory User that way. Obviously that is flawed because if something happens to Access or the database is touched by something else then it will not log the audit.
Ok, it's working here. I'm seeing my windows credentials when I update my tables.
UI Builder for Microsoft Access
So, I bet we missed a step. Let me put together a 1,2,3 sequence of what I did and maybe we can track down where this is breaking for you.
I tried playing with Access a bit to see if I could find a way for you. There should be a section in Access called "External Data" I'm running a new version of Access, so the menu choice might be different. I then created a Machine datasource.
I selected SqlServer from the drop down list. Then when I click Next, I'm prompted for how I want to authenticate. We also have a database system that is used exclusively within the organisation and use Window NT logins.Microsoft Access Asset Tracking Database Template
This function returns the current users login name:. How many users of the app will there be? Is there possibility of using windows integrated authentication for SQL authentication? I would only create the UI in Access and create an ADO connection to the server using windows authenticated in the connection string. Compile you Access application as dbe to protect the VB code. I would not issue SQL statement, but I would call stored procedures to perform the changes in the database, and create the audit log entry in an atomic transaction.
The UI Access does not need to know the inner works on the server. The server should handle the work. Or retrieve a single record and lock only that row for editing. Create a table to set the server status. You application will check it before any action. Access is a great tool. But it should only handle its local data and not be allowed to mess with the precious server.
Learn more. Asked 11 years, 8 months ago. Active 6 years, 11 months ago. Viewed 4k times.Keep in touch and stay productive with Teams and Officeeven when you're working remotely.
Learn More. Learn how to collaborate with Office Tech support scams are an industry-wide issue where scammers trick you into paying for unnecessary technical support services.
You can help protect yourself from scammers by verifying that the contact is a Microsoft Agent or Microsoft Employee and that the phone number is an official Microsoft global customer service number. Did this solve your problem? Yes No. Sorry this didn't help. April 7, Keep in touch and stay productive with Teams and Officeeven when you're working remotely. Site Feedback. Tell us about your experience with our site. Hello all! I am an experienced Excel user but absolute Access newbie.
I am trying to expand my quality audit tracking. Essentially, I have a multi-question checklist which we use for reviewing quality. The spreadsheet does all of the calculations to weight each response then provides a weighted score at the end. What I want is to build this directly into a database so auditors can fill out their checklists but then I can analyze results either by users or time or both.
The goal is to be able to address individual and group quality. Unfortunately, I am not sure how to structure my tables so I can do calculations in reports to give me what I want. The outputs should be a report for an individual audit, an output that shows me average score by employee over a given time and outputs which will show me what errors are most prevalent so I can target group training.
Internal Auditing - Access Database for tracking Internal Audits
I hope this makes sense. Here is a sample from my current audit checklist. There are more checklist questions but hopefully you get the idea. The error number is the type of error they are categorized and the error level is the weight with higher numbers being more serious.What is an audit trail?
Audit trails are extremely useful for administrators because they can view remote changes to the system that they control. In Access, implementing a tracking system, or even a small procedure to record data changes is very simple. Depending on the need of the business and the environment in which the business operates, the tracking may be very different.
For years now, Microsoft has had an article published about this sort of technique. The first thing to understand about tracking changes in an Access database is that the program continuously updates data definitions in the file as they happen. For instance, updating a form field will yield immediate results in the table, thus saving the form is not necessary. This is very different than Word or Excel, where the files must be saved to record data changes. The difference lies with "data binding", which is a concept that basically says that interfacing controls like listboxes and textboxes are just upscale representations of the raw data in the table.
Audit trails can only be made possible by this concept. Bound controls on forms are the key factor in creating a successful tracking system, and they can only exist on bound forms. So let's look at a bound form. How do you know it's bound to a table? By its Recordsource property:. Once the form has a source, controls can be bound to fields in that table or query. In the same form as the one pictured above, the textbox called "First Name" is now bound to the table field of the same name:.
At this point, the BeforeUpdate and OldValue properties become important. The "BeforeUpdate" property of any given form can be triggered an infinite number of times.
But the most common trigger is moving from one record to another. The only condition for the event is that one or more bound controls must have been modified on the form.
Generally speaking, this event will occur if this condition is met, and a user does any one of the following:. This property only applies to form controls, and only those that can hold values, such as text boxes and combo boxes.
This is the property used in an audit trail record. So how do you put it all together? The simple answer is that it's up to you! The Microsoft Article mentioned above gives a good outline of the general principles that you can use.
Some administrators track everything, and some don't track anything. Some record changes in every table, when that table is modified specifically. Others write records to a master table that can be easily exported and read in other programs. More often than not, the reason for the implementation of an audit system is either because internal policy requires it, or auditing firms request the information.
Below is a small example of how to track changes to the Northwind database:. The right-side menu lets us access all kinds of forms.
Inventory, Orders, Customers, etc. The most effecient way to audit your database is to write a global function that can be called by any form. Using this method, we can use a combination of Microsoft's code and our own to record data changes on any form to an AuditTrail table:. Now, everytime we update a record the administrator will have one more thing to look at. If we go in and add some email addresses to our customers' records, we see the result in the audit table immediately:.
Adam Evanovich lives in Iowa in the United States and frequently works on contract in various industries. He started using Access in to record notes in a small database for a marketing program.