Changing the size of an existing field through code 
Author Message
 Changing the size of an existing field through code

I'm trying to change (make longer) the field size of a
numeric field in an existing table through code but just
can't fathom it.
I don't have direct physical access to the table in
question - it's part of a runtime program I created so I
want to write something that can be run remotely without
losing existing data in the field.

Maybe I'm just looking in the wrong places... can anyone
point me in the right direction?

Thank you! Julie.



Fri, 12 Dec 2003 21:07:14 GMT  
 Changing the size of an existing field through code
Get to the TableDef object in question first and then the specific Field int
that TableDef.  I sriously doubt that you will be able to change the field
size property at runtime tho.  It might give you problems if there is
already data in the table...give it a try.

Quote:
> I'm trying to change (make longer) the field size of a
> numeric field in an existing table through code but just
> can't fathom it.
> I don't have direct physical access to the table in
> question - it's part of a runtime program I created so I
> want to write something that can be run remotely without
> losing existing data in the field.

> Maybe I'm just looking in the wrong places... can anyone
> point me in the right direction?

> Thank you! Julie.



Fri, 12 Dec 2003 22:11:40 GMT  
 Changing the size of an existing field through code
On Mon, 25 Jun 2001 06:07:14 -0700, "Julie L"

Quote:

>I'm trying to change (make longer) the field size of a
>numeric field in an existing table through code but just
>can't fathom it.
>I don't have direct physical access to the table in
>question - it's part of a runtime program I created so I
>want to write something that can be run remotely without
>losing existing data in the field.

>Maybe I'm just looking in the wrong places... can anyone
>point me in the right direction?

To do it in VBA, you'll need a multistep process:

1) Create a new field of the desired type in your table - open the
Tabledef object and use the CreateField method
2) Either in a recordset or (better) in an update query, update the
new field to contain the data in the old field
3) Delete the old field and rename the new one.

           John W. Vinson[MVP]    
http://go.compuserve.com/msdevapps?loc=us&access=public

Quote:

>Thank you! Julie.



Sat, 13 Dec 2003 00:56:49 GMT  
 Changing the size of an existing field through code

Quote:

>I'm trying to change (make longer) the field size of a
>numeric field in an existing table through code but just
>can't fathom it.
>I don't have direct physical access to the table in
>question - it's part of a runtime program I created so I
>want to write something that can be run remotely without
>losing existing data in the field.

>Maybe I'm just looking in the wrong places... can anyone
>point me in the right direction?

>Thank you! Julie.

Julie

What you will need to do is to emulate what Access does behind the scenes when
you manually change a field's properties, which is to create a new field with
the required properties, insert the data from the old field into the new field,
delete the old field, and then rename the new field. Below is some simple SQL
code that does this. Note that some field properties cannot be accessed/set
through SQL, so you might need to use DAO instead.

Sub sChangeField(strTableName As String, strFieldName As String, strFieldType
As String)
    Dim db As Database
    Dim strSQL As String
    Set db = CurrentDb
'   Add a new field, called TempField, of the correct type in the table
    strSQL = "ALTER TABLE [" & strTableName & "] ADD COLUMN [TempField] " &
strFieldType & ";"
    db.Execute strSQL
'   Copy the data from the existing column to the new column
    strSQL = "UPDATE DISTINCTROW [" & strTableName & "] SET [" & strFieldName &
"]=[TempField];"
    db.Execute strSQL
'   Delete the existing field from the table
    strSQL = "ALTER TABLE [" & strTableName & "] DROP COLUMN [" & strFieldName
& "];"
    db.Execute strSQL
'   Change the new field name back to the existing field name
    db.TableDefs(strTableName).Fields("TempField").Name = strFieldName
    Set db = Nothing
End Sub

You can then call the sub like:

Call sChangeField("tblName","fldName","TEXT(100)")

Which will convert the field called fldName in the table tblName to a text
field with a maximum length of 100 characters.

Jon

Access tips & tricks - http://www.applecore99.com
Microsoft Access webring -
http://nav.webring.yahoo.com/hub?ring=microsoftaccess



Sat, 13 Dec 2003 01:42:35 GMT  
 Changing the size of an existing field through code
Thank you all - your help is much appreciated.
Quote:
>-----Original Message-----
>I'm trying to change (make longer) the field size of a
>numeric field in an existing table through code but just
>can't fathom it.
>I don't have direct physical access to the table in
>question - it's part of a runtime program I created so I
>want to write something that can be run remotely without
>losing existing data in the field.

>Maybe I'm just looking in the wrong places... can anyone
>point me in the right direction?

>Thank you! Julie.

>.



Sat, 13 Dec 2003 17:45:24 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Changing 'field size' in existing table

2. Change table field size in code

3. Changing text field size with code

4. Changing field size from code ?

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

6. VB code to change Access DB field size???

7. Change field's Size property via code

8. HOW TO change field size and delete field

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

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

11. Can VB5 change an existing (Access) Field Property?

12. Changing text field length in existing DB

 

 
Powered by phpBB® Forum Software