Help Automatically running and Update Query

[JohnRandolphSTL]

I have an excel spreadsheet that I have as a linked table within Access. That excel spreadsheet updates on a daily basis. I have another table within access that is pulling information out of the linked excel table. I have a query set up that will update the information in the existing table from the linked excel table.

Is there any way I can have this query automatically run every night? Or is there a way that I can have the query automatically update when I open the program?

[pbaldy]

You can do either. If you have multiple users, I'd go with the first (otherwise every user is running the update). I'd create an MDB/ACCDB that did your update when it started, then closes itself. Then I'd run that file from Windows Scheduled Tasks.

[JohnRandolphSTL]

How would I tell the Scheduled Task to run it though?

[pbaldy]

Not sure what you mean. You'd specify your MDB in the Run box in Scheduled Tasks. In your MDB, you can use an AutoExec macro to kick off your process.

[JohnRandolphSTL]

That's where my problem is...I don't know how to set up an AutoExec macro. Do you know code for that by chance? Or the steps I would need to take in Access to create one.

[pbaldy]

You simply create a macro that does what you want, either within itself or by calling a VBA function that does what you want, and name it "autoexec". Any macro with that name is automatically run on startup.

[JohnRandolphSTL]

Awesome! Thank you! But now I get the message: 'You won't be able to undo the changes this action query is about to make to the data in a linked table or tables' every time the schedule task runs. Where would I remark out that I am ok with this? Here is my code so far.

UPDATE q_VoltageForDay INNER JOIN ServicePoint ON q_VoltageForDay.BI_SRV_MAP_LOC = ServicePoint.MAP_NO SET ServicePoint.Voltage_Min = [q_VoltageForDay].[Min], ServicePoint.Voltage_Average = [q_VoltageForDay].[Average], ServicePoint.Voltage_Max = [q_VoltageForDay].[Max];

[pbaldy]

You're just executing the query from the macro? You can either:

Put a SetWarnings action before executing, setting them to False. Then after executing the query set them back to True.

In 2007 click on the Office Button then Access Options. On the Advanced tab uncheck the box under Confirm for Action Queries.

[JohnRandolphSTL]

I have all of those boxes unchecked. When I have that option checked it gives me three dialog boxes. Right now all three of those boxes are unchecked, 'Record Changes' 'Document deletions' and 'Action queries' but I am still receiving that last message. I don't know how to set a warning within that sql table. I'm fairly new to access. How would the code look with my SQL statement?