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

Asked 27/9, 2008 at 11:3 Answered 6/11, 2020 at 13:36

Does anyone know how to modify an existing import specification in Microsoft Access 2007 or 2010? In older versions there used to be an Advanced button presented during the import wizard that allowed you to select and edit an existing specification. I no longer see this feature but hope that it still exists and has just been moved somewhere else.

Africah answered 27/9, 2008 at 11:3 Comment(0)

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

Schliemann answered 29/9, 2008 at 16:14 Comment(0)

I don't believe there is a direct supported way. However, if you are desperate, 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!

Creighton answered 27/9, 2008 at 12:7 Comment(5)

Those tables are not editable by the user. They can only be edited through Jet. – Oversupply 27/9, 2008 at 22:54

David, I can edit both of the MSysIMEXColumns and MSysIMExSpecs table for naming fields and changing types. Not extensively tested by me, and obviously unsupported, but they ARE editable. – Creighton 28/9, 2008 at 0:18

This must be something that has changed in A2K7, as I have no version of Access that allows me to directly edit them (A97, A2K2, A2K3). – Oversupply 29/9, 2008 at 23:34

You can edit the MSysIMEXColumns and MSysIMEXSpecs tables with queries in Access 2007 and earlier versions. Try something like this: UPDATE MSysIMEXColumns SET FieldName = "LastName" WHERE FieldName = "Field4" AND SpecID = 5 – Scion 1/10, 2008 at 3:29

You, sir, win the Internets today. Thank you so much for this tip!! I'm trying to find my way around a legacy access database and without this, it would take much longer to figure the imports out! Thanks again!! – Success 6/1, 2012 at 19:10

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:

Import a new file (or the same one all over again) but,
This time choose to append, instead of making a new
Click OK.
Go into "advanced" All your column heading and data-types will be there.
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?)
You can then use that spec for any further imports. It's not a full solution, but saves some of the work.

Arlettaarlette answered 13/5, 2009 at 0:20 Comment(2)

Also see: luke.gedeon.name/blog/2009/05/… – Spanjian 19/1, 2011 at 15:50

at the tail of end of 2012 this is apparently still the only method, with Office 2010. Thanks for documenting the workaround. – Cosmopolite 20/11, 2012 at 19:44

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

Accra answered 21/1, 2013 at 16:29 Comment(2)

All I wanted to do was make the path dynamic, but I can affirm that the second sub works great in Windows10, Office2016 (replacing strFind with "c:\dynamicname.txt", strRepl with actual location, and then replacing it back before setting mySpec to nothing). – Diminished 17/1, 2017 at 16:43

Hi Mike your solution worked perfectly for me, thank you. I added some code to modify the xml-object instead of replacing strings to change the path, I added the additional code in an answer below here – Healthful 6/11, 2020 at 13:41

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.

Claustral answered 28/7, 2013 at 22:17 Comment(0)

Why so complicated?

Just check System Objects in Access-Options/Current Database/Navigation Options/Show System Objects

Open Table "MSysIMEXSpecs" and change according to your needs - its easy to read...

Undo answered 26/2, 2014 at 17:12 Comment(1)

This is the same as the 2008 answer from Knox. – Jacktar 3/6, 2014 at 15:38

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.

Islas answered 6/3, 2017 at 17:53 Comment(0)

Tim Lentine's answer works IF you have yours specs saved. Your question did not specify that, it only stated you had imported the data. His method would not save your specs that way.

The way to save the spec of that current import is to re-open the import, hit "apend" and that will allow you to use your current import settings that MS Access picked up. (This is useful if your want to keep the import specs from an Excel format you worked on prior to importing into MS ACCESS.)

Once you're in the apend option, use Tim's instructions, which is using the advanced option and "Save As." From there, simply click cancel, and you can now import any other similar data to various tables, etc.

Roselani answered 12/3, 2014 at 15:16 Comment(1)

You are not referencing Luke Gedeon, and this answer is agreeding with Tim Lentine's answer, and adds additional information which was not previously given. Smandoli, seems like you're going around dropping useful points for no reason. – Roselani 27/6, 2014 at 21:11

I have just discovered an apparent bug in the whole Saved Import/XML setup in Access. Also frustrated by the rigidity of the Saved Import system, I created forms and wrote code to pick apart the XML in which the Saved Import specs are stored, to the point that I could use this tool to actually create a Saved Import from scratch via coded examination of a source Excel workbook.

What I've found out is that, while Access correctly imports a worksheet per modifications of default settings by the user (for example, it likes to take any column with a header name ending with "ID" and make it an indexed field in the resulting table, but you can cancel this during the import process), and while it also correctly creates XML in accordance to the user changes, if you then drop the table and use the Saved Import to re-import the worksheet, it ignores the XML import spec and reverts back to using its own invented defaults, at least in the case of the "ID" columns.

You can try this on your own: import an worksheet Excel with at least one column header name ending with "ID" ("OrderID", "User ID", or just plain "ID"). During the process, be sure to set "Indexed" to No for those columns. Execute the import and check "Save import steps" in the final dialog window. If you inspect the resulting table design, you will see there is no index on the field(s) in question. Then delete the table, find the saved import and execute it again. This time, those fields will be set as Indexed in the table design, even though the XML still says no index.

I was pulling my hair out until I discovered what was going on, comparing the XML I built from scratch with examples created through the Access tool.

Genagenappe answered 29/1, 2015 at 18:4 Comment(1)

PS the bug appears to be only with the index option on columns suffixed by "ID". The Saved Import did keep an index I placed on a text field. But when there is a field name ending with ID, it appears to ignore the Index setting in the XML spec. – Genagenappe 29/1, 2015 at 18:42

I used Mike Hansen's solution, it is great. I modified his solution in one point, instead of replacing parts of the string I modified the XML-attribute. Maybe it is too much of an effort when you can modify the string but anyway, here is my solution for that. This could easily be further modified to change the table etc. too, which is very nice imho.

What was helpful for me was a helper sub to write the XML to a file so I could check the structure and content of it:

Sub writeStringToFile(strPath As String, strText As String)
    '#### writes a given string into a given filePath, overwriting a document if it already exists
        Dim objStream
        
        Set objStream = CreateObject("ADODB.Stream")
        objStream.Charset = "utf-8"
        objStream.Open
        objStream.WriteText strText
        objStream.SaveToFile strPath, 2
    End Sub

The XML of an/my ImportExportSpecification for a table with 2 columns looks like this:

<?xml version="1.0"?>
<ImportExportSpecification Path="mypath\mydocument.xlsx" xmlns="urn:www.microsoft.com/office/access/imexspec">
    <ImportExcel FirstRowHasNames="true" AppendToTable="myTableName" Range="myExcelWorksheetName">
        <Columns PrimaryKey="{Auto}">
            <Column Name="Col1" FieldName="SomeFieldName" Indexed="NO" SkipColumn="false" DataType="Double"/>
            <Column Name="Col2" FieldName="SomeFieldName" Indexed="NO" SkipColumn="false" DataType="Text"/>
        </Columns>
    </ImportExcel>
</ImportExportSpecification>

Then I wrote a function to modify the path. I left out error-handling here:

Function modifyDataSourcePath(strNewPath As String, strXMLSpec As String) As String
'#### Changes the path-name of an import-export specification
    Dim xDoc As MSXML2.DOMDocument60
    Dim childNodes As IXMLDOMNodeList
    Dim nodeImExSpec As MSXML2.IXMLDOMNode
    Dim childNode As MSXML2.IXMLDOMNode
    Dim attributesImExSpec As IXMLDOMNamedNodeMap
    Dim attributeImExSpec As IXMLDOMAttribute

    
    Set xDoc = New MSXML2.DOMDocument60
    xDoc.async = False: xDoc.validateOnParse = False
    xDoc.LoadXML (strXMLSpec)
    Set childNodes = xDoc.childNodes
 
    For Each childNode In childNodes
           If childNode.nodeName = "ImportExportSpecification" Then
                Set nodeImExSpec = childNode
                Exit For
            End If
    Next childNode
    
    Set attributesImExSpec = nodeImExSpec.Attributes
    
    For Each attributeImExSpec In attributesImExSpec
        If attributeImExSpec.nodeName = "Path" Then
            attributeImExSpec.Value = strNewPath
            Exit For
        End If
    Next attributeImExSpec
    
    modifyDataSourcePath = xDoc.XML
End Function

I use this in Mike's code before the newSpec is executed and instead of the replace statement. Also I write the XML-string into an XML-file in a location relative to the database but that line is optional:

Set myNewSpec = CurrentProject.ImportExportSpecifications.item("TemporaryImport")
    myNewSpec.XML = modifyDataSourcePath(myPath, myNewSpec.XML)
    Call writeStringToFile(Application.CurrentProject.Path & "\impExpSpec.xml", myNewSpec.XML)
    myNewSpec.Execute

Healthful answered 6/11, 2020 at 13:36 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags