Defining Text field sizes in destination Make-Table Query

  • Thread starter Thread starter JASelep
  • Start date Start date
J

JASelep

when extracting fields from ODBC DB2 tables into access using Make-Table Query

howdoes one define/limit the destination text field size?

Access seems to default to 255 or 50 characters which is problematic on
large numbers of records when the field only needs to be 3 to 10 characters

Keeping blowing 2GB limit because of all the dead space.
I'm unsure of how to trim / exclude / limit during the Maketable query process
 
when extracting fields from ODBC DB2 tables into access using Make-Table Query

howdoes one define/limit the destination text field size?

Access seems to default to 255 or 50 characters which is problematic on
large numbers of records when the field only needs to be 3 to 10 characters

Keeping blowing 2GB limit because of all the dead space.
I'm unsure of how to trim / exclude / limit during the Maketable query process
Click to expand...

You can't.

However, Access does not store trailing blanks. A 255-byte field containing
"XYZ" takes up 3 bytes (if you have Unicode compression on, which is the
default).

You can get a lot more control over your table structure if you avoid
MakeTable queries entirely, and use Append queries into a pre-built table
instead. Are you routinely importing the same kind of data, or is it a
different table every time? Have you been Compacting the database routinely?
That's essential if you are adding and deleting data (whether by deleting
tables or just deleting the data in the tables).
 
Yes I've been Compacting

Client wants it in processes' they understand (they hadn't seen a Union
Query before I started)

they WANT Make-Table at each step of process (i'd have gone with update query)

Even going in to table design after it's made and changing the text-field
sizes then compacting would be pushing it for this department in a repeatable
"idiot-proof" clerical process they desire
 
Where would I find the Unicode compression switch? - Is it per access DB
defined or Access install defined or defined @ Enterprise level?

My non-extensive anecdotal experience would leave me to believe Unicode
compression is not currently employed
 
Back
Top