Does anyone know if there is a way to make a calculated weeks active field that updates each day? Right now I have a button on the main menu marked 'Update Weeks' where I use ADODB to open the relevant tables and do a DateDif() on it if there is no ship date but it often comes up with ...'Currently Locked by user such-and-such' errors when pushed. Any ideas out there?
1) You want to recalculate a field on a reoccuring basic.
2) You have locking issues when you attempt to do this calculation.
For the first issue, here's an easy way to do it. Create a macro with four actions, SetWarnings, OpenQuery, SetWarnings, then Quit. First, set the warning off so you don't get dialog boxes. Next, fill out the OpenQuery info. Set the warnings back on, put a Quit action, and save the macro. Then right-click the macro, and choose Create Shortcut. This will save a MAM file to your hard drive.
Next, use some type of task scheduling program (Windows comes with one) to execute the macro shortcut at the specified time. Whenever the task scheduler program executes the shortcut, Access will open, it will run the query or queries, and then it'll close itself.
That's pretty much it for the first issue. Now, keep in mind this isn't the best way to do it. You really won't be able to check for any erros when it runs, so you may consider doing it in straight VBA rather than macros to trap for erros and handle them gracefully.
For the second issue, if you have multiple users then you may want to examine how these users open the database (default open, read-only, exclusive, or exclusive read-only). Then make adjustments to that procedure to prevent locking problems. You may want to have a memo sent to remind users to close any open applications that hit the database if it is not needed.
If there are competing applications that automatically run on the database in question, then schedule your calculation update during a time when these processes don't run. Lastly, you may want to examine how you are doing the updates to see if there is an issue there with record locking. Without any details, I cannot point you in the right direction for the update itself, but I think this should get you started.
Thanks for your help--that's a good idea. The problem is that multiple user, maybe 7 or 8, have the database open continously because they all update different data at multiple times during the day. I therefore can't have anyone open exclusively or tell people to close the DB when it is not being used because even given that, chances are someone will be using the affected tables. On the positive side, these weeks active calculations are helpfull but not at all critical, so if there are errors it just looks funny, it doesn't really affect productivity. I think I will try the macro way, though I'm not sure how to get a macro to look through every record in three tables and make a change based on a certain condition. Right now the Update Weeks button that I'm using has a bunch of VBA behind it. I tend to code rather than use macros myself so I'll have to teach myself how to get a macro to execute a block of code. Again, thanks for the help