Maximum Start Position in Access error

[jkalpus]

A client recently tried importing Filemaker data into Access by using the Get External Data tool in Access 2003. When the tab delimited data, exported from Filemaker, was imported, the Import Text Wizard returned the following: The Field 'Field 29' contains a start position of '42977'. The maximum start position allowed is 32767.

What does this mean, exactly, and how can it be overcome or corrected? Is this a Filemaker export problem, or an Access import problem, or both? Thanks!

 

[Andrew Fergus]

Hi

How many characters are there for each record? It appears there are well over 40k. Either there are lots of fields of the 'memo' variety or Access has mis-interpreted the file and is trying to import the entire file into one record. Can you provide an error number or the exact error message wording?

Andrew

 

[jkalpus]

Thanks, Andrew... the exact error message is: The Field 'Field 29' contains a start position of '42977'. The maximum start position allowed is 32767. That's the whole thing, i.e., NO numerical error number. I don't know how many characters are in each field, yet. I was curious what the error message actually meant. What is a "start position"?

 

[Andrew Fergus]

I can't locate anything on this error on msdn, Google or any other Access forums - that's why I questioned the wording. There is an article here that is loosely related and may answer your question : http://support.microsoft.com/?scid=kb;en-us;872914&spid=2509&sid=50

I believe (not based on fact, merely a guess) the start position is a reference to the character number in a record. In other words, Access thinks one (most probably the first) record contains in excess of 32767 characters - in fact it tells you there are at least 42977 characters. As I alluded to earlier, that is a LOT of characters for one record (ie read as 'one row' in table). For example, in the following string :

abcdefghijk

the letter 'j' is character number 10 and if we were to separate each character into it's own field, then field number 10 (i.e. 'j') would have a starting position of 10. Extend that string out by another 42k characters and that is how long Access believes the record is. What sort of data is being imported? Hopefully that helps to 'decipher' the error. As for why it's doing it - as I mentioned earlier, either there is a huge amount of data for each record (can you confirm this?), or Access is trying to import all of the data into one record, or there is a 'Null' data issue per the link above.

To see if it is an import interpretation problem (eg maybe the file isn't tab delimited?), rather than importing the entire table, can you make a copy of the table in FileMaker, delete all of the data until it contains just 2 records and then try importing that? If you examine the data that is imported it may shed some light on what is going on.

Insofar as the 32767 is concerned, it may be some sort of limit within Access regarding the maximum number of characters it can import into any one record - although I don't see it listed here as a limit : http://office.microsoft.com/en-us/assistance/HP051868081033.aspx

This is going to be a process of elimination and I'm not sure how much more help I can be with this one.

HTH, Andrew