mktbl query question

[Joe Blow]

In Access 2003, is there a way to force the field size on a text field in a
mktbl query?

I am linked to a SQL table, the field is 16 characters, but I need the text
field to be 17
for an append query later on down the road...

I tried:

ORDER_NO: Format([ORDER_NO_SQL],"@@@@@@@@@@@@@@@@@")
But it made the field 255 characters.

Any ideas?

-Joe

 

[John W. Vinson]

In Access 2003, is there a way to force the field size on a text field in a
mktbl query?

No. It will always be 255 characters.

Access Text fields are actually like SQL Varchar - trailing blanks aren't
stored, so the specified size is a maximum limit, not the amount actually
stored.

The Append query should work just fine; why do you see a need to specify the
fieldsize?


John W. Vinson [MVP]

 

[Joe Blow]

I am getting the data from a SQL 2000 Server . (linked tables)

My first mktbl query creates a table with a field that has an order # which
is 16 digits long.
The second query is an append query from a different table where the order #
is 17 digits long.
The append query is truncating the order # to 16 digits.

I was hoping to tell the mktbl query to force the field size to 17 even
though the data is only 16 digits long.
Then the append query would populate without getting the last character
truncated.

My alternatives so far have been:

A)
Run the second query first, which creates the table, order_no field length
is 17.
Then run the first query as an append, which appends with order #'s that are
16 digits.
(I didn't want to do this, because in a month or two, the second table is
going away, and I'll have to re-design the queries)

B)
Design the table ahead of time, setting the order_no field to 17 digits.
Run the first query as an append. (16 digits)
Run the second query as an append (17 digits)
Then run a delete query after processing the data.

I need the .mdb file size as small as possible for backup purposes, so I
delete all of the tables I've created at the end of the macro.

 

[John W. Vinson]

My first mktbl query creates a table with a field that has an order # which
is 16 digits long.

In what type of field? Text? If you view the table after the MakeTable runs,
what is the size of the field?

Would it be possible to create an empty table with the desired fieldsize, and
- instead of running a MakeTable from the linked SQL table - just run an
Append into this table?

The second query is an append query from a different table where the order #
is 17 digits long.
The append query is truncating the order # to 16 digits.

I don't understand why that would happen.

John W. Vinson [MVP]

 

[John Spencer]

Deleting all records from a table or deleting the table will recover no
space until you compact the database, so using a predefined table has almost
no affect on the amount of space the used.

If these are temporary tables then you might consider using a temporary
database.

See Tony Toews website
http://www.granite.ab.ca/access/temptables.htm
for an example

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..