External Data Export - Unattended Execution

[cmcfarland]

Hello!

I have a data export built, and also have it set as an Outlook Task to make it easy to execute, BUT I'd REALLY like to have it fire off on a schedule and not have to bother with that button push to kick it off.



If I miss firing off the task it messes up some processes that depend on that exported data and things jump right off the rails.

Is there a way for me to have that export run automatically, almost like a batch file that's kicked off by the task scheduler?

Thanks for looking!

[JoeM]

I do this quite often.

In Access, any Macro that you name "AutoExec" automatically fires/runs upon opening.

Likewise, in Access, you can have a Startup Form that loads automatically whenever the database is opened. If you then put VBA code in the "Load" event of that Form, that VBA codes runs automatically when the Form opens.

So, either of those methods will kick off Macros or VBA code upon opening the database. Then just set up a job on Task Scheduler to open your database at your specified time. And then you will have a database that executes automatically!

[cmcfarland]

Nice!

Neither seems too horrible, though I'm really an MS SQL guy fishing around here in the MS Access universe.

Are these the kinds of things where some research on the web would lead me to the scripting that I'd use to create the macro or the VBA bit?

Not afraid of writing script, and I understand that this is the way that you do great things in Access, so I do want to have a grasp.

[JoeM]

How to you run the Export manually?

Note the following Macro Actions:
- TransferText - imports/exports text files
- TransferSpreadsheet - imports/exports Excel files
- OutputTo - another method of exporting information

So, many times you can use one of these in an AutoExec macro in order to export information. There is detailed help on each of the arguments right in the Macro Builder, which makes it pretty easy.

If exporting a text file, you may need to create an Export Specification, which is pretty easy also. Just go through the steps of manually exporting a file, and just before clicking "Finish", click on the Advanced button and save the Import Specification with any name that you want. Then use that Import Specification name in the TransferText action.

If you need to make things a little more dynamic (i.e. not always exporting to the same file name), you can create a Macro that does an example of what you want, then use the "Convert Macros to Visual Basic" functionality found on the Macros menu. This gives you a great start on your VBA code, if you need to go that route.

Hope this gets you started. Post back here if you run into issues.

[June7]

It is also possible for a VBScript to call a procedure in Access. Here is example I use:

Dim accessApp
If (Month(Date()) >= 6 And Month(Date()) <= 9) Or ((Month(Date()) <= 5 Or Month(Date()) >= 10) And Day(Date()) < 8) Then
set accessApp = CreateObject("Access.Application")
accessApp.OpenCurrentDataBase("\\your server name\file path\filename.accdb")
accessApp.Run "ConstructionExtract"
accessApp.Quit
set accessApp = nothing
End If

I have Windows Task Scheduler run this script every Monday morning.

[cmcfarland]

I think that is great, and thank you!

I have the Export Specification built, and have things set so that the file's named the same each time so now I'm off to the Macro Builder to stir up some trouble!

[cmcfarland]

Hello!

I'm finally playing around with this VBS deal, and it's really a lot of fun.

I'm close, but the "accessApp.Run "ConstructionExtract" phrase. I've substituted the 'ConstructionExtract' with the name of my Saved Import, but that doesn't seem to work.

What is supposed to go there???

Thanks!

[June7]

It is supposed to be the name of a procedure in a general module, not an Import/Export specification. The procedure can have code that runs the import/export.

[cmcfarland]

OK, sure, that makes sense! To make a Macro.

I've found a Macro action for RunSavedImportExport, but noticed that when I go into the Macro page I get a message telling me about the other users that are connected to that database (it's true, too, as there's a law practice running off of this Access instance...), and that if I continue my work will not be able to be saved.

Is there something that I should do to disconnect the other people so that I can build my Macro?

Thanks for your help, I really appreciate the guidance.

[June7]

Do you mean macro or VBA? I don't know VBS can call macro, maybe only VBA procedure.

Call them and tell them to close db for an hour.

This is not a split db? Multi-user db should be split. Each user should run their own copy of frontend installed on their workstation or in their user folder on server.

[cmcfarland]

Makes sense.

Will do and report back.

Thank you.