Automate exporting tables from Access using Visual Basics

Question

I want to automate Access to export tables every time I close the database, or at least have it happen once a day. It says you can automate the exporting, but I can't for the life of me find the command or function to do it.

I used VBA to set up a Module, where I used the DoCmd.TransferText acExportDelm command to transfer 10 tables to a folder. The module works just fine. However, it is annoying to have to remember to do it every time I finish doing my work. It says on Microsoft's website that one can automate the process, and I need help find out how.

Answer 1

There is also the possibility to use Windows (the OS) Task Scheduler.

In your Access application, define a Macro which would initiate the operation you are interested to perform.

In the task scheduler, run: MSACCESS.EXE  YourDatabaseApp.accdb /X YourMacro

and select the time of the day you want that macro to be executed.

You only need MS Access for that solution, and some know-how about your OS. Under Vista, simply make a Start Search on Task Scheduler.

Answer 2

Why?  What are you trying to "solve" by doing this?  "How" depends on "what"...

Answer 3

I like Gina and Scott's suggestions but if they aren't what you are looking for, another option would be to do the following:

Schedule an import or export operation

http://office.microsoft.com/en-us/access/HA012258131033.aspx

HTH,

Kathy

* Please post a response to let us know whether our answers have helped or not.

Answer 4

If you want this done automatically on close, you need to have a form that opens with the database and remains open all the time. In mmany of my apps, I have a login form to identify the current user. After they login, I set the Visible property of the form to No so it becomes hidden and can't be manually closed. In the ON Close event of that form, I put code to do cleanup chores such as you want done. When the user closes the app by any means, the code in that On Close event is run.

---

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2009 Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

Answer 5

Do  you have a Main Menu or Switchboard in your database?  You *could* put a form to open when your Main Menu closes that says something like Please Wait....  Run your code and then have that form close and exit Access.

---

-- Gina Whipp 2010 Microsoft MVP (Access) Please post all replies to the forum where everyone can benefit.