How to do, via vba, to create the MSYSImexSpecs and MSYSImexColumns tables?

[amorosik]

I have a vba routine that adds to the MSYSImexSpecs table some export specifications suitable for the tables I want to export
I see that a newly created .accdb file does not contain the MSYSImexSpecs and MSYSImexColumns tables
So I assume they are created on the fly when the operator manually saves the first export spec
So if I start my vba routine to programmatically add my export specs it returns a nice 3078 (table not present)

The question is: how to do, via vba, to allow Access to create the MSYSImexSpecs and MSYSImexColumns tables?

 

[Ranman256]

You shouldnt be making system tables via VBA.

 

[theDBguy]

Also, if I remember correctly, newer versions of Access don't use those tables anymore. Instead, a new XML table is used now (I can't remember its name).

Sent from phone...

 

[CJ_London]

They have to be created via the system although I think you can import system created tables from another db.

reason is some settings/ properties (don’t know which) are created at the same time. So far as I know there is no vba way of doing this. But I believe @Pat Hartman uses them so she may know but I just use vba to import/export

 

[ebs17]

vb@rchiv · Tipps & Tricks · Importspezifikation einer DB in VB erstellen

Textdateien in eine DB importieren nach Importspezifikation - und alles programmiert in VB!

www.vbarchiv.net

 

[isladogs]

The old IMEX specifications were largely replaced by IMEX data tasks using XML in Access 2010.
Note to @theDBguy - the XML info isn't stored in a table

However, the old method does still work for text files and, if you specify the settings, the 2 tables will be automatically created

If you really want to force creating the 2 IMEX system tables, you can do so using:

SysCmd(555)

You may find this article about the newer data tasks useful: View & Edit IMEX Data Tasks (isladogs.co.uk)

 

[amorosik]

If you really want to force creating the 2 IMEX system tables, you can do so using:

SysCmd(555)

Many thanks, this is solution for the problem
But, where have you find this command?

 

[isladogs]

You're welcome though the IMEX tables are now only of limited use. Suggest you learn how to use the newer XML Data Tasks approach.

As for the code, its one of several undocumented SysCmd values which I learned about many years ago.

 

[Pat Hartman]

though the IMEX tables are now only of limited use

Interesting. I find the "not so new" option to be of limited use. Why?
1. you cannot use the same spec for import and export
2. the source table/query name is fixed and you cannot change it at run time
3. the import/export file name is fixed and you cannot change it at run time.
4. you must rebuild the spec if you want to change any of the export details.

At a minimum, the vast majority of my exports require custom file names for export. Some of the imports do too so I use a file dialog to allow the user to select them.

 

[ebs17]

the IMEX tables are now only of limited use

They have the same functionality and utility as before the introduction of saved imports.

In contrast to saved imports, the specifications can only be used for text files. With the saved imports, you can also create and use new specifications for Excel.

 

[isladogs]

They have the same functionality and utility as before the introduction of saved imports.

In contrast to saved imports, the specifications can only be used for text files. With the saved imports, you can also create and use new specifications for Excel.

That was the point I was making. Sorry if my comment wan't clear.

Prior to the introduction of XML data tasks, the IMEX system tables were used for all imports/exports including Excel.
Existing IMEX specifications still work but new IMEX specs can only be created for text files

The XML data tasks do have limitations that @Pat Hartman mentioned but it is possible to overcome those. See my article / example app:

View & Edit IMEX Data Tasks

Use to view & modify saved import/export XML data tasks

www.isladogs.co.uk

 

[ebs17]

Prior to the introduction of XML data tasks, the IMEX system tables were used for all imports/exports including Excel.

Incorrect. Before the introduction there were no specifications that could be used with Excel. This was a perennial problem because there is no data type safety for columns in Excel. Keyword: TypeGuessRows/registry.

In a text file (plain text) you have at least saved text that can then only be formatted and interpreted.

 

[isladogs]

Clearly my memory was faulty!
Long time since I tried exporting from A2003 but I've just checked and you are indeed right
The IMEX specifications did only work for text files including .csv.

I think I may have been thinking about the various Excel & Access template files that I often used for importing & exporting data in the past.

Anyway, thanks for the correction

 

[Pat Hartman]

Incorrect. Before the introduction there were no specifications that could be used with Excel.

Almost true. You get a different wizard when you import than when you link. The import wizard gives you the same detail view and data type choices as you get with the text imports --- EXCEPT ---- that your choices are not saved as a spec so you cannot reuse them. When you link, you get no choice. You're at the mercy of the negotiations made between Access and Excel regarding what the column data types are. Seems like they use approximately the first 30 rows to make this decision.

Clearly, I'm missing something. Here are the two exports:

    DoCmd.TransferText acExportDelim, "Export - tblCust2", "tblCust2", "C:\Data\UsefulDatabases\Access_TeachingSamples\aaaaaaaaaa_Testing\aaExport.csv", False
    DoCmd.RunSavedImportExport "export - tblcust2"

The "new" version allows me to specify the name of an export but does not allow me to specify an alternate destination or file name at run time.

I would love to know how to do that.

 

[isladogs]

The "new" version allows me to specify the name of an export but does not allow me to specify an alternate destination or file name at run time.

I would love to know how to do that.

It is possible to alter the file name & path at runtime . . . but not using the wizard

This is a screenshot from the article I linked in post #11 and shows what changes can be made to a saved import/export data task.

Any changes made edit the XML created when saving the task.

So, for example, choose an export task and select modify external file path.
NOTE: It isn't necessary to know what the original path is.
Browse/enter a file path/name & click Execute Action => the XML is updated.
A copy of the XML can be viewed in a system table USysIMEXSpec created for the purpose

Now select Run Task & then Execute Action. The file will be exported to the new path

For the purposes of the demo, I've done each part as a separate action. However, both actions could be combined in a single button click

 

[Pat Hartman]

I think you are not understanding that I don't want to permanently modify the spec. I want to modify it on the fly based on data in each record.

Dim Source        As String
Dim Path         As String
Dim FileName    As String

Source = Me.txtSource
Path = dLookup("Path", "tblCompany", "CompanyCD = '" & Me.CompanyCD & "'")
If Right(Path,1) <> "\" Then
    Path = Path & "\"
End if
FileName = "BillingSummary_" & Me.CompanyCD & "_" & Format(Me.CloseDT,"yyyymmdd") & ".csv"
DoCmd.TransferText acExportDelim, "Export - tblCust2", Source, Path & FileName, False

Your utility is very clever but it is not at all obvious to the average Access user who doesn't come across this thread. The solution you implement, which may be the only solution, replaces the old version of the spec with the modified version. The ability to change the file name and directory at run time is a far more flexible and "discoverable" solution when using the "old" specs.

I see no benefit to using the "new" spec so I won't be changing any time soon. Thanks for pointing me to your utility.

 

[isladogs]

Yes I understood exactly what you meant. The utility provides what is probably the closest match to that requirement.
Although it does edit the XML behind the scenes, it can be modified at any time you need to do so without creating a new task.

For example, I find it very useful when apps containing import / export tasks are moved to a different computer where files have different locations.

 

[ebs17]

Apart from changing the XML specification, the path and name of the file to be imported are fixed.
However, nobody prevents you from changing the path and name of any file to the fixed setting, for example using the Name function.

The much bigger drawback of saved imports is that you have to import and you can't just link. With more complex processes you always have a temporary table = garbage data.

 

[isladogs]

Yes but to state the blindingly obvious, linking isn’t importing. Depending on what you might want to link, there are other processes for this

 

[Pat Hartman]

The much bigger drawback of saved imports is that you have to import and you can't just link.

Are you talking about the Old or the New spec because I only ever use the old model and I link "input" data but I export "report" data. If the input data is intended to be imported permanently, I use an append query to do some validation as I append the linked data to the permanent tables.