Out of string space (Error 14)

[MoonMullen]

I am trying to read in a record that is very large. Access "sees" the file as one record, while Excel "sees" the file as 57,834 records, or rows. I realize I am exceeding the limit that a string variable can hold. Is there anyway to use a variable greater than a variable-length string, which can hold up to approximately 2 billion characters? After holding this really long string, I will be searching for the linefeed character to break the string into a usable format. I

 

[ETID]

Why not let excel delimit this data for you?

open your text file from within excel, when the import wizard starts, select "delimited" then in next step, uncheck all delimiters except "other" then click in the declaration box for other and hold down alt while typing 010 you should see an example of how you data is split in the preview window.

If your data comes in in the 57,834 records as stated then repeat the above steps after selecting (highlite) the entire column that the data is in, then from menu DATA>TEXT TO COLUMNS to start the wizard.

Note: Alt+010 = line feed and Alt+013=carriage return
one or the other should work.


Then save as new name.xls or csv which ever you like and Access should be able to import it....good luck

 

[MoonMullen]

It sounds like a good idea, but I do not want to leave Access and open Excel to parse this file. I guess I could remotely open Excel from within Access, DDE or is it OLE, however that seems to be a complicated process. I

 

[llocklin]

Why not just open the file in VBA and bring it in yourself?

Luther

 

[MoonMullen]

Thanks llocklin, but from my first note I am trying to code in an Access module. The problem is that I need to hold one record that is greater than what a variable length string can hold. Are there any variables that are bigger than a variable length string? I

 

[THoey]

Have you looked at the TransferSpreadsheet method which will import an Excel spreadsheet???? "Alcohol is our friend, and it's about time we had more friends over."

Terry M. Hoey