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.

 

[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

 

[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

 

[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

 

[ebs17]

I mean both variants.
The cases where the importing table and database table are identical in structure (same number of fields and identical field names) and are simply appended are rare in my practice.

A real import looks like that only some fields are taken into account during the import, whereby fields have different names. It will often be the case that only new records have to be imported - a double import can sometimes occur, but no one needs duplicates, neither does index errors.
Therefore, for me, a linked table is already a table that can be used in queries (update and append) and that can make a lot of modifications in the queries.
An import can also quickly appear in such a way that the data in the import table has to be divided into several tables in a database schema, including the generation of the necessary keys for the relationships. That means using queries again. All this also works with a linked table.

A previously imported table that does not fit into the existing database schema is only a bad intermediate step, because it is a temporary intermediate step and then waste data that has to be disposed of. I would only consider such a preliminary import to be useful if fields in this table are indexed specifically for performance - or out of necessity because a linked table does not work or cannot be created.
Linking a table during an import is therefore my first choice and quite important.