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: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Databasedude.com Sent: Tuesday, November 01, 2005 11:45 AM To: [email protected] 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 [email protected], "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: [email protected] > [mailto:[EMAIL PROTECTED] On Behalf Of Databasedude.com > Sent: Sunday, October 30, 2005 3:13 AM > To: [email protected] > 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 [email protected], "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: [email protected] > > [mailto:[EMAIL PROTECTED] On Behalf Of Tom Oakes > > Sent: Saturday, October 29, 2005 9:37 AM > > To: [email protected] > > 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: [email protected] > > [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/
