Export works, but spec not in MSysIMEXSpecs list

[damselfish]

I run an online business, and I have a form from which I export selected items into a csv file to upload in my listing software.

The form works fine, the export is created, but I cannot find the export spec for it anywhere. I am looking for the export that is being run by this line of code:
DoCmd.RunSavedImportExport "Export-MyExport". (actual name of the export replaced by MyExport throughout this post)

Here's the whole sub that this line is found in:
Private Sub bExportMy_Click()

DoCmd.RunSQL "delete * from tblTempMyExport"
DoCmd.OpenQuery "CopyOfAdd_qryExportMyInfo"

Dim FileName As String
FileName = CurrentProject.path & "\MyExport.csv"
DoCmd.RunSavedImportExport "Export-MyExport"

MsgBox "File saved!", vbInformation

End Sub

I have checked two ways:
1) directly in MSysIMEXSpecs
2) the sort of back-door way where you start an export, click Advanced and then Specs to get the list of fields in the spec.

There is no spec called MyExport. I can't figure out what I'm missing here.

All help appreciated!

 

[Gasman]

How about just the Saved Exports button in the External Data ribbon?

Perhaps read this page https://isladogs.co.uk/view-edit-imex-data-tasks/index.html

 

[MarkK]

Check out CurrentProject.ImportExportSpecifications, which is a collection of saved ImportExportSpecification objects. To return a member of the collection, you can use its name, or its numeric index.

Note that your code declares and assigns a value to a variable FileName, but that value is never used. This line of code....

DoCmd.RunSavedImportExport "Export-MyExport"

...almost certainly saves the output to a filename defined in the spec's XML.

To amend your code to run that same spec directly from the collection you could do...

Private Sub bExportMy_Click()
   DoCmd.RunSQL "delete * from tblTempMyExport"
   DoCmd.OpenQuery "CopyOfAdd_qryExportMyInfo"
  
   CurrentProject.ImportExportSpecifications("Export-MyExport").Execute

   MsgBox "File saved!", vbInformation
End Sub

This code should perform exactly the same as the code you posted.

 

[damselfish]

Check out CurrentProject.ImportExportSpecifications, which is a collection of saved ImportExportSpecification objects. To return a member of the collection, you can use its name, or its numeric index.

I absolutely agree with you about the function of the DoCmd line. But how do I get to CurrentProject.ImportExportSpecifications to check it out? I think this is fundamentally my problem here. I don't know where the storage container for the spec is, and I don't know how to open it up and look inside. Thank you for responding!

 

[Gasman]

So you did not bother to look at the link I posted?

 

[damselfish]

Gasman, I have tried to reply to you multiple times but for some reason it keeps calling it out as spam. If this posts, I will try again. Here we go....

 

[damselfish]

So you did not bother to look at the link I posted?

Gasman, I have read that link and my issue is very basic. Please remember that I'm a beginner. They give an example of the XML for an import. That's exactly what I would like to see, and potentially change, for my export. The problem is that I don't know how to open or see those lines of code in my database. I hope you can help me.

 

[Gasman]

Read that link.
You use that app that Colin (@isladogs ) created to be able to view and edit the specification. Just import into your dB I believe?

His app makes it very easy to amend the specs.

 

[damselfish]

Read that link.
You use that app that Colin (@isladogs ) created to be able to view and edit the specification. Just import into your dB I believe?

His app makes it very easy to amend the specs.

Thank you Gasman...so OK, there is literally no way to see and fix the XML in Access, without a special separate app? That seems so crazy! (Not directed at you, but at Access).

I read through the rest of the page. I think that downloading and using that app, and importing those various tables and modules to my database just to look inside one export and fix it is probably beyond what I want to take on. I will have to figure out some other way. I appreciate you trying to help me.

 

[isladogs]

As my article makes clear, there are two different methods of importing/exporting data
1. The original IMEX specifications which are stored in two MSysIMEX system tables. These are relatively easy to edit but only work for text files
2. The newer IMEX data tasks which work for a wider variety of files but are stored as XML within your Access app. There is no built-in method of fully editing these which is why I created my helper app. AFAIK there is no easier way of editing the XML . . . so if that’s too hard for you to use then don’t use IMEX data tasks

 

[MarkK]

But how do I get to CurrentProject.ImportExportSpecifications to check it out?

CurrentProject.ImportExportSpecifications is an object in your database. You just type it in, and *poof*, you are there.

Sub ShowIMEXSpecs()
    Dim spec As ImportExportSpecification
    
    For Each spec In CurrentProject.ImportExportSpecifications ' *poof*
        Debug.Print spec.Name
        Debug.Print spec.XML
        Debug.Print
    Next
End Sub

OK, there is literally no way to see and fix the XML in Access, without a special separate app?

Incorrect. The XML property of an ImportExportSpecification object is read/write. You can print the XML. You can create IMEXSpecs on the fly. You can save the XML of a known working Spec to a variable or field in a table, edit it, create a new Spec, load your edited XML into it, run it, and then delete it like it never existed. Sure, there is no fancy spoon-fed XML field editor, but this is not an opaque set objects by any means. You have full-on programmatic access.

 

[damselfish]

CurrentProject.ImportExportSpecifications is an object in your database. You just type it in, and *poof*, you are there.

Sub ShowIMEXSpecs()
    Dim spec As ImportExportSpecification
   
    For Each spec In CurrentProject.ImportExportSpecifications ' *poof*
        Debug.Print spec.Name
        Debug.Print spec.XML
        Debug.Print
    Next
End Sub

Incorrect. The XML property of an ImportExportSpecification object is read/write. You can print the XML. You can create IMEXSpecs on the fly. You can save the XML of a known working Spec to a variable or field in a table, edit it, create a new Spec, load your edited XML into it, run it, and then delete it like it never existed. Sure, there is no fancy spoon-fed XML field editor, but this is not an opaque set objects by any means. You have full-on programmatic access.

Thank you a thousand times! This "It's an object" is the 100% simple thing that I didn't know. I feel very silly. Anyway, I added your sub over in my utilities, and as you say "poof", now I can see the specs and understand exactly why my export comes out looking as it does.

Thanks again!

 

[damselfish]

As my article makes clear, there are two different methods of importing/exporting data
1. The original IMEX specifications which are stored in two MSysIMEX system tables. These are relatively easy to edit but only work for text files
2. The newer IMEX data tasks which work for a wider variety of files but are stored as XML within your Access app. There is no built-in method of fully editing these which is why I created my helper app. AFAIK there is no easier way of editing the XML . . . so if that’s too hard for you to use then don’t use IMEX data tasks

Thanks again for trying to help! I think I've got what I need now.

 

[isladogs]

Thanks again for trying to help! I think I've got what I need now.

For info, the code that @MarkK supplied does is one part of what my app is designed to do.