Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    9

    Unanswered: Rule or Listener Event?

    Hello i was wondering whether someone could please advise me?

    We have an ASP system at work which writes to a field in a table in an Access database. I would like to change this system so the data is also entered into another field within the table as well.

    The obvious choice would be to change the ASP script, but unfortunately we dont have the license to do that, but we can change the database itself.

    There was two ways that i was thinking this could be done, (your have to excuse me if they are complete rubbish i havent used Access in years). The first was to create some kind of rule on the table which will automatically be carried out whenever the field is updated - this will in turn update the second field. The second method would be to create some kind of listener module, that whenever the table is updated then the code within the module will be activated, this code will copy one field into another.

    Thanks for any help.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Access has no equivalent of a triggers or inbuilt functions, these tend to be a feature of server back ends.

    The only way I can think of doing this is to run a query periodically, say daily, or weekly updating the second field.

    I suppose the real issue is going to be what the second column is going to be used for

    You could do an update query to populate the second column, only if it is null IE empty. That way round you would reduce the amount of time required to run the query, any changes to either column would not come through

    I presume you wouldn't want to overwrite the second column if the first one changed, otherwise why use the secoind column at all?
    HTH

  3. #3
    Join Date
    Feb 2004
    Posts
    9
    Thanks......

    Doh! There isnt something that could be written in a module that could achieve this then?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes
    JET doesn't have the ability to perform any action apart form something that it is explicitly told to do either by form or queries. If you were using SQL Server or soemthing similar then you could use a stiored procedure

    The only semi automated workaround I can think of is to have a form open in access which periodically clicked in (say at hourly or half hourly intervals) and ran the query or queries. OR a batch file triggered by time using the windows scheduler or similar, call the query by a macro.

    Failing that its a manual process

  5. #5
    Join Date
    Feb 2004
    Posts
    9
    Thanks but i dont think thats viable. Guess we will have to make do how it is.

  6. #6
    Join Date
    Feb 2005
    Location
    England.
    Posts
    232
    Could you not just have a form that is left open? You could use the form's timer event to check every X amount of time, depending on how often you wish this to be done.

    The timer event could either call a module, or you could write some code to do this for you. Either write the SQL or get it to run an append query?

    Does this help?

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178

    Smile

    I assume that this change is the only one made by an external file, and all other data input or changes, reports, etc., are made within Access with the database open.

    If this is so, you really only need to make the update to column 2 when you open the database. That's easy! All you need to do is create the Update query, and call it from an Autoexec macro, which will run, as it sounds, every time the database is open. If you open a form as part of your database startup, you can instead call the query from the OnLoad event of your form, making the update automatic and not subject to human memory.

    Sam

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    That workaround would work
    except how often to do you run the query?, if its a big process you are potetnailly sucking a greatr deal of data accross the network every time the applciation is opened.
    would all machines opeing the tables run the query? - if not who would have the access / authority to do it? If its a 'golden' user then how do you cater for that person being off sick

    In many ways I would prefer a manual procedure, purely on the grounds that it becomes familiar, if something is done in background automatically the reasons for it become lost over time.

    I still think the best solution is to run the update queries via a macro called from a batch job run at some time that is convienient to the system (eg early hours of the morning), after all back up jobs have completed and before users start entering data.

Posting Permissions

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