Results 1 to 6 of 6
  1. #1
    Kundan is offline Competent Performer
    Windows XP Access 2013 32bit
    Join Date
    Mar 2018
    Posts
    155

    Regarding Import/Export

    When we Import or Export Data in Access there is an option of saving the steps. Where are the steps saved and how can we use them in future?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Saved imports/exports are saved in two hidden system tables.
    The names you used when saving are stored in MSysIMEXSpecs & the full details in MSysIMEXColumns

    Click image for larger version. 

Name:	ImportExpiortSpecs.PNG 
Views:	24 
Size:	6.8 KB 
ID:	33772

    To view the tables, tick system objects in Navigation Options
    Although parts of each table can be edited, DON'T do so or you will mess up the saves you have done

    To reuse or modify them, click Saved Imports or Saved Exports in the External Data ribbon
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67
    There are two different ways to save the Import/Export steps. The solution described by Colin stores the field specs in the hidden system tables. These are utilized through the "Advanced..." and then "Specs..." in the Import Wizard.

    Click image for larger version. 

Name:	Access_Import_Specs.jpg 
Views:	22 
Size:	197.0 KB 
ID:	33773

    The MSysIMEXColumns and MSysIMEXSpecs tables can be edited like any other table.

    After going through this process, there is the option to "Save Steps" which will save the import job in XML format. This can be found under the "Saved Imports" or "Saved Exports" and can be manipulated in VBA in needed.
    Click image for larger version. 

Name:	Access_Import_XML_Specs.jpg 
Views:	22 
Size:	122.4 KB 
ID:	33774

    To see the XML details (and more importantly to figure out what to change) use the VBA commands:

    ? CurrentProject.ImportExportSpecifications.count
    ? CurrentProject.ImportExportSpecifications(0).name
    ? CurrentProject.ImportExportSpecifications(0).descr iption
    ? CurrentProject.ImportExportSpecifications(0).xml

    Where "0" is the index number. The XML is a string specification that can be changed and set e.g.
    You can make a new specification with ADD.
    currentproject.importexportspecifications.add "NewImportSpec" validXMLstring


    Peter

  4. #4
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67
    P.S. - The reason for doing this is to use the same Import/Export specification multiple times.
    The default "Save Steps" assumes you are importing/exporting the same Internal and External table. This is useful if you are routinely exporting a query result to an excel file (for example).
    However, if your file names change then you can use the Wizard to quickly apply the same specifications while changing table names.

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    [QUOTE PeterM]The MSysIMEXColumns and MSysIMEXSpecs tables can be edited like any other table.[/QUOTE]
    MOST of the fields can be edited but not all e.g. SpecID can't be altered in MSysIMEXSpecs - I still don't recommend the OP does so

    Thanks for adding the XML aspect which I forgot about
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    Kundan is offline Competent Performer
    Windows XP Access 2013 32bit
    Join Date
    Mar 2018
    Posts
    155
    Quote Originally Posted by ridders52 View Post
    Saved imports/exports are saved in two hidden system tables.
    The names you used when saving are stored in MSysIMEXSpecs & the full details in MSysIMEXColumns

    Click image for larger version. 

Name:	ImportExpiortSpecs.PNG 
Views:	24 
Size:	6.8 KB 
ID:	33772

    To view the tables, tick system objects in Navigation Options
    Although parts of each table can be edited, DON'T do so or you will mess up the saves you have done

    To reuse or modify them, click Saved Imports or Saved Exports in the External Data ribbon
    Thanks!!! God Bless You!

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

Similar Threads

  1. Import/Export dat into SQL
    By Ray67 in forum Import/Export Data
    Replies: 7
    Last Post: 05-05-2017, 03:18 PM
  2. My saved import/export are gone
    By egyhamada in forum Import/Export Data
    Replies: 4
    Last Post: 02-06-2016, 08:47 AM
  3. Data Import/Export
    By revvedmoto in forum Access
    Replies: 15
    Last Post: 02-15-2014, 07:31 PM
  4. Automating import and export
    By trotta in forum Access
    Replies: 4
    Last Post: 02-19-2013, 06:51 AM
  5. export/import by email
    By sue tang in forum Import/Export Data
    Replies: 5
    Last Post: 03-14-2012, 04:41 PM

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