• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

How do I indicate the field types when creating a new table while using an MS Access Make Table Query?

I am trying to create an Access table by using a make table query that contains an expression to build a concatenated data field.  The expression uses the Dconcat function created by Patrick Matthews which is very helpful for putting together the data into one field, but the problem is that the data being concatenated sometimes goes over the 255 characters limit that is created by default when the make table query runs.  I need to know how to indicate in the query to make the field be of "Memo" type instead of the "Text" type that gets created by default so that the data will fit in the field as needed.  Any assistance with solving this problem will be greatly appreciated.  Thanks! DConcat-SampleData-jb.accdb DConcat-SampleData-jb.accdb
Jaime Bonilla
Jaime Bonilla
1 Solution
There isn't any way to control the data type in a make-table query.

To get the final result you want you will have to build the table first with the memo field in place and try using an append query.

However that still might not work because many SQL operations involving memo fields result in them being truncated to 255 characters.  If that turns out to be the case, you may be faced with doing the update of the memo field in code.
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
To add to Peter57r's excellent answer, the table can be persistent.  Instead of using a Make Table, keep a table with the structure you want.   Each time to you need to use it, run a Delete query to clean out all the records, then reload them with an Append query.  This will keep your structure correct.

Jaime BonillaSystems AdmininistratorAuthor Commented:
I did change the query to an append query as suggested and it resolved my problem.  I greatly appreciate the advice.

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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now