Limiting Field Length on Linked Table 1

[mrimagepueblo]

I think I have tried all the obvious but I am still not getting the solution I need. I have a pipe delimited (|) text file that has 211 fields and approx 15,000 records that I need to link to and then ultimately export a number of files out as pipe delimited files based on a number of queries. I do this kind of thing all the time and run night time macros to produce the files. The only diffrence is that all the other files I have all have less than 150 fields, so the error has never happened. The problem is when I try and export the file I get an error which states "Field 143 contains a start position of 32806, the maximum start position allowed is 32767." I've managed to find out why and it's that Access 2003 (the version I'm using) automatically creates a field length of 255 characters for each field, hence 255 x 143 = 36465, which is past the maximum character length that Access will export to any given line. I can go into Tools -> Options and set the Default Field Length to say 25, but it does nothing to the actual field sizes. The weird thing is that I can export the query in a macro to excel in a tab seperated format and it sends the needed files out. The unacceptable solution to that is the data files now have in the first line FIELD 1 FIELD 2, etc. and the data is in tabbed format instead of pipe delimited. Ultimately it's not the end of the world because I can write a shell script to strip the first line and change the tabs to pipes, but it's just so many steps it's ridiculous.
I also tried to copy the table format and define the field lengths at 30 and tried an append or make table query, but when I try and copy/paste the table structure I get the same error, or if I try and define the table in the beginning with smaller field lengths, I get an error that says Too Many Fields. Go figure! I've got to believe that if their is a solution it's an easy one, I just don't know what it is? The only two options I have past finding and answer is to use the Excel solution I have or learn SQL and I just don't have the time to tackle that monster.
Any thougths on this would be mucho appreciated.

 

[LittleSmudge]

I'm not totally convinced of the validity of this suggestion - but I hope it might help .. ..

Have you looked at what Export Specification you are using to do this export ?

The approach I take to generating new Export Specification is to initiate a manual export and specify .txt/.csv/etc option.
Get part way through the process and then click on the ADVANCED button that is on the dialog box.
In the Advanced dialog box you can save all sorts of parameters along with the delimiting character and, I believe, the text length. ( If the width column is not visible it may be set to zero width so try expanding it as you would a xero width excel column ) Then save this Export Specification to some meaningful name.

Then in code when you do a DoCmd.Transfer.. .. you add this name in the ExportSpecification parameter.

Note: Import and Export specifications are stored withing the .mdb file they are created in.
As far as I know they cannot be transfered from one .mdb to another.



'ope-that'elps.



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

 

[mrimagepueblo]

I know what you are referring to and it's not there. I was able to expand that window, but all it gave me was a checkbox to skip fields.
I guess this one of those can't plug a square peg into a round hole situations.
Thanks for your effort

 

[mrimagepueblo]

You were almost right, well you were right, I'm just convinced you didn't state it properly. It's not in the Export Specification using the Advanced Tab, it's in the Advanced Tab of the Linking Process when linking the Table in the first place. When I link the table and choose the advanced tab there, I had to move over the hidden columns, and the Field length was there. It defaulted to the lowest # of characters in the field. I have to convert some codes to the longer description, but when I exported the file in my test, I used the Import specification I saved when importing and it worked perfectly. I wonder why it's importing at a smaller field length but then when it exports, it defaults to the 255 characters. I proved this out by copying and pasting the table as mentioned in my earlier post. I have a bunch of 4 to 6 character codes that need to be converted to about 20 characters at most. But I am convinced you gave me the correct solution.
Your a genius and you saved 3 other steps in the process, which I know will reduce the chances of error when this project gets automated.
Thank you very, very much.