Modifying MSysIMEXColumns

[dandot]

Hi All,

I have a problem.

A couple of months ago I took over the support of an Access db.

I import 4 text files daily. They are imported using the DoCmd.TransferText acImportDelim command. Each text file has a header line which defines the column names seperated by ~.

The problem is that one of these text files will soon be changed to include a new fields in the column heading. Column headings are being mapped to the destination table via the MSysImexSpecs and MSysImexColumns tables.

How would I update the MSysImexColumns to reflect these new headings? What values do I put in the DataType Column and what values do I put in the Start and Width Columns?

Has anyone ever had this problem?

I am limited to working with this solution as I am not the original developer of this application.

 

[KenReay]

Hi

If you look at the DoCmd.TransferText coommand syntax you will see that one of the paramters is the import specification. You can edit this without doing anything dangerous like amending the system table directly.

it is the second parameter:

Specification Name The specification name for the set of options that determines how a text file is imported, exported, or linked. For a fixed-width text file, you must either specify an argument or use a schema.ini file, which must be stored in the same folder as the imported, linked, or exported text file.

It is not very obvious how to get into this option.

Choose File \ Get External Data \ Import

Choose fule type text, select a text file, click inport

The import text file wizard will start, choose Advanced, then Specs. This will show you a list of existing specifications, choose the one you want and change it, or probably safer, create a new one in the format required and amend the name of the specification file in the docmd.

Told you it was not very obviuos, an Access afterthought I suspect.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK