Auto run macro in access without task scheduler (1 Viewer)

Hi

Is there a way I can auto run a macro that will import once in the morning and also delete the same table in the evening.

My company have disabled and will not authorise anyone to have Windows task scheduler on their profiles. At the minute I have an excel workbook that is dumped into a shared location via a scheduled output from business objects. Every time a user opens the database it imports the whole workbook (approx 15k records), as you can imagine that is ok when the first users opens it but when 3-5 try it dims the lights in the building. This data is non editable and comes from our asset management system so a macro to delete this table at a set time in the evening is also required.

All suggestions are very much appreciated, I know this sounds messy but without a scheduler I'm a bit stumped. I can't link the table either as it is excel and the db is multi user.

Thanks miff3436

You could simply set a date flag in a table with the last update run, and only run the update if it's not run on that day?

Hi Minty,

Thanks for the response. This sounds like an option, stick with me its been a long time since I used Access.

So if I create a table that holds a system time + Date stamp of the first import. When the db is opened it will look at the last date in the table and if = today It will not run the import macro. Are we talking about the same thing ?

Thanks miff3436

One solution;

Add a configuration file to your app that holds last date imported. When your database is run, have it first open the config record. If not on the same date, set the date in your configuration file to be NOW, update the config file, delete * from the table you need to import, import from excel, then delete the excel file.

If each person is working off of a copy of this file ("Every time a user opens the database it imports the whole workbook" leads me to think this) you would be better served by importing it to a shared database first, then copy local if required. Not sure of your network topology but having a copy local is generally not the best idea.

SPECIAL NOTE: Even if you can't have a scheduler running under windows, you can write your own using timer events. Please look HERE for the little you need to brush up on. You could, should you really wish, have table with times that you wanted actions to be performed. You would then leave a form up and running all the time that would handle your requests as needed.

Hi Mark_

Thanks for the options to choose from. As my VB skills are at the beginner end of the spectrum i am going to try a config table first. I will feedback for other users once ive tackled it.

Thanks again miff3436

Hi,

I have been playing around with this and have hit a bit of a stumbling block. You will know doubt think this is long winded but I like to learn by trial and error and not just googling for answers.

I have created a table that has two fields in it 'LastImportTime' & 'CurrentTime'. Using Expr1: Right([Lastimportupdate],8) for each field I am just capturing the hours / minutes & secs.

I then have a form called 'Home' that opens when the db is opened, this form has two text boxes that shows 'LastImportTime' & 'CurrentTime' values.

Text box 'C' =LastImportTime and Text box TxtCurrenttime=CurrentTime.

I want to run a macro
This feels like it can work for me, I just need a bit of a pointer with the hours issue.

Many thanks..........miff3436

You need to look at DATEDIFF as a way to compute date differences in selected units, and that function WILL provide a difference in hours. Just remember when taking differences to get the times in the right order because otherwise ALL of your numbers will be negative and will therefore ALL be < 6 hours.

Be warned that by not using the Windows Task manager, you run into the problem that if you want your macro to run at a certain time, the hosting machine MUST be running AND LOGGED IN at that time, and that Access must be activated. Further, something MUST be active within Access to provide the basis for the timer. This is usually a form, though I'm sure someone might have other suggestions.

Basically, what I'm saying is that you need a scaffold to support this function and it includes running Access wide-open on that terminal with a form that will provide a code context for the code you want to run. (I suppose your screen could be locked at the time, but that's about all it could be and still run.)

The "multi-user" part is a fly in the ointment. You need to perhaps restrict how many people could do this.

To be honest, what you REALLY need to do is to get a sign-off from your boss who wants this run and take it to the security wonk and slap him with it. Then when the wonk says "NO" to the task scheduler, you go to the boss and get the guy in trouble for interfering with required business and make it HIS problem as to how to allow you to do what needs to be done.

I might be saying this in a somewhat facetious tone, but I worked in a government environment - U.S. Dept. of Defense - for over 25 years. The correct solution is not to make your exception request from below, but rather have it come around from above so that it CAN'T be avoided.

