I've got an odd question ..
I have an application with an Access 2002 front end with
linked tables to a SQL 2000 backend. Users have always
been able to edit the SQL tables via access linked
tables/forms with no problem. I have now created an audit
table and using triggers to register each change. The
problem is, users want to be able to view the audit tables
through an access linked table .. and for some reason the
data in the audit table is not the same as the data on the
SQL server. However, if I import a copy of the audit table
the data is exactly the same. For example, if I make a
change to the column subtitle, the SQL table view will
show:
Subtitle ..... auditAction
Testing Snapshot A
Testing 1 SnapShot B
An imported copy of the table into Access will show:
Subtitle ..... auditAction
Testing Snapshot A
Testing 1 SnapShot B
While the linked Access table with show:
Subtitle .... auditAction
Testing Snapshot A
Testing Snapshot A
It doesn't make sense to me that the linked SQL table does
not show the same data as an imported table? Is there
caching going on somewhere?
The trigger is:
CREATE TRIGGER [TRIGGERPLAN2005] ON [dbo].[tblPlan2005]
FOR INSERT, UPDATE, DELETE
AS
IF (SELECT COUNT(*) FROM INSERTED) > 0 AND (SELECT COUNT
(*) FROM DELETED) > 0 BEGIN INSERT auditPlan2005
(auditUniqueID,auditQuarter,auditSubjectCourseAssign,auditL
Section,auditSubtitle, auditInstructorID,
auditInstructorPost, auditSupervise, auditEnrollLimit,
auditThirdWeekEnroll, auditNonUSNonEU, auditFilmCourse,
auditNotes,auditUser,auditDate, auditAction) SELECT
UniqueID, Quarter, SubjectCourseAssign, LSection,
Subtitle, InstructorID, InstructorPost, Supervise,
EnrollLimit, ThirdWeekEnroll, NonUSNonEU, FilmCourse,
Notes, User_Name(), GetDate(), 'SNAPSHOT A' From deleted
INSERT auditPlan2005
(auditUniqueID,auditQuarter,auditSubjectCourseAssign,auditL
Section,auditSubtitle, auditInstructorID,
auditInstructorPost, auditSupervise, auditEnrollLimit,
auditThirdWeekEnroll, auditNonUSNonEU, auditFilmCourse,
auditNotes,auditUser,auditDate,auditAction)
SELECT UniqueID, Quarter,SubjectCourseAssign,LSection,
Subtitle, InstructorID, InstructorPost, Supervise,
EnrollLimit, ThirdWeekEnroll, NonUSNonEU, FilmCourse,
Notes, User_Name(), GetDate(), 'SNAPSHOT B' From inserted
END ELSE IF (SELECT COUNT(*) FROM INSERTED) > 0 BEGIN
INSERT auditPlan2005
(auditUniqueID,auditQuarter,auditSubjectCourseAssign,auditL
Section,auditSubtitle,auditInstructorID,
auditInstructorPost, auditSupervise, auditEnrollLimit,
auditThirdWeekEnroll, auditNonUSNonEU, auditFilmCourse,
auditNotes,auditUser,auditDate, auditAction) SELECT
UniqueID, Quarter, SubjectCourseAssign, LSection,
Subtitle, InstructorID, InstructorPost, Supervise,
EnrollLimit, ThirdWeekEnroll, NonUSNonEU, FilmCourse,
Notes, User_Name(), GetDate(), 'ADDED' From inserted END
ELSE IF (SELECT COUNT(*) FROM DELETED) > 0 INSERT
auditPlan2005
(auditUniqueID,auditQuarter,auditSubjectCourseAssign,auditL
Section,auditSubtitle, auditInstructorID,
auditInstructorPost, auditSupervise, auditEnrollLimit,
auditThirdWeekEnroll, auditNonUSNonEU, auditFilmCourse,
auditNotes,auditUser,auditDate, auditAction) SELECT
UniqueID, Quarter, SubjectCourseAssign, LSection,
Subtitle, InstructorID, InstructorPost, Supervise,
EnrollLimit, ThirdWeekEnroll, NonUSNonEU, FilmCourse,
Notes, User_Name(), GetDate(), 'REMOVED' From deleted ENDIF
