Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 529
  • Last Modified:

Is it possible to schedule a query for export in access?

I have a query that is linked to live data that I run every morning and export to excel.  Is it possible to have this done automatically on a scheduler of some kind?
0
garyrobbins
Asked:
garyrobbins
2 Solutions
 
Kelvin SparksCommented:
The only way I know to do this is to use Windows Scheduler to start Access (probably a copy of your current db) which will have your report built into a macro. The startup string would be used to call the Macro (Google Access startup strings) which would then run the report, export it and close the database.


Kelvin
0
 
garyrobbinsAuthor Commented:
How do i get a Macro to run on Access Startup
0
 
Helen FeddemaCommented:
Years ago, I did this with a VBScript macro:

Set appAccess= CreateObject("Access.Application")
strDBNameAndPath = "E:\Documents\Access 2002-2003 Databases\General.mdb"
appAccess.Visible = True
appAccess.OpenCurrentDatabase strDBNameAndPath
appAccess.DoCmd.RunMacro "mcrPrintOrdersReport"
appAccess.CloseCurrentDatabase
Set appAccess = Nothing

Open in new window


Save it with the .vbs extension, and try running it from the Windows Scheduler.  The last time I tried it was probably in Windows ME and Office XP or thereabouts, so I don't know whether it would work now.
0
 
Helen FeddemaCommented:
You can run a macro from the startup of an Access database (not Access in general) by putting a RunMacro action into an AutoExec macro.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<The startup string would be used to call the Macro (Google Access startup strings) which would then run the report, export it and close the database.>>

  Google Access startup strings?  you've got to be kidding.

There are three ways to fire off:

1. Helen hit the first, call the code from a macro called autoexec.

2. Use the /x command line switch to call a specific macro at start-up (which Kelvin sort of hit)

3. Use the /cmd switch to pass parameters to the DB, view those parameters in code, and react accordingly based on the value.

all those are covered here:

http://www.experts-exchange.com/VP_73.html

at 23:50 in.

Jim.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now