Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2004
    Posts
    41

    Question Unanswered: Set Import Location in Table

    Hello,
    I am setting up a database to share with dozens of users, and therefore it needs to be versatile. It imports multiple text files with set filenames, but I would like the other administrators to be able to change the import location in a table, and then use that location (e.g. C:\Temp\) in the code for all the files? Currently everything is set up in a macro, but I'm switching over to code, but this has me stumped.

    In the example below, I would want the folder location to pull from a text field in a table, but the filename would be set.

    Code:
    DoCmd.TransferText acImportDelim, "Import Specification", "myTable", C:\Temp\myTextFile.txt, yes
    Any help would be greatly appreciated!
    Cheeers,
    Reg

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You could use DLookup or a recordset to get the location from the table:

    Dim strLocation As String
    strLocation = DLookup(...)
    strLocation = strLocation & "myTextFile.txt"
    DoCmd.TransferText acImportDelim, "Import Specification", "myTable", strLocation, yes
    Paul

  3. #3
    Join Date
    Dec 2004
    Posts
    41
    Many thanks for the reply pbaldy!

    The DLookup worked like a champ once I figured how to set up the expression and domain ... awesome!
    Last edited by Reg; 03-13-08 at 20:34.

  4. #4
    Join Date
    Dec 2004
    Posts
    41
    One follow up question ... would I need to repeat "Dim strLocation As String" for each differently named text file?

    Or, just this since I would just be changing the value of "strLocation"?

    strLocation = DLookup("[Field]", "Table")
    strLocation = strLocation & "myTextFile.txt"
    DoCmd.TransferText acImportDelim, "ImportSpec", "Table", strLocation, yes


    Many thanks!

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'm not clear on your overall process/goals. If you were going to use the same location for various text files, I'd probably do this:

    Dim strLocation As String
    Dim strFileLoc As String

    strLocation = DLookup(...)

    strFileLoc = strLocation & "myTextFile.txt"
    DoCmd.TransferText acImportDelim, "Import Specification", "myTable", strFileLoc, yes

    strFileLoc = strLocation & "SecondFile.txt"
    DoCmd.TransferText acImportDelim, "Import Specification", "myTable", strFileLoc, yes
    Paul

  6. #6
    Join Date
    Dec 2004
    Posts
    41
    That's exactly what I was looking for ... I figured there was an easier way.

    Thanks again!

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problem; glad it worked for you.
    Paul

Posting Permissions

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