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?
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?
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
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
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.
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.
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
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.
[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
Thanks!!! God Bless You!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
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