Microsoft Access has its good points and its bad points. One mostly nice point is that you can use a Visual Basic function to calculate a query result. For example:
Select Field1, Field2, MyVbaFunction(Field3)
But I wanted to do a “select into” and to have MyVbaFunction return a long string. Something like
SELECT col1, col2, MyVbaFunctionReturning1000ByteString(Field3) INTO newtable FROM table1
The trouble is, Access assumes field types. It assumes type of text(255) for any function which returns a string. So my 1000 byte result was getting truncated to 255 characters.
There really is no good work-around. You can’t tell a VBA function to return a memo. VBA doesn’t know about ‘memo.’ My hack was to do this in multiple steps.
First, run the query in a simplified form:
SELECT col1, col2, "dummy" as dummy INTO newtable FROM table1
Then edit the field definitions for table1 and change the 3rd column into a Memo column.
Finally, run this SQL:
update table1 set dummy = MyVbaFunctionReturning1000ByteString(Field3)
But… If you copy/paste this data from Access into Excel, it still gets truncated in Excel!