Changing multiple field sizes to same size

[MarkB]

I have imported ASCII data into an Access table. All of the fields show a
255 size. None of the data being imported is larger than 50 in field size.

For exporting purposes I need all fields to be 40 or less in size for each
field. Is there a way to gloablly replace the field size in the Table
Design?

Also, when changing field sizes indivieually, it seems rather kludgy to
click the field, then move down to field properties to cilck in field size
and the tab to save value then go back up and click on another field and
repeat... is there some other design view where you can go down the column
called field size and just change values?

 

[Tim Ferguson]

For exporting purposes I need all fields to be 40 or less in size for
each field. Is there a way to gloablly replace the field size in the
Table Design?

for exporting:

select recordid,
iif(len(field1)>40, left(field1), field1) as fieldone,
iif(len(field2)>40, left(field2), field2) as fieldtwo,
etc...


in vba:

for each fld in tdf.Fields
if fld.type = acFieldText then ' can't remember constant name!
fld.length = 40
end if
next fld

Also, when changing field sizes indivieually, it seems rather kludgy
to click the field, then move down to field properties to cilck in
field size and the tab to save value then go back up and click on
another field and repeat... is there some other design view where you
can go down the column called field size and just change values?

Nope. The table design UI is only meant to be used once at design time,
and so is not particularly optimised for repeat use.

An alternative method is to design the table (correctly) first; append
the imported records into it; and then empty it ready for the next
import. This means that you don't have to keep redoing the table
characteristics, and will incidentally reduce mdb bloat and risk of
corruption.

Hope that helps


Tim F

 

[Amy Blankenship]

You can use the F6 key to jump between the top pane and the bottom pane.
It's pretty fast once you get used to it.

HTH;

Amy

 

[MarkB]

Thanks. I'll try it.

Reason we don't design the database first is often we're just trying to
bring in some data to maniputlate in Access and then dump to another
program. The format coming in isnt' always the same. Was looking for a
faster way to deal with a database we didn't 'design'.

 

[MarkB]

Thanks for the tip!

You can use the F6 key to jump between the top pane and the bottom pane.
It's pretty fast once you get used to it.

HTH;

Amy

 

[Tim Ferguson]

Reason we don't design the database first is often we're just trying to
bring in some data to maniputlate in Access and then dump to another
program. The format coming in isnt' always the same. Was looking for a
faster way to deal with a database we didn't 'design'.

The other way to approach is to use SQL: although jet itself isn't strong
enough you can use ADO with T-SQL, particularly if the other program
happens to be SQL-server or another real database. I am by no means a T-
SQL expert, but something along the lines of

SELECT CONVERT(NVARCHAR(40),Field1) AS FieldOne,
etc

might help too.

At the same time, bear in mind that (a) Access stores records in
variable-length fields, so the "wasted" 215 (255-40) characters are not
taking up any space, and (b) the target database will presumably coerce
whatever is being imported into its own fields sizes anyway. Are you sure
this effort is actually necessary?

All the best


Tim F

 

[John Nurick]

Reason we don't design the database first is often we're just trying to
bring in some data to maniputlate in Access and then dump to another
program. The format coming in isnt' always the same. Was looking for a
faster way to deal with a database we didn't 'design'.

If this is a regular need I'd think in terms of writing code to parse
the input file and assemble and execute a SQL CREATE TABLE statement to
create a table ready for the data.