can I change text field size in output table of make table query?

[Guest]

Hi all
I am trying to download daily orders from web site (sql) using a make table
query in access and then transferthe
file to an AS400. I don't have admin priv on the Sql server database so
have been linking to data via Access.

The table created by the Access query has all the dataI need, but the text
field sizes are too big. ( field First name is 100 characters!) Can I code
the make table query to produce the table with smaller field sizes for later
upload to the AS400? Or is there some other better way to do this.

Thanks in advance for any help.

 

[Jack MacDonald]

In the query, you can enter an expression rather than a field name.
So, for example, where you presently have FName as one of the fields
in your query, you can replace it with ShortFirstName: left(FName,20)
to return only 20 characters of the name.

Hi all
I am trying to download daily orders from web site (sql) using a make table
query in access and then transferthe
file to an AS400. I don't have admin priv on the Sql server database so
have been linking to data via Access.

The table created by the Access query has all the dataI need, but the text
field sizes are too big. ( field First name is 100 characters!) Can I code
the make table query to produce the table with smaller field sizes for later
upload to the AS400? Or is there some other better way to do this.

Thanks in advance for any help.

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[John Vinson]

The table created by the Access query has all the dataI need, but the text
field sizes are too big. ( field First name is 100 characters!) Can I code
the make table query to produce the table with smaller field sizes for later
upload to the AS400? Or is there some other better way to do this.

Access doesn't store trailing blanks, so the name "Samm" takes up four
characters on disk, whether the field length is 10 or 255.

It's probably NOT necessary to use a MakeTable query at all, unless
you will be manually editing the data downloaded from SQL prior to
exporting it. Even if you are doing so, you may be better off having a
stored (empty) table with the fieldsizes you want, linking to SQL, and
running an Append query rather than a make-table. When you're done
with that data, run a Delete query to empty it.

You can also base your Export on a Query selecting only part of the
field. Even if the name field is 100 bytes, you can use an expression
like

Left([FirstName], 20)

as a calculated field in a query, and export that. It's not necessary
to restructure the table to do so!

John W. Vinson[MVP]