MS Access Import/Export Specifications
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
Leave a Reply
You must be logged in to post a comment.
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 SubHope this helps someone. cheers.
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!
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
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.