Export a Table from a Command Line

1. Create and save a macro with the following two lines:

TransferText (to export; it will give you a place to specify the table name and filename)
Quit (to close Access once you're done

2. Create a scheduled task that runs the following line:

"C:\path to Access\MSACCESS.exe" "C:\path to your DB\YourDB.mdb" /x YourMacro

Be sure to include quotation marks if their are spaces in your path.  MSACCESS.exe is usually located in "C:\Program Files\Microsoft Office\Office?\", where ? is a number that depends on your version of access.  The /x flag tells Access to open and run the macro specified.

ok.  i don't write macros so i don't even know where to begin to write this macro.  suggestions?

ok so far so good, but my db is password protected.  I entered /pwd and my password in the command line after the file path to my db.

"C:\Documents and Settings\All Users\Start Menu\Programs\Microsoft Office\Microsoft Office Access 2003.lnk" "U:\Sspivey\My Documents\Prospectus Central\Database\PCentral Live.mdb" /pwd ******** /x ExportProspectus

When I run the command line manually I get a window asking me for a username (which is already populated with my windows username) and password which is blank.  What am I missing?

Have you tried including both the user id and password?

"C:\Documents and Settings\All Users\Start Menu\Programs\Microsoft Office\Microsoft Office Access 2003.lnk" "U:\Sspivey\My Documents\Prospectus Central\Database\PCentral Live.mdb" /user useridhere /pwd ******** /x ExportProspectus

If you're using a specific workgroup file, you'll need to include that too.

"C:\Documents and Settings\All Users\Start Menu\Programs\Microsoft Office\Microsoft Office Access 2003.lnk" "U:\Sspivey\My Documents\Prospectus Central\Database\PCentral Live.mdb" /user useridhere /pwd ******** /wrkgrp "C:\path to file\yourwrkgrpfile" /x ExportProspectus

when i set up password protection on the db i did not include a username, just a password.  the way i set the password was to open the db exlusively and then went to tools and security and chose set db password.  this option only asks for a password, no username is requested.  i tried to add /user and then put my windows username in there that didn't work either.  I even included the full username with domain PROSPECTUSCENTR\sspivey but nothing seems to work.

ok here is something interesting.  i changed the command line to this to see what would happen:

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "U:\Sspivey\My Documents\Prospectus Central\Database\PCentral Live.mdb" /x ExportProspectus

without using the /pwd command i can manually enter my db password and the macro runs.  but if i go back in and add /pwd either before or after the /x command to run the macro then Access requests a username and password.  is there another command to use for db that only has a password for protection?

Just a guess: The default user for Access is named Admin, so I would suggest trying

/user Admin /pwd ****

that doesn't work either.

put the operation 'inside' the database

create a small form to use as the form to launch when the database opens.

have the Form_open sub launch a macro or module that runs your operation and close the mdb when completed.

open the mdb with the /user xxxxxxx /pwd ******** command line options.

-j-

  By setting the password, your using database level security, and as you already found out, the /pwd switch won't work.

  What you need to do is use one MDB to open another.  Typicall this is called a "Launcher" app.  Here is the link to a MSKB article on opening a password protected MDB through automation:

http://support.microsoft.com/?kbid=235422

Jim D.

i missed that small detail... I recommend dropping the database security to be able to let the app launch it's primary function.  Network security should be able to keep 'prying fingers' away from the mdb.

Hello,

One simple solution. Follow these steps:

1. Create a blank new database, call it for example Nightly.mdb
2. Menu "File / Get External Data / Link Tables...", browse to and select your database
3. Provide the password
4. Select your table and click [OK]
5. Switch to the tab [Macros]
6. Click [New]
7. On the  first row of the macro, choose "TransferText"
8. In the lower pane, specify Type: Export Delimited.
9. Table name: <your guessed it!>
10. And provide the full path for the export:
    U:\Sspivey\My Documents\Prospectus Central\OEP Comparisons\dbo_Prospectus.txt
11. In the second row of the macro, select "Quit"
12. Close and save with the name "AutoExec"
13. Double-click AutoExec...

You now have a new "database" called Nightly.mdb that will export your table to the text file each time you double-click it.

In the task manager, paste a shortcut to that new "database" and set the time...

Cheers!
(°v°)
12. Save

JDettman,

Error 3044

I am getting this errror everytime I try to run the OpenPasswordProtectedDB module.  I copied and pasted the file path directly from Windows Explorer to be sure I didn't have any typos.  What gives?

Here is my file path:

"U:\Sspivey\sspivey's Documents\Prospectus Central\Database\PCentralLive.mdb"

peter57r,

finally.  thx for the help.

scott