How can I modify a saved Microsoft Access 2007 or 2010 Import Specification?

Question

I am able to use this feature on my machine using MS Access 2007.

• On the Ribbon, select External Data
• Select the "Text File" option
• This displays the Get External Data Wizard
• Specify the location of the file you wish to import
• Click OK. This displays the "Import Text Wizard"
• On the bottom of this dialog screen is the Advanced button you referenced
• Clicking on this button should display the Import Specification screen and allow you to select and modify an existing import spec.

For what its worth, I'm using Access 2007 SP1

I don't believe there is a direct supported way. However, if you are desparate, then under navigation options, select to show system objects. Then in your table list, system tables will appear. Two tables are of interest here: MSysIMEXspecs and MSysIMEXColumns. You'll be able edit import and export information. Good luck!

Tim Lentine's answer seems to be true even in the full release. There is just one other thing I would like to mention.

If you complete your import without going into "Advanced..." and saving the spec, but you do save the import for reuse at the end of the wizard (new feature AFAIK), you will not be able to go back and edit that spec. It is built into the "Saved Import". This may be what Knox was referring to.

You can, however, do a partial work around:

1. Import a new file (or the same one all over again) but,
2. This time choose to append, instead of making a new
3. Click OK.
4. Go into "advanced" All your column heading and data-types will be there.
5. Now you can make the changes you need and save the spec inside that dialog. Then cancel out of that import (that is not what you wanted anyway, right?)
6. You can then use that spec for any further imports. It's not a full solution, but saves some of the work.

Below are three functions you can use to alter and use the MS Access 2010 Import Specification. The third sub changes the name of an existing import specification. The second sub allows you to change any xml text in the import spec. This is useful if you need to change column names, data types, add columns, change the import file location, etc.. In essence anything you want modify for an existing spec. The first Sub is a routine that allows you to call an existing import spec, modify it for a specific file you are attempting to import, importing that file, and then deleting the modified spec, keeping the import spec "template" unaltered and intact. Enjoy.

Public Sub MyExcelTransfer(myTempTable As String, myPath As String)
On Error GoTo ERR_Handler:
    Dim mySpec As ImportExportSpecification
    Dim myNewSpec As ImportExportSpecification
    Dim x As Integer

    For x = 0 To CurrentProject.ImportExportSpecifications.Count - 1
    If CurrentProject.ImportExportSpecifications.Item(x).Name = "TemporaryImport" Then
        CurrentProject.ImportExportSpecifications.Item("TemporaryImport").Delete
        x = CurrentProject.ImportExportSpecifications.Count
    End If
    Next x
    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
    exit_ErrHandler:
    For x = 0 To CurrentProject.ImportExportSpecifications.Count - 1
    If CurrentProject.ImportExportSpecifications.Item(x).Name = "TemporaryImport" Then
        CurrentProject.ImportExportSpecifications.Item("TemporaryImport").Delete
        x = CurrentProject.ImportExportSpecifications.Count
    End If
    Next x
Exit Sub    
ERR_Handler:
    MsgBox Err.Description
    Resume exit_ErrHandler
End Sub

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

When I want to examine or change an import / export specification I query the tables in MS Access where the specification is defined.

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

You can also use an UPDATE or INSERT statement to alter existing columns or insert and append new columns to an existing specification. You can create entirely new specifications using this methodology.

Another great option is the free V-Tools addin for Microsoft Access. Among other helpful tools it has a form to edit and save the Import/Export specifications.

Note: As of version 1.83, there is a bug in enumerating the code pages on Windows 10. (Apparently due to a missing/changed API function in Windows 10) The tools still works great, you just need to comment out a few lines of code or step past it in the debug window.

This has been a real life-saver for me in editing a complex import spec for our online orders.