[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Export a Table from a Command Line

Posted on 2006-05-05
16
Medium Priority
?
1,776 Views
Last Modified: 2012-06-27
I have a table in Access that I need to automatically export every night into a text file.  How do I set up a command line in Windows Scheduler to perform this?  The table I need to export is named dbo_Prospectus and I want it to be exported to this location as a text file named dbo_Prospectus.

U:\Sspivey\My Documents\Prospectus Central\OEP Comparisons
0
Comment
Question by:scottspivey
  • 7
  • 4
  • 2
  • +3
16 Comments
 
LVL 14

Expert Comment

by:JohnK813
ID: 16616841
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.
0
 
LVL 1

Author Comment

by:scottspivey
ID: 16616926
ok.  i don't write macros so i don't even know where to begin to write this macro.  suggestions?
0
 
LVL 14

Accepted Solution

by:
JohnK813 earned 1200 total points
ID: 16617188
It's very simple in this case.

1. In your Database Window (the one that lists all your tables, forms, etc), click Macros, then New.

2. You'll see two columns: Action and Comments.  In the first row of the Actions column, use the dropdown to select TransferText.  Below you'll see some options.  Set
Transfer Type to Export Delimited
Table Name to dbo_Prospectus
and File Name to U:\Sspivey\My Documents\Prospectus Central\OEP Comparisons\dbo_Prospectus.txt.
If you want to include your field names in the file, set Has Field Names to Yes.

3. In the row below TransferText Action column (we're back up top now), select Quit from the dropdown box.

4. That's it!  Save your macro (preferrably without a space in the name), and use it in the command line of your scheduled task.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:scottspivey
ID: 16618150
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?
0
 
LVL 14

Expert Comment

by:JohnK813
ID: 16630441
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
0
 
LVL 1

Author Comment

by:scottspivey
ID: 16630499
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.
0
 
LVL 1

Author Comment

by:scottspivey
ID: 16631117
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?
0
 
LVL 14

Expert Comment

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

/user Admin /pwd ****
0
 
LVL 1

Author Comment

by:scottspivey
ID: 16631577
that doesn't work either.
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 800 total points
ID: 16633547
If you remove the Database password first, and then set a password for the Admin user (through Tools>Security>User And Group Accounts) then you will achieve the same effect on your machine as setting a database password.  However, this username and password can be included in the shortcut you use for your scheduled job.

I stress this will only affect YOUR machine.  If you move the databse to somewhere else no password will be required.
The downside will be that you have to use your password for all Access databases on your machine.

You can get round that by creating a workgroup file with a specific name (not exactly the same name as your db), joining that workgroup  BEFORE you set the password and then specifying that workgroup file in the startup command for your application.  You create a new workgroup file (xxx.mdw) using the workgroup administrator program or menu option (depends on your version of access as to which applies).

So steps are:

Remove database password
Create workgroup file
Join workgroup file
Apply password to Admin
Exit database
Create shortcut for 'daytime'  use, specifying your workgroup file.
Create shortcut for scheduled use, specifying workgroup file, username and password.


Pete
0
 
LVL 32

Expert Comment

by:jadedata
ID: 16633556
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-
0
 
LVL 58
ID: 16633578

  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.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 16633636
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.
0
 
LVL 58

Expert Comment

by:harfang
ID: 16634256
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
0
 
LVL 1

Author Comment

by:scottspivey
ID: 16634615
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"
0
 
LVL 1

Author Comment

by:scottspivey
ID: 16635058
peter57r,

finally.  thx for the help.

scott
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question