Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Run a DOS command from Access (2003 SP2)

    From the DOS prompt screen I run the command "Dir M:Synoptic_Coding_Admin_AccessDB_Processing /s >> C:SCPFiles.txt" to build a list of files in the folder and copy them to a text file. I then import the text file into Access to be used in a procedure that imports data from each file in the list into a table. How can I run this DOS command from Access?
    Thanks
    chuck

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run a DOS command from Access (2003 SP2)

    Welcome to Woody's Lounge!

    You can use the following:
    <code>
    Shell "cmd /c Dir M:Synoptic_Coding_Admin_AccessDB_Processing /s >> C:SCPFiles.txt"</code>

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Run a DOS command from Access (2003 SP2)

    Given your description, there doesn't seem to be much point in creating the text file. You could use the Dir command within your import procedure to specify which files to import from?
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Thanks for the help

    Wow! I've got a lot to learn. I will try the Dir command within the import procedure. Thanks.
    Thanks
    chuck

  5. #5
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Run a DOS command from Access (2003 SP2)

    I tried your suggestion in the immediate window and it worked fine, but I discovered some folder names will not work. One such folder is
    M:Synoptic Coding Project - 2006 FebruaryCompleted - Admin Reviews. In the DOS window it will work with quotes. In the immediate window quotes give me an error so I tried brackets. No error and the file is created, but no data. Any suggestions?

    chuck
    Thanks
    chuck

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run a DOS command from Access (2003 SP2)

    Try putting doubled quotes around the path:
    <code>
    Shell "cmd /c Dir ""M:Synoptic Coding Project - 2006 FebruaryCompleted - Admin Reviews"" /s >> C:SCPFiles.txt"
    </code>
    When you want to include quotes within a quoted string, you must double them, otherwise VBA gets confused over where the string ends.

  7. #7
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Run a DOS command from Access (2003 SP2)

    That did it, Hans. Thanks.
    Thanks
    chuck

  8. #8
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Run a DOS command from Access (2003 SP2)

    When I read your suggestion, rather than ask how I would do it, I thought I would just do what I understood for now. Creating the text file was working...but then I began to get inconsistent results. When I set up the import specification I walked through the wizard and choosing TAB delimited it indicated two columns. The second column had the data I wanted so I imported column two. Today the same file, TAB delimited, is only one column so I get nothing. I walked through the wizard and if I use COMMA delimited I get two columns again but it splits at a different point. Now I am really confused. Then I remembered your suggestion to use the Dir command within my procedure, so I decided to ask how to modify my code to skip making the text file.

    Here is my current procedure:

    Private Sub cmdGetFileList_Click()
    Shell "cmd /c del c:SCPFiles.txt"
    Shell "cmd /c dir ""M:Synoptic Coding Project - 2006 FebruaryCompleted - Admin Reviews"" /s" _
    & ">>c:SCPFiles.txt"
    MsgBox "This step imports SCPFiles.txt into the impSCPFiles table.", vbInformation, "Button Explanation"
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qry_EMPTY_impSCPFiles"
    DoCmd.OpenQuery "qry_EMPTY_tblFileNames"
    DoCmd.TransferText acImportDelim, "SCPFiles Import Specification", _
    "impSCPFiles", "C:SCPFiles.txt", False
    DoCmd.OpenQuery "qryAppendFileNames"
    DoCmd.SetWarnings True
    MsgBox "Import Completed"
    End Sub
    Thanks
    chuck

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Run a DOS command from Access (2003 SP2)

    Do you need to loop through subdirectories? (and, if so, do you need to record the name of the subdirectory?) If not, something like:
    <pre>Sub FillFileList(strPath As String)
    Dim strTableName As String, strFileName As String
    Dim rst As ADODB.Recordset
    strTableName = "tblFileNames"
    Set rst = New ADODB.Recordset
    rst.Open strTableName, CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdTable
    strFileName = Dir(strPath & "*.*", vbNormal)
    Do Until strFileName = ""
    With rst
    .AddNew
    .Fields("FileName") = strFileName
    .Update
    End With
    strFileName = Dir
    Loop
    rst.Close
    Set rst = Nothing
    End Sub
    </pre>

    should do what you want, though you may need to change table and/or field names to suit. Just call it with <code>FillFileNames "C:Foldername"</code>
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Run a DOS command from Access (2003 SP2)

    That is so slick. My comments always credit Woody's Lounge. Thanks for the help.
    Thanks
    chuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •