Analyst/Consultant/Coder

MS Access Import/Export Specifications

101 Secrets of an Excel Addict

101 Secrets of an Excel Addict

Have you ever wondered where MS Access stores the Import/Export specifications that you create? Have you ever tried to figure out how to export, document, or change them? After inheriting an MS Access application I needed to locate, document and change an existing export specification.

Microsoft Access stores Import and Export Specifications in two system tables:

  • MSysIMEXSpecs
  • MSysIMEXColumns

These tables can be joined using the SpecID column. Here is a handy piece of SQL code you can execute from within Access itself to extract the specification of your choice.

If you happen to be handy with SQL you can make changes to a specification and adjust existing fields or add new fields by executing SQL statements against these same tables. This can be much easier than recreating a specification from scratch.

5 Responses to MS Access Import/Export Specifications

  • Great article. I needed to have more control over importing Excel spreadhseets into Access 2010 and TransferSpreadsheet doesn’t help. Based on your article an some additional research, I ended up using the ImportExportSpecification. Here are a couple of handy little routins to customize the importexport XML once you have created one using ” Save Steps”.

    'For modifying the name And/Or the XML
    Public Sub fixImportSpecs(myTable As String, strFind As String, strRepl As String)
        Dim mySpec As ImportExportSpecification
        
        Set mySpec = CurrentProject.ImportExportSpecifications.Item(myTable)
        
        mySpec.XML = Replace(mySpec.XML, strFind, strRepl)
        Set mySpec = Nothing
    
    End Sub
    
    Public Sub MyExcelChangeName(OldName As String, NewName As String)
        Dim mySpec As ImportExportSpecification
        Dim myNewSpec As ImportExportSpecification
        Set mySpec = CurrentProject.ImportExportSpecifications.Item(OldName)
        
        CurrentProject.ImportExportSpecifications.Add NewName, mySpec.XML
        mySpec.Delete
        Set mySpec = Nothing
        Set myNewSpec = Nothing
    
    End Sub
    
    'to run the import spec from vba code
    Public Sub MyExcelTransfer(myTempTable As String, myPath As String)
        Dim mySpec As ImportExportSpecification
        Dim myNewSpec As ImportExportSpecification
        Set mySpec = CurrentProject.ImportExportSpecifications.Item(myTempTable)
        CurrentProject.ImportExportSpecifications.Add "TemporaryImport", mySpec.XML
        Set myNewSpec = CurrentProject.ImportExportSpecifications.Item("TemporaryImport")
        
        myNewSpec.XML = Replace(myNewSpec.XML, "\\MyComputer\ChangeThis", myPath)
        myNewSpec.Execute
        myNewSpec.Delete
        Set mySpec = Nothing
        Set myNewSpec = Nothing
    
    End Sub
    

    Hope this helps someone. cheers.

    • drdave4394 says:

      Thanks so much for this code. I do not have VBA experience but this sounds like what I am looking for. After playing with it, I can not seem to get it to run. Can you maybe direct me as to what parts of the code I need to change with my database’s locations?

      The saved import I want to edit is called “TwitterPersonalBackup”
      It is a pull from twitter’s website and I am looking to change the URL.
      I have a Query which is building the next URL to search. Is it possible to have the new URL change based on that Query?

      Thanks so much!

  • vishwas says:

    Hi Andrew,

    Thanks for the above info. I was finding out how to save the SPECS in SQL (TOAD), when we want to import the text data into a table in SQL. (we do have a import/export SPECS in Access, do we have samething in toad?)

    I was just trying to save the SPECS, like we do in the ACCESS in SQL. Please help you if have any idea.

    Thanks,
    Vishwas

    • Andrew says:

      Unfortunately I do not have any experience with TOAD. I am completing and useful code library named IncreLogic.ETL that can be found at IncreLogic.com. The library has the functionality to build and then store and retrieve import/export specifications that include complex data transforms, There is no GUI though as it it primarily for programmers.

  • Thanks for the MS Access Import/Export Specifications
    hint, I had to reconstruct one of these from an old Access database and it’s just what I needed.

    BTW, we have a similar background in age and technology, it was fun to read yours. I started out with the same computer! These days are managing .NET with Oracle and SQL during the day and teaching classes at night at a technical college.

    Nice looking web site.

Copyright © 2011. All Rights Reserved.