As Tom said, you can set them to run in VBA code, if your tracking needs don't coincide with a change to the table.
Otherwise, if the tracking is based on a record being added/edited/deleted, by all means the most efficient way is to call the sp from a trigger. It's not a requirement, though. HTH Bryant --- In AccessDevelopers@yahoogroups.com, "Angelo Antonino" <[EMAIL PROTECTED]> wrote: > > Hi Bryant, > > Can you confirm that you fire the sp's from triggers set at the table > level. > You don't fire the sp's from VBA? > > thanks, > Angelo > > > > -----Original Message----- > From: AccessDevelopers@yahoogroups.com > [mailto:[EMAIL PROTECTED] On Behalf Of Databasedude.com > Sent: Tuesday, November 01, 2005 11:45 AM > To: AccessDevelopers@yahoogroups.com > Subject: [AccessDevelopers] Re: ADP / SQL Server log tools > > Sure can! I like to capture the "before" and "after" versions > personally. > > When a record is edited, SQL actually has the values stored in two > tables, Inserted (new record & values) and Deleted (old record & > values). You can also test for certain fields being changed. > > Glad you asked this - I'm going to revisit it for an upcoming project, > and this will give me a head start! > > HTH > Bryant > > --- In AccessDevelopers@yahoogroups.com, "Angelo Antonino" > <[EMAIL PROTECTED]> wrote: > > > > Thanks Bryant. > > I have yet to use triggers. > > If the trigger is activated when a field is modified, can the sp store > > the value of the field, before and after the change? > > > > thanks, > > Angelo > > > > > > > > -----Original Message----- > > From: AccessDevelopers@yahoogroups.com > > [mailto:[EMAIL PROTECTED] On Behalf Of > Databasedude.com > > Sent: Sunday, October 30, 2005 3:13 AM > > To: AccessDevelopers@yahoogroups.com > > Subject: [AccessDevelopers] Re: ADP / SQL Server log tools > > > > Personally I created something very similar for my projects, and use > > the stored procedure as a trigger on the tables which I wish to audit. > > That allows reporting on all changes, regardless of the interface in > > which they were initiated. > > > > HTH > > Bryant > > > > --- In AccessDevelopers@yahoogroups.com, "Angelo Antonino" > > <[EMAIL PROTECTED]> wrote: > > > > > > Thanks Tom. > > > > > > So you trigger the sp from VBA? > > > > > > There are tools available that record changes to the SQL log files, > > and > > > allow reporting on these changes. > > > Any one had any experience with these? > > > > > > thanks, > > > Angelo > > > ________________________ > > > > > > Angelo Antonino > > > Tanzanite Software Solutions > > > M: 0403317772 > > > E: <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED] > > > W: <http://www.tanzanite.com.au> www.tanzanite.com.au > > > <mailto:[EMAIL PROTECTED]> > > > > > > -----Original Message----- > > > From: AccessDevelopers@yahoogroups.com > > > [mailto:[EMAIL PROTECTED] On Behalf Of Tom Oakes > > > Sent: Saturday, October 29, 2005 9:37 AM > > > To: AccessDevelopers@yahoogroups.com > > > Subject: RE: [AccessDevelopers] ADP / SQL Server log tools > > > > > > Angelo, > > > > > > I do something like this in most of my applications. I have a table > > > named tblTrack, that looks something like this: > > > > > > TrackID int (identity) > > > TrackIP nvarchar 15 > > > TrackLogID int > > > TrackType nvarchar 50 > > > TrackAction nvarchar 50 > > > TrackLinkID int > > > TrackText nvarchar 255 > > > TrackUsername nvarchar 20 > > > dtmTrackStamp datetime 8 (default value = getDate()) > > > > > > The stored proc that populates it is pretty simple: > > > > > > ALTER Procedure spAddTrack > > > ( > > > @TrackIP nvarchar(15) = Null, > > > @TrackLogID int = 0, > > > @TrackLinkID int = 0, > > > @TrackType nvarchar(50) = Null, > > > @TrackAction nvarchar(50) = Null, > > > @TrackText nvarchar(255) = Null > > > ) > > > As > > > > > > INSERT INTO tblTrack (TrackIP, TrackLogID, TrackLinkID, TrackType, > > > TrackAction, TrackText) > > > VALUES (@TrackIP, @TrackLogID, @TrackLinkID, @TrackType, > @TrackAction, > > > @TrackText) > > > > > > > > > ------------------------------------------------------------------------ > > > --------------------------------------------------------------- > > > TrackIP = Many of my applications have a web interface, so I capture > > the > > > IP address, if applicable. > > > LogID = ID of user who is logged into the program. You may want to > > use > > > the Windows username (a string), if you're not using custom > security. > > > TrackType = this is kind of a "category" that lets me know what > > > domain/table we're dealing with (i.e. "Product", "User", "Project", > > > "Invoice"). > > > TrackAction = the action that took place ("Add", "Update", "Delete", > > > "Failure", ....) > > > TrackLinkID = If TrackType is specified, this should be the primary > > key > > > of the record in question. This is optional. > > > TrackText = descriptive text of what happened. Optional. > > > > > > ------------------------------------------------------------------------ > > > --------------------------------------------------------------- > > > > > > So...when a product is deleted from the database, this proc fires > > prior > > > to the actual delete: > > > EXEC spAddTrack "24.21.140.182", 1900, 20332, "Product", "Delete", > > > "Widget #4055" > > > > > > ...when a new customer is added: > > > EXEC spAddTrack "24.21.140.182", 1900, 901, "Customer", "Add", "NW > > Media > > > Inc." > > > > > > ...when someone tries to login and fails: > > > EXEC spAddTrack "24.21.140.182", 1900, 0, "Login", "Failure", "" > > > > > > And so forth. > > > > > > You could make this a lot more complicated, obviously. One of my > > > clients wants to be able to see an audit trail of everything that > > > happens on a Project/WBS basis; so when a product is deleted, for > > > instance, we also store that product's project id with the audit > > record. > > > > > > > > > Hope that helps you. > > > > > > Tom Oakes > > > Personal PC Consultants, Inc. > > > [EMAIL PROTECTED] > > > 503.230.0911 (O) > > > 402.968.6946 (C) > > > 734.264.0911 (F) > > > > > > _____ > > > > > > From: AccessDevelopers@yahoogroups.com > > > [mailto:[EMAIL PROTECTED] On Behalf Of Angelo > Antonino > > > Sent: Friday, October 28, 2005 3:46 AM > > > To: 'AccessDevelopers' > > > Subject: [AccessDevelopers] ADP / SQL Server log tools > > > Hi, > > > > > > I have an ADP connected to a SQL Server 2000. > > > > > > I need to keep a log of changes done to the data (ie. Edit's, > Create, > > > Delete - who did it, time, field.etc). The more detail logged the > > > better. > > > > > > Has anyone had experience with any tools that do such a thing? > > > > > > I could create a stored procedure, that would populate a table every > > > time data changes, but I want to investigate what is out there > first. > > > > > > Any thoughts appreciated. > > > > > > thanks, > > > Angelo > > > ________________________ > > > > > > Angelo Antonino > > > Tanzanite Software Solutions > > > M: 0403317772 > > > E: <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED] > > > W: <http://www.tanzanite.com.au> www.tanzanite.com.au > > > <mailto:[EMAIL PROTECTED]> > > > > > > > > > > > > > > > > > > Please zip all files prior to uploading to Files section. > > > > > > > > > > > > SPONSORED LINKS > > > > > > Microsoft > > > > > > <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+developer&w1=Micro > > > > > > soft+access+developer&w2=Microsoft+access+help&w3=Microsoft+access+datab > > > > > > ase&w4=Microsoft+access+training&w5=Microsoft+access+programming&w6=Micr > > > osoft+access+tutorial&c=6&s=186&.sig=beZP9veAC8Wk2esgxlpTRQ> access > > > developer > > > Microsoft > > > > > > <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+help&w1=Microsoft+ > > > > > > access+developer&w2=Microsoft+access+help&w3=Microsoft+access+database&w > > > > > > 4=Microsoft+access+training&w5=Microsoft+access+programming&w6=Microsoft > > > +access+tutorial&c=6&s=186&.sig=B44I3Zk6lhirfxsAge8nLQ> access help > > > > Microsoft > > > > > > <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+database&w1=Micros > > > > > > oft+access+developer&w2=Microsoft+access+help&w3=Microsoft+access+databa > > > > > > se&w4=Microsoft+access+training&w5=Microsoft+access+programming&w6=Micro > > > soft+access+tutorial&c=6&s=186&.sig=uSZc3LUV_ONpuYljCyPAnA> access > > > database > > > > > > Microsoft > > > > > > <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+training&w1=Micros > > > > > > oft+access+developer&w2=Microsoft+access+help&w3=Microsoft+access+databa > > > > > > se&w4=Microsoft+access+training&w5=Microsoft+access+programming&w6=Micro > > > soft+access+tutorial&c=6&s=186&.sig=PFhSWgTKPZGWDOojGq8lSg> access > > > training > > > Microsoft > > > > > > <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+programming&w1=Mic > > > > > > rosoft+access+developer&w2=Microsoft+access+help&w3=Microsoft+access+dat > > > > > > abase&w4=Microsoft+access+training&w5=Microsoft+access+programming&w6=Mi > > > crosoft+access+tutorial&c=6&s=186&.sig=pfyn-CEy_tx3-qpe8oivIw> > access > > > programming > > > Microsoft > > > > > > <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+tutorial&w1=Micros > > > > > > oft+access+developer&w2=Microsoft+access+help&w3=Microsoft+access+databa > > > > > > se&w4=Microsoft+access+training&w5=Microsoft+access+programming&w6=Micro > > > soft+access+tutorial&c=6&s=186&.sig=cByQE6x-t27mwtr-CD_MTg> access > > > tutorial > > > > > > _____ > > > > > > YAHOO! GROUPS LINKS > > > > > > * Visit your group "AccessDevelopers > > > <http://groups.yahoo.com/group/AccessDevelopers> " on the web. > > > > > > * To unsubscribe from this group, send an email to: > > > [EMAIL PROTECTED] > > > > > > <mailto:[EMAIL PROTECTED] > > > > > > > > > > * Your use of Yahoo! Groups is subject to the Yahoo! Terms > of > > > Service <http://docs.yahoo.com/info/terms/> . > > > > > > _____ > > > > > > > > > > > > > > > > > > > > > > > Please zip all files prior to uploading to Files section. > > Yahoo! Groups Links > > > > > > > > > > > Please zip all files prior to uploading to Files section. > Yahoo! Groups Links > ------------------------ Yahoo! Groups Sponsor --------------------~--> Fair play? Video games influencing politics. Click and talk back! http://us.click.yahoo.com/T8sf5C/tzNLAA/TtwFAA/q7folB/TM --------------------------------------------------------------------~-> Please zip all files prior to uploading to Files section. Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AccessDevelopers/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