You have one other problem here as well. If your security wonk is that strict, you might not be ABLE to have a computer active without someone in front of it, which means your system might not be able to do this anyway unless you want to be there for the morning AND evening events. You might even be facing reboots for patching after hours. That will surely leave your system unable to perform your tasks.

Hopefully I'm not overthinking this ...

Dates & times are stored in Access as double numbers
Each full day = 1, so 6 hours is 1/4 day = 0.25

So you need to convert your times to doubles using CDbl and subtract 0.25

If CDbl(Me.TxtImportTime)< CDbl(Me.TxtCurrenttime) - 0.25 Then

Click to expand...

That should trigger another import after another 6 hours

However there is a whopping great fly in the ointment

If the last import was done on 12 Dec at say 8pm (20:00) then for anyone logging in anytime up to 20:00 on 13 Dec, the current time will be less than the import time.
Even after that time, you won't have 6 hours before midnight resets the clock again.
Ditto the following day/week/month/year/decade ... you get the point.

In other words, you need to store the date & time for both import & current values

EDIT - I hadn't read Doc's reply - also describing flies in the ointment

Hi,

Thanks for the responses. THE_DOC_MAN, I totally agree with you and anyone else I have spoke to that understands what I am trying to achieve is that the Windows task scheduler is the correct option. I have spoken to the head of our IT and it is an absolutely straight NO. It's quite a large company (20k+ employees) and access is frowned upon and has lots of the functionality disabled. SO...... this makes trying to develop this (which is a proof of concept piece of work) that much more of an uphill struggle. What IT have done for me is placed the db onto our active directory so that all users can only access the front end via the icon in their start up menu's. Only myself has access to the back tables or design view. The data I need to import is from our asset management system and is scheduled into a drive on a 6 hourly schedule (excel format). Currently every time a user opens the db it imports the whole report each time, you can imagine the strain when 2-5 people log in at the same time. This would mean multiple instances of the report (approx. 60k+ records) importing at the same time. As I cant simply link the report to a table (report is in excel format) because it will make it single user and lock the table out to other users.

This is why I started thinking about the option of reducing the amount of times the data is imported. If I could get it to import just once every 6 hours by a single user and then anyone else within the time frame could just simply open the db without having to import.

I hope that gives a bit more insight into why I'm going down this path and the business constraints I am up against

thanks miff3436

If the problem is that they don't like Access in the first place, your solution will not be well received anyway. But that was the case with the Navy until someone pointed out that they had bought a license including it and therefore were wasting money.

you can imagine the strain when 2-5 people log in at the same time. This would mean multiple instances of the report (approx. 60k+ records) importing at the same time.

Click to expand...

Welcome to nightmare city. The ONLY ways to implement this to prevent the strain are to (a) only allow one specific user to do this or (b) to implement a semaphore system, in essence a "roll your own" lock for the process.

The "roll your own lock" is based on a table, call it MyLocks or something like that. Put in three fields - a brief lock name and a user ID and a time stamp.

Then write some code that in overview is similar to this:


In essence, try to set the lock IF someone else doesn't already own it (because the first WHERE includes that the lock should be 0.) If you get the lock, it will have your ID in it. BUT if you try and DON'T get the lock, you have to do something else. The Lock_Not_Mine case would occur if two users logged in at very nearly the same time and entered a "race" to set the lock. Whoever gets there first will try to set the lock. It actually doesn't matter who DOES get the lock. What matters is that only ONE of them gets the lock.

That something else COULD be to query the lock time via DLookup and if it is more than X seconds old, take over the lock anyway. Or you could issue a pop-up saying "Lock not available, click when ready to try again" and loop until you DO get the lock. BUT in your context, if someone else owns a lock less than 6 hours old, your function has already been done and you don't NEED to process the report.

Not saying this is fool-proof. But this is how you avoid the "free-for-all" that ensues if you have folks logging in and suddenly they all try to do the import.

By the way, getting a straight "NO" from the head of IT betrays ignorance on his part. Disciplined use of Access is no worse than disciplined use of Word. Do they restrict use of Word? Excel ALSO has VBA behind it. Do they restrict Excel?