Changing 'field size' in existing table 
Author Message
 Changing 'field size' in existing table

I have an access application which I am using to create reports from tables
in another database collection. All of the tables in the external database
are 'fixed width' text tables. The tables are automatically imported into
Access and then manipulated through a series of queries to achieve the
desired reports.

The problem I have run up against is the field width of the imported tables.
Initially the field width is set by the the import specification for each
imported table. There does not appear to be a way to alter the field width
programmatically since it appears that the 'make table' queries inherit the
field widths from the source tables. I have tried to alter the field widths
using DAO or VBA procedures, but it appears that this is not possible. I
need to change the field width as there are data which is appended to some
tables in a later process which requires a larger field size.

My less elegant fix was to alter the field width manually, then use a macro
to clear the table and append the newly imported data.

Thanks,
Dave Loiselle



Fri, 08 Feb 2002 03:00:00 GMT  
 Changing 'field size' in existing table

Quote:

> I have an access application which I am using to create reports from tables
> in another database collection. All of the tables in the external database
> are 'fixed width' text tables. The tables are automatically imported into
> Access and then manipulated through a series of queries to achieve the
> desired reports.

> The problem I have run up against is the field width of the imported tables.
> Initially the field width is set by the the import specification for each
> imported table. There does not appear to be a way to alter the field width
> programmatically since it appears that the 'make table' queries inherit the
> field widths from the source tables. I have tried to alter the field widths
> using DAO or VBA procedures, but it appears that this is not possible. I
> need to change the field width as there are data which is appended to some
> tables in a later process which requires a larger field size.

> My less elegant fix was to alter the field width manually, then use a macro
> to clear the table and append the newly imported data.

Rather than use Make-Table queries, would it be possible to use Delete and
Append queries and re-use the same table again and again?  You then would be
able to keep a larger field size and not have to worry about changing it....

--
HTH
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
|  gHaD (Aaron Robinson)  | The Never-Ending Commute  |
|  MJM-PC Solutions, Inc. | Web & Database Developer  |
|       Chicago, IL       |   Will work for Dew?!     |
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-



Sat, 09 Feb 2002 03:00:00 GMT  
 Changing 'field size' in existing table

Quote:
>I have an access application which I am using to create reports from tables
>in another database collection. All of the tables in the external database
>are 'fixed width' text tables. The tables are automatically imported into
>Access and then manipulated through a series of queries to achieve the
>desired reports.

>The problem I have run up against is the field width of the imported tables.
>Initially the field width is set by the the import specification for each
>imported table. There does not appear to be a way to alter the field width
>programmatically since it appears that the 'make table' queries inherit the
>field widths from the source tables. I have tried to alter the field widths
>using DAO or VBA procedures, but it appears that this is not possible. I
>need to change the field width as there are data which is appended to some
>tables in a later process which requires a larger field size.

Why not start by designing the tables you need then appending the
incoming data to them? You should be able to do this directly or by
running the import you have to a temporary table and appending from that
to the "proper" table. Then just delete and repeat as needed.

Quote:
>My less elegant fix was to alter the field width manually, then use a macro
>to clear the table and append the newly imported data.

--
Albert Marshall


Sat, 09 Feb 2002 03:00:00 GMT  
 Changing 'field size' in existing table
As you can see from the end of my message, your suggestion is the only work
around I have found for this. The only reason I would like to do it
programmatically is that from time to time there are new tables to be
imported and it would be more elegant if this part were automated.

Quote:


>Rather than use Make-Table queries, would it be possible to use Delete and
>Append queries and re-use the same table again and again?  You then would
be
>able to keep a larger field size and not have to worry about changing
it....

>--
>HTH
>-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
>|  gHaD (Aaron Robinson)  | The Never-Ending Commute  |
>|  MJM-PC Solutions, Inc. | Web & Database Developer  |
>|       Chicago, IL       |   Will work for Dew?!     |
>-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-



Sat, 09 Feb 2002 03:00:00 GMT  
 Changing 'field size' in existing table
If you really want to change the field size, take a look at my answer
to the recent message in this group called, Schema Updates in DAO. By
the way, this is much easier with Jet 4, which supports ALTER TABLE
ALTER COLUMN for changing field sizes in one step.

 -- Andy

Quote:

>As you can see from the end of my message, your suggestion is the only work
>around I have found for this. The only reason I would like to do it
>programmatically is that from time to time there are new tables to be
>imported and it would be more elegant if this part were automated.



>>Rather than use Make-Table queries, would it be possible to use Delete and
>>Append queries and re-use the same table again and again?  You then would
>be
>>able to keep a larger field size and not have to worry about changing
>it....

>>--
>>HTH
>>-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
>>|  gHaD (Aaron Robinson)  | The Never-Ending Commute  |
>>|  MJM-PC Solutions, Inc. | Web & Database Developer  |
>>|       Chicago, IL       |   Will work for Dew?!     |
>>-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-



Sat, 09 Feb 2002 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Can't change text field size using DAO; can't specify size using ALTER TABLE SQL

2. Change field's Size property via code

3. How to change a field's size?

4. Help Changing Table's Field Type and Format using DOA

5. changing field prop's or copying data to another table

6. How to modified existing field and add new field in existing table for Access database

7. Changing the size of an existing field through code

8. Table's Field doesn't bind to HTML Control Element

9. Table's Field doesn't bind to HTML Control Element

10. Table's Field doesn't bind to HTML Control Element

11. Create new field in existing table exactly like field in second table

12. Change 'from' field

 

 
Powered by phpBB® Forum Software