Results 1 to 10 of 10
  1. #1
    xinelo is offline Novice
    Windows 7 32bit Access 2000
    Join Date
    Nov 2011
    Posts
    5

    query Access databases from the command line

    Good day,

    I would like to query several Access databases in a batch process. If it can be done in the background without needing to open the database, it'd even better, but that's not essential.

    I know a bit about SQL and Access, but not about VB or VBA.

    This is what I can do: I can open the .mdb file and run the query and see the results.



    I would like to do the same thing from the command line for several databases. Something like this (imaginary syntax) should work for one database:

    "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "d:\My Documents\cl97.mdb" q:"SELECT Dictionaries.DictName FROM Dictionaries;" > C:\results.txt
    Having that would already be great. For several databases, I imagine I would need to write a loop:

    foreach db in X:\path\to\*\ {
    "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "$db" q:"SELECT Dictionaries.DictName FROM Dictionaries;" >> C:\results.txt
    }
    The results.txt file looks includes the name of the database plus the results of the query (the values of the "DictName" fields).

    Is it possible to do any of the above stuff? I've been searching the web but I couldn't find a clear explanation.

    Thank you so much!

    Cheers, Manuel

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Hi Manuel,
    I hope I am not totally off the mark with my response.
    I know you specifically mentioned 'command line' . . .
    but . . .
    There is one option you might consider even though it is not run from a batch file in the command line.
    It involves using a Macro named 'AutoExec'.
    If your databases have a Macro named 'AutoExec' - that macro will execute as soon as you open your database. As a result - any actions that you have in the macro will be caried out.
    I have several Windows Scheduled Tasks set up on my machine to run early each morning.
    They simply open the databases.
    The first action in my AutoExec macros is 'SetWarnings = No'.
    This prevents any Acess messages popping up and stopping execution of the other actions in my AutoExec Macro.
    The last action in my AutoExec Macro is 'Quit Access' - which closes Access.
    You can create your Queries - including a hard-coded field with the name of the database in which they are - and then do an export action in the AutoExec macro that will automatically export the query.
    Actually, what I have is reports [based on my queries] that are exported.

    I hope this helps.
    If this is not what you are looking for, you might re-post because you may get fewer people reading this once there is a reply in here [not really sure about that, though!].

    Let me know if you have any questions.
    And 'Sorry!' if this is not what you are looking for!


  3. #3
    xinelo is offline Novice
    Windows 7 32bit Access 2000
    Join Date
    Nov 2011
    Posts
    5
    Hi Robeen,

    First of all, thank you very for your kind reply.

    Yes, I mentioned command line because that's the way I know to run batch processes, but it doesn't need to be that way. Any alternative method would be perfect as long as the result is equivalent.

    I'll try to find that AutoExec macro and see if I can insert there the orders I want. I guess they have to be written in VBA.

    Thanks again!

    Cheers, Manuel

  4. #4
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    You have to create a Macro and name it 'AutoExec'.

    In the Macro, you will be able to select 'Actions'.

    I think the difficult part is getting to know what actions Access makes available to you - and then figuring out how to use the actions to achieve what you need.

    Let me know if you need help.

  5. #5
    xinelo is offline Novice
    Windows 7 32bit Access 2000
    Join Date
    Nov 2011
    Posts
    5
    Hi again,

    I see I would need to open every database, not just run Access. I didn't have the AutoExec macro, so I've created one. It contains (translating from Spansih, sorry if something doesn't match):

    Code:
    Action|Comment
    SetWarnings|No
    ExecuteSQL|SELECT Dictionaries.DictName FROM Dictionaries;
    Save|file.txt
    Log off
    I can run the macro, but it says "SQL instruction is not valid".

    Any help? Does it look like I am on the right track?

    Cheers, Manuel

  6. #6
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Do you have an ImportExport Data option in your Macro?
    See if you can find that option.
    I think in your case - you need to export your query to a text file.
    Whatever you need in your text file will need to be included in the Query.

  7. #7
    xinelo is offline Novice
    Windows 7 32bit Access 2000
    Join Date
    Nov 2011
    Posts
    5
    Thanks Robeen.

    I don't see an ImportExport Data option. The closest seems to be "Transfer text". However, the error that I get happens before getting to that, when executing the query itself.

    Something else. Would I need to create the same macro in every database where I want to run it?

    Cheers, Manuel

  8. #8
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    I have never used the ExecuteSQL action.
    My guess is that the Execute SQL action will not work in this case.

    Do you have a button somewhere that says something like 'Show All Actions'?

    I'm sorry that I do not have Access 2000 here at work - so I don't know what you have available in the 2000 version. I'll have to check at home this evening.

    But I think you are on the right track.
    Try Google - put in 'MS Access 2000 Export Data'.
    You might find some help there . . .

    I will look at Access 2000 when i get home this evening.

  9. #9
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Hi Manuel!

    I didn't answer one of your questions.

    Yes - you will need a Macro named AutoExec in each of the Databases in which you have data that you want written out automatically.

    Another thing you might research is 'DoCmd'.
    Here's a microsoft page: http://msdn.microsoft.com/en-us/libr...ffice.10).aspx
    . . . it says this:
    In Microsoft Access 2000, you can't use a SQL statement to specify data to export when you're using the TransferText action or the TransferSpreadsheet action. Instead of using a SQL statement, you must first create a query and then specify the name of the query in the Table Name argument.
    I know this may not solve your problem - but I hope it gives you something to work on!!

    If it is any encouragement to you - I think what you are trying to do is not difficult - and once you've been able to do it once - for one database - it iwll become easy for you to do with all the other databases.

    Don't hesitate to let us know if you need more help!

  10. #10
    xinelo is offline Novice
    Windows 7 32bit Access 2000
    Join Date
    Nov 2011
    Posts
    5
    Thanks, Robeen.

    I haven't had time to explore this thing further. If I need to do the same thing in every database, it really wouldn't help, because I need more or less as much time to open each database and create the macro as I would need to open each database and check visually the table that I want to query with the macro.

    I need something that I can run once for many databases. There's a bit above 1.000 of them.

    I'll have a look at DoCmd. I had seen in my searchs it but, not knowing whether it would be useful, I hadn't gone very deep into it.

    Thanks again for all your help!
    Manuel

Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA and command line
    By dsk96m in forum Access
    Replies: 1
    Last Post: 09-04-2011, 08:06 PM
  2. Replies: 2
    Last Post: 10-11-2010, 03:15 AM
  3. Need help linking access databases
    By AlphaNumeric in forum Access
    Replies: 2
    Last Post: 05-18-2010, 04:08 PM
  4. Pass command line parameters
    By lanto3000 in forum Access
    Replies: 2
    Last Post: 03-21-2010, 03:53 PM
  5. Replies: 1
    Last Post: 08-31-2009, 10:24 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums