import text wizard error "record is too large"

[tranchemontaigne]

I'm working on another solution to this problem (using StatTransfer), but was curious if anyone might be able to explain why it was produced.

I was attempting to import data from a fixed width text file that occupies 1,569,735 KB of space. The schema I provided to MS Access had the file layout for more than 400 fields, but less than 200 fields were selected for the import.

Checking the Windows Registry, I verified that the HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Jet 4.0\MaxLocksPerFile registry key was set to 200 million.

 

[dawnd3]

I had a similar problem so maybe this will help in your search for an answer. I was having problems after getting text files into Access and found that somewhere in the code I had the wrong data type for one of the number fields. For instance, maybe I had it set as integer and some of the numbers in the field were higher than Access allows for the integer field size.

hope this helps.

Dawn

http://www.calliopeconsulting.net

 

[willir]

Check...

http://www.databasezone.com/techdocs/acclimit.html

Have you exceeded a limitation within Access?

255 fields - you seem to be aware of this on.
255 characters in a text field
1 GB table size
255 characters for description of table or field
2000 characters - max record size (does not inlcude OLE, hyperlinks and memo fields beyond the first 255 characters)

Richard

 

[tranchemontaigne]

Thanks for your help here.

Dawn, thought your thought was excellent, it was not relevant to my situation. The table I was attempting to import was composed entirely of text fields.

Richard, I think you are right in that I exceeded the limits allowable within MS Access.

When resolving this issue through the use of 3rd party software (StatTransfer), I still had to do some tweaking. Firstly I needed to collapse the data structure down to less than 255 total fields (including the fields I did not want to import), next I needed to break the collapsed fields of data I did not want into less than 255 character strings. Once these two steps were taken I was able to import the data I needed. StatTransfer gaved me the added feature of being able to query the records contained within the fixed width text file and import only the records I wanted. Has MS Access been successful importing the text file directly, I would have still had to run a delete query to remove all records from the data set that were not relevent to my work.

Thanks for your help here. It is always nice to learn why software does not act as expected.

PS: If either of you ever are looking for a tool that will convert your data to and from different analysis software formats, StatTransfer is awesome. We use it here to convert data from MS Access to SAS, SPSS, DBF, and STATA formats. The software also allows for you to convert data into Minitab, Matlab, Paradox, SyStat, S-Plus and many more mundane formats such as Lotus 1-2-3, MS Excel, and various kinds of text/html formats. Being able to use the right tool for analysis makes the job much easier. Using StatTransfer has helped me greatly in getting data to the point where the right tool could be used.