Use VB to control Import Specification 1

[kpal29]

I have a db that uses a Import Specification to import a .txt file. Recently the .txt file's field separator and format has been changing from month to month. Is there a way to make a user-friendly interface for the user to edit and update the import spec?

 

[dhookom]

Yes, you can carefully modify values in the result of'
SELECT MSysIMEXSpecs.*
FROM MSysIMEXSpecs;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[LittleSmudge]

Great one Duane

As you say - "carefully"
You could really cause some damage wandering round in there with your boots on !

Any Idea where to look for a translation of the integer in the FileType column ?


Of course I can work it out for existing files types in the db - but I'm thinking about future requirements. I might want to look up a file type and receate the record in MSysIMESpecs myself.
( It'll be a lot easier than trailing through the wizard ! )






G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[dhookom]

There are two tables that manage the import/export process. These two table store the "attributes" set on the screen during the import process. You should be able to determine which field in the table corresponds with which attribute.

What I am getting at is I have no idea what you mean by "translation of the integer".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[LittleSmudge]

In table MSysIMEXSpecs there is a field called FileType

When the files that the spec relates to is a .CSV file the the value in FileType = 1252


So we have

FileType type of file
1252 .CSV
850 .txt


So I wondered what all the other possible FileType numbers equated to.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[dhookom]

I did a little searching around in the MDE and MDT files and couldn't find a table with these values.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MichaelRed]

this type of info is often available in (through?) the object browser the key there is to know (or find out through the libberal use of wading into it) the object which includes the info.




MichaelRed

 

[kpal29]

So I can use a Update Query string to run a query that will update the various values in this table and the values can be linked to fields on a form?

I would like to create form called frmSpecs. On this form would be unbound fields that correspond to each field in the MSysIMEXSpecs table.

When the user needs to change these values, they choose from the values in the fields (for example file type would be a dropdown limited to list) or enter string (for example a new column label ) and click a button that is attached to the VB.

The vb will contain lines to execute an update query based on the values in the forms fields.

Will this work?

 

[dhookom]

This could work if you have a thorough understanding of the fields and values.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kpal29]

Where can I learn about this table and it's fields and values? I have searched 'MSysIMEXSpecs' in google and not much results.

 

[dhookom]

These tables are officially undocumented so you might not find much information. If I needed to know more information about the values, I would just "play" with the specs using the supplied interface and see what happens.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Remou]

I was looking at this at one stage and have this info left over. It might save someone a few minutes.

DataType    Description    Width   Attribute
2           Byte              5          0
5           Currency          9          0
8           DateTime          9          0
7           Double            7          0
12          Hyperlink        10      32768
3           Integer           8          0
4           Long Integer     13          0
12          Memo          32000          0
11          OLE Object       11          0
6           Single            7          0
10          Text              5          0
1           YesNo             6          0[tt]
[/tt]

 

[MichaelRed]

While it is reasonable to extract and use info in hte "system" tables, I would urge "caution"* the attempt to modify it in any way.

* "System" tables are generally read only, as they should be. This is where the keys to the remainder of the database exist. Disruption of them is alway problematic, often causing loss of necessary information and all to often FATAL.




MichaelRed