User updated database help

Report · Apr 15, 2008

I have built a database in Access which allows me to import a list of file names hundreds at a time into a table. My ultimate goal is to be able to allow users to make changes to that list (for example, make note of who updated that file and when they did so). What command do I use for this? Would I use a flash table?

As a preface, I have some, although minimal experience with Dreamweaver and ColdFusion and I have been working with the Macromedia ColdFusionMX7 Web Application Kit book.

Thank you in advance for your help and direction.

Report · Apr 15, 2008

Create another table in your database to log changes to the data.

Write a web app that makes the people log in to make changes. At some point you will need two queries, and update for the data, and an insert into the log table.

Report · Apr 15, 2008

I use IIS and Integrated Authentication. Therefore I can use ColdFusion's built-in CGI variables to tell me who the currently logged in user is (notice I did not say "who is behind the keyboard"). With this information I log the date, time, and other pertinent information of literally every page hit on my intranet. This also doubles as my hit counter, for no other reason than the fact that management loves to know how many "hits" we are getting.

It may appear to be resource intensive and over-the-top, but much of my information is patient medical records, and there is a requirement in place to log just about everything.

So, like Dan said, create a new table (mine is called tblHits) and have at it.

I use the Application.cfm page to determine if the visitor is in my permissions table with the right to view the current directory, since my web site directories are strategically set up according to department. I look up that information in my usertable. If the answer is no, the user is CFLOCATION'd away to the Access Request Page.

I use the OnRequestEnd template to log the username, the visited page, the referring page, the IP address, the current date and time, etc. In our case, everything patient related revolves around the patient's client number, so if the client number is defined on any given page I log that too. Sooo, if I want to know who accessed client number 5348's information over the last month it is a straighforward query.

There are more modern ways to achieve this with Application.cfc, etc, but my way flat out works for us, and probably will not be changed until our needs change.

In Response To tclaremont · Apr 15, 2008

Thank you both for your quick responses.

tclaremont - I appreciate your suggestions however I need employees to modify ONLY when they have made changes or updates to the files listed, not just when they are logged in.

Dan - I went ahead and created another table in my database named tblUserDatabase. The fieldnames which I included in my table are EmployeeID, Status, CheckedBy, Clean-Prep, and strPictureFilename (which is a relationship with my original table that lists Filenames from a folder).

I also went ahead and created a new query to update the data titled UserDatabaseQuery which includes the same fieldnames as my tblUserDatabase.

Now, in ColdFusion my code is as follows:



<cfquery name="files" datasource="ImportFiles">
SELECT strPictureFilename, dtePictureDate
FROM tblImportTestTable
ORDER BY strPictureFilename
</cfquery>


<html>
<head>
<title>Imported Files</title>
</head>

<body>

<h1>Imported Files</h1>


<table border="1">
<cfoutput query="files">
<tr bgcolor="##CCCCCC">
<td>#strPictureFilename#</td>
<td>#dtePictureDate#</td>
</tr>
</cfoutput>
</table>

</body>
</html>

What do I need to do in order to have my new table shown and available to be modified by employees when they modify or update the files listed?

Thank you again for any and all assistance.

Report · Apr 15, 2008

Brianne, I realize that my method is overkill. The point I was making is that if you are using IIS and Integrated Authentication, you will know WHO is making the changes and can log all the information you need using CGI variables and simple Now() values for the date and time.

If there is a specific page that commits the changes to the files listed, then put your logging actions on THAT page, rather than on the OnRequestEnd page.

Report · Apr 15, 2008

Sorry for the misunderstanding! Your response and suggestion is very much appreciated, just not the solution I am looking for in regards to this specific issue. I will not be monitoring the changes on a regular basis, the updates shown are to help employees working on certain tasks to keep track of what has been completed by fellow team members. I will definitely keep it in mind for other projects however!

Report · Apr 15, 2008

This is a general approach for updating records and logging the updates. Note that if you don't make your users log in somewhere, you won't be able to identify the users. Depending on your circumstances, tclaremont's suggestion may or may not be appropriate.

Also, you will need a working table. Let's call it worktable.

form page
<cfquery name="q1">
select id, otherfield
from yourtable
</cfquery>

<cfform>
<cfoutput query="q1:>
<cfinput name=record#id# value="#otherfield#>
closing tags.

On your action page
Delete all records from your working table

Now you are going to re-populate it by looping through your form fields.

<cfloop list = "#form.fieldnames#" index = "thisitem">
<cfif left(thisitem, 6) is "record">
<cfset thisid = right(thisitem, 7, len(thisitem)>
<cfset thisvalue = form[thisitem]>
now insert this record into your working table
</cfif>
</cfloop>

log your changes first
<cfquery>
insert into log_table
(fields)
select values
from worktable w join realtable r on w.id = r.id
where w.thefiled <> r.thefield
</cfquery>

then update your real table from the working table. The best syntax is db specific.