Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2006
    Posts
    5

    Unanswered: Monitoring Changes in Access's Table

    Hello,

    Please is there any way to keep track of changes that are made in an Access Table with out using Forms. For example if someone modify a field (either Delete ,enter or modify the field) in the same row another field would set to true, this would take place in the same session and before the table is closed.
    Thanks in advance for your suggestion.

  2. #2
    Join Date
    Mar 2004
    Posts
    4

    Re: Monitoring Changes in Access's Table

    Id did it using a query created in access to send the content of the record to a table with the same structure but different name. For Example, the table where the modification or delete ocurred was name (in spanish) "billetes de empeño", and there were 2 tables "billetes de empeño (bajas)" and "billetes de empeño (cambios)" for registering deleted and modified records respectively, a query like this was executed from visual basic in the form_BeforeUpdate event:

    INSERT INTO [Billetes de empeño (Cambios)] (Avalúo, Categoría, Descripción, Fecha, [Fecha de Vencimiento], Folio, [Folio Auditoria], [Folio Original], Identificación, Intereses, [Liquidación de desempeño], Nombre, Préstamo, Recargos, [Recargos Calculados], [Refrendo No], [Saldo Capital], [Saldo Intereses], [Saldo Total], [Tasa de interés], [Telefono(s)], Usuario, Usuario_Baja, Fecha_Baja)
    SELECT [Forms]![Billetes de empeño]![Avalúo].[OldValue] AS Expr1, [Forms]![Billetes de empeño]![Categoría].[OldValue] AS Expr2, [Forms]![Billetes de empeño]![Descripción].[OldValue] AS Expr3, [Forms]![Billetes de empeño]![Fecha].[OldValue] AS Expr4, [Forms]![Billetes de empeño]![Fecha de Vencimiento].[OldValue] AS Expr5, [Forms]![Billetes de empeño]![Folio].[OldValue] AS Expr6, [Forms]![Billetes de empeño]![Folio Auditoria] AS Expr7, [Forms]![Billetes de empeño]![Folio Original].[OldValue] AS Expr8, [Forms]![Billetes de empeño]![Identificación].[OldValue] AS Expr9, [Forms]![Billetes de empeño]![Intereses].[OldValue] AS Expr10, [Forms]![Billetes de empeño]![Liquidación de desempeño].[OldValue] AS Expr11, [Forms]![Billetes de empeño]![Nombre].[OldValue] AS Expr12, [Forms]![Billetes de empeño]![Préstamo].[OldValue] AS Expr13, [Forms]![Billetes de empeño]![Total de Recargos Cobrados].[OldValue] AS Expr14, [Forms]![Billetes de empeño]![Total de Recargos Calculados].[OldValue] AS Expr15, [Forms]![Billetes de empeño]![Refrendo No].[OldValue] AS Expr16, [Forms]![Billetes de empeño]![Saldo Capital].[OldValue] AS Expr17, [Forms]![Billetes de empeño]![Saldo Intereses].[OldValue] AS Expr18, [Forms]![Billetes de empeño]![Saldo].[OldValue] AS Expr19, [Forms]![Billetes de empeño]![Tasa de interés].[OldValue] AS Expr20, [Forms]![Billetes de empeño]![Telefono (s)].[OldValue] AS Expr21, [Forms]![Billetes de empeño]![Usuario].[OldValue] AS Expr22, [Forms]![Billetes de empeño]![Usuario] AS Expr23, Now() AS Expr24;

    I hope this answer your question.

    Ing. Jesús Rodríguez L.
    Spanish Pawn Software
    http://www.sicmx.com

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Not without using an intermediate form or query. Access does not have facilities for table-level triggers.

    Uhh.. you're not giving users access to the raw tables without using a form...are you?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Apr 2006
    Posts
    5
    Hi Teddy,

    Thanks for your reply. In fact what I have right now is the password only. So after that I don't have any other protection and that is why I was trying to implement a trigger mechanism to monitor changes in the table, so upon any changes in a row the last fields on that row would be set to true, this way I would have a way to know that something were changed manually. So I guess your suggestion is that the only way in Access if I use Form and monitor changes from there. But would I be able to protect the Tables?

    Thanks

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Yes. How you do that depends on how/if your application is distributed. You could compile your application to an mde/ade, disable the allowbypasskey setting and set your form to open on startup. This would lock users out of anything you haven't explicitly given them access to.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Nov 2003
    Posts
    1,487
    Check this out.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  7. #7
    Join Date
    Mar 2004
    Posts
    4

    Re: Monitoring Changes in Access's Table

    I agree with it: it's better if you migrate your database to SQL Server (MSDE) and use forms instead of giving users direct access to the tables. And if you do not want to migrate to SQL Server, the only way to do this is with the visual basic code behind the forms. Access do not support triggers.

    Ing. Jesús Rodriguez L
    Spanish Pawn Software Specialists
    http://www.sicmx.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •