Create a Memo field using a Make-Table query

I am trying to use a make-table query to concatenate 8 text fields from an original table into a memo field in the new table.  Unfortunately, Access wants to create this new field as a Text type.  Even after going into Options > Tables/Queries > Default text field: memo, Access still wants to create the new field as Text.  There has been a similar question posted on Experts Exchance that was never technically resolved.  Anybody know how to do this effectively?
VEICAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Leigh PurvisConnect With a Mentor Database DeveloperCommented:
Personally, if I had such a precise need, I'd likely create the definition of the new table first and then just append the records into it.

Is the table from which you're selecting a local Jet one?
(i.e. you're familiar and in control of it?)
Then you're in a position to create the new table too?

If you wanted a quick cheat you could run a make table then an append - with a little DDL statement inbetween.
Roughly...

With CurrentDb
    .Execute "SELECT * FROM tblOld INTO tblNew WHERE 0"
    .Execute "ALTER TABLE tblNew ALTER COLUMN MemoFieldName MEMO"
    .Execute "INSERT INTO tblNew SELECT * FROM tblOld"
End With
0
 
VEICAuthor Commented:
Thank you kindly!
0
 
Leigh PurvisDatabase DeveloperCommented:
Ah - a favourite phrase of mine (was a fan of Due South).

You're most welcome. :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.