VB code to change Access DB field size??? 
Author Message
 VB code to change Access DB field size???

Nope I don't think it can be done as a resize.

Look at Help.Field Object, Fields Collection Summary
It says that fieldsize is only controlable in recordsets.

You might think about cloning your current database tabledefs to a new
database
change the field sizing with Access, then populate the new database and swap
dbs
That's all I can think of immediatelly.

Quote:

>I need to change the field size property in an existing  Access database
table
>(it contains data).  I've found and successfully used code to append a new
>field to a table, but I can't seem to find a way to edit the field size
>property of an
>existing field.  Surely this can be done!  Can anyone tell how??

>My thanks,
>Rick



Mon, 19 Aug 2002 03:00:00 GMT  
 VB code to change Access DB field size???
I need to change the field size property in an existing  Access database table
(it contains data).  I've found and successfully used code to append a new
field to a table, but I can't seem to find a way to edit the field size
property of an
existing field.  Surely this can be done!  Can anyone tell how??

My thanks,
Rick



Tue, 20 Aug 2002 03:00:00 GMT  
 VB code to change Access DB field size???
I think appending the data to a new field is the best method.

On Thu, 2 Mar 2000 23:19:52 -0500, "Colonna Vallozzi"

Quote:

>Nope I don't think it can be done as a resize.

>Look at Help.Field Object, Fields Collection Summary
>It says that fieldsize is only controlable in recordsets.

>You might think about cloning your current database tabledefs to a new
>database
>change the field sizing with Access, then populate the new database and swap
>dbs
>That's all I can think of immediatelly.


>>I need to change the field size property in an existing  Access database
>table
>>(it contains data).  I've found and successfully used code to append a new
>>field to a table, but I can't seem to find a way to edit the field size
>>property of an
>>existing field.  Surely this can be done!  Can anyone tell how??

>>My thanks,
>>Rick



Tue, 20 Aug 2002 03:00:00 GMT  
 VB code to change Access DB field size???
Kevin and Calonna,

Thanks.  But I still suspect it can be done.  For example, you can do it
through Access even if the table contains data.  And Access is just a layer on
top of the Jet DB.  

Rick



Tue, 20 Aug 2002 03:00:00 GMT  
 VB code to change Access DB field size???

Quote:

> Thanks.  But I still suspect it can be done.  For example, you can do it
> through Access even if the table contains data.  And Access is just a layer on
> top of the Jet DB.

I've been there. Jet doesn't expose this functionality. This is what I do in DAO:

0. Assume a field trans_type TEXT (20) whose size needs to be 30.

1. Add a field trans_type_temp TEXT(30)

2. UPDATE <Table> SET trans_type_temp = trans_type

3. Delete the trans_type field.

4. Add a field trans_type TEXT(30)

5. UPDATE <Table> SET trans_type = trans_type_temp

6. Delete the trans_type_temp field.

--
MikeC

Please reply to the group.

  MikeC.vcf
< 1K Download


Tue, 20 Aug 2002 03:00:00 GMT  
 VB code to change Access DB field size???
MikeC,

Your solution was one I was contemplating .  I'm stubborn at times, and believe
it OUGHT to be doable more directly (I doubt Access uses the dummy field,
create and delete method).  A fellow in the VB database group just suggested it
might be done in Jet/SQL using an ALTER TABLE command.  If I find that works
I'll try to remember to let you know about it.

My thanks, Rick

Quote:
>I've been there. Jet doesn't expose this functionality. This is what I do in
>DAO:

>0. Assume a field trans_type TEXT (20) whose size needs to be 30.

>1. Add a field trans_type_temp TEXT(30)

>2. UPDATE <Table> SET trans_type_temp = trans_type

>3. Delete the trans_type field.

>4. Add a field trans_type TEXT(30)

>5. UPDATE <Table> SET trans_type = trans_type_temp

>6. Delete the trans_type_temp field.

>--
>MikeC



Wed, 21 Aug 2002 03:00:00 GMT  
 VB code to change Access DB field size???
Sorry Rick, you cannot change the field size. What you have to do, is
create a new table and move the data.

Even though the MS Access program let's you believe that it can be done,
notice how long it takes .... because it is actually making a new table.

I had posted this very same question about year and a half ago, and I
received a reply from non other than Debra Kurata, with this answer.

Good luck.

Daja



Quote:
> I need to change the field size property in an existing  Access
database table
> (it contains data).  I've found and successfully used code to append a
new
> field to a table, but I can't seem to find a way to edit the field
size
> property of an
> existing field.  Surely this can be done!  Can anyone tell how??

> My thanks,
> Rick

Sent via Deja.com http://www.deja.com/
Before you buy.


Wed, 21 Aug 2002 03:00:00 GMT  
 VB code to change Access DB field size???

Quote:

> Your solution was one I was contemplating .  
> A fellow in the VB database group just suggested it might be done in Jet/SQL using
> an ALTER TABLE command.  

Don't think it's possible. See what my MSDN help says:

ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL] [CONSTRAINT index] |
CONSTRAINT multifieldindex} |
DROP {COLUMN field I CONSTRAINT indexname} }

Using the ALTER TABLE statement, you can alter an existing table in several ways. You can:

Use ADD COLUMN to add a new field to the table.

Use ADD CONSTRAINT to add a multiple-field index.

Use DROP COLUMN to delete a field.

Use DROP CONSTRAINT to delete a multiple-field index.

--
MikeC

Please reply to the group.

  MikeC.vcf
< 1K Download


Wed, 21 Aug 2002 03:00:00 GMT  
 VB code to change Access DB field size???

Quote:
>Even though the MS Access program let's you believe that it can be done,
>notice how long it takes .... because it is actually making a new table.

>I had posted this very same question about year and a half ago, and I
>received a reply from non other than Debra Kurata, with this answer.

Daja,

With that information, I am extremely close to being a believer.  My thanks!
I'll do it the hard way.

Rick



Thu, 22 Aug 2002 03:00:00 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. Jet/Access: change field size in VB code???

2. how to change field size and delete field MS ACCESS/VB

3. VB 3.0 Changing the size of a text field in an Access database

4. Change Access 97 Field sizes using VB 6

5. Converting Access 2.0 DB to Access 97 DB in VB code

6. Changing db field sizes.

7. VB6 code to edit field size in DB?

8. Change table field size in code

9. Changing the size of an existing field through code

10. Changing text field size with code

11. Changing field size from code ?

12. adjust Access DB Field Size....

 

 
Powered by phpBB® Forum Software