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.
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?
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.
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.
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.