Results 1 to 9 of 9
  1. #1
    Abacus1234 is offline Competent Performer
    Windows 8 Access 2013 32bit
    Join Date
    Nov 2011
    Posts
    212

    How to reverse export of multi-table query to txt file


    I have the following code,

    Code:
    DoCmd.TransferText acExportDelim,  , szQueryName.  szTempTextFilePath, True
    which takes a query with many joined tables and exports it to a fixed width txt file, and after that a mailmerge is performed using the txt file.

    I would like to be able to reverse the process, take the txt file and import it into an access database. I haven't yet been able to do it manually successfully using the wizard, and as many tables are involved it seems it is a complicated process.

    Any advice on step by step approach to get me going?

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You say you want to import it - well, do it. What I guess you want to do in a second step is table updates, which is a whole different animal. Define exactly what you would like to accomplish.

  3. #3
    Abacus1234 is offline Competent Performer
    Windows 8 Access 2013 32bit
    Join Date
    Nov 2011
    Posts
    212
    Yes your guess is correct. And if I could "do it", I certainly would. But I think "doing it" is to big a leap for me now, so I would be grateful for a place to start or a way of thinking about it, that would get me going.

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Use the same syntax to import it. It will require a table name, call it anything you like.
    DoCmd.TransferText acImportDelim

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Abacus1234 View Post
    ... a place to start or a way of thinking about it, that would get me going.
    Since you already know the data that will be imported, I would start by setting up a table with the appropriate data types for each of the fields for the data in the text file.
    Next, do a manual import, set up and save an import specification.

    You say you have "...many joined tables..." in the original export query. You will have to write code to separate the data into their respective tables.
    Now you have to write code to populate the tables in the correct order from the import table..

  6. #6
    Abacus1234 is offline Competent Performer
    Windows 8 Access 2013 32bit
    Join Date
    Nov 2011
    Posts
    212
    Thanks Steve, I am following your suggestions, and realize that thinking "data" is different. I haven't succeeded in step one yet, trying to manage keyfields and foreign keys. to import, I only need the foreign key not other data associated with it. Thanks for your input.

  7. #7
    Abacus1234 is offline Competent Performer
    Windows 8 Access 2013 32bit
    Join Date
    Nov 2011
    Posts
    212
    I have now spent several hours working with the wizard, what a catch-22 joke. Why does it put so much junk in the text file, why is windows the default encoding, one has to begin at the beginning, but this is such a waste of good effort and time.

  8. #8
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you must import manually, save the schema.
    THEN you can import automatically, now the schema is created.

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Are you able to post a sample of the CSV file? Or the CSV file and you dB for analysis/testing???

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

Similar Threads

  1. Export Access table data to flat file (txt file)
    By edmscan in forum Import/Export Data
    Replies: 3
    Last Post: 06-17-2015, 12:03 PM
  2. Replies: 7
    Last Post: 06-30-2014, 12:11 AM
  3. Export Whole table to text file with UTF-8
    By Auto in forum Import/Export Data
    Replies: 3
    Last Post: 08-29-2013, 05:09 PM
  4. Export table to txt file with a variable filename
    By macollins7 in forum Import/Export Data
    Replies: 6
    Last Post: 07-12-2012, 09:44 AM
  5. Reverse updation of table
    By elamaranr in forum Forms
    Replies: 1
    Last Post: 07-10-2011, 10:26 AM

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