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.

SELECT 
  MSysIMEXSpecs.SpecName,
  MSysIMexColumns.*
FROM 
  MSysIMEXSpecs
  LEFT JOIN MSysIMEXColumns 
    ON MSysIMEXSpecs.SpecID = MSysIMEXColumns.SpecID
WHERE
  SpecName = 'MySpecName'
ORDER BY
  MSysIMEXSpecs.SpecID, MSysIMEXColumns.Start;

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.

“It is a universal truth that the loss of liberty at home is to be charged to the provisions against danger, real or pretended, from abroad.”

~James Madison

Copyright © 2011. All Rights Reserved.