DAO Field Name and Size

Hi.  I have created a database in VB 6.0.  Now there may be a time when a person needs to change the field size.  The default size that I put is 25 but on occasion they may want to allow for a greater number of characters.  How can I resize a field of a table that already has data in it.  Is this possbile programatically?  Also, how would I rename a field.
alariacAsked:
Who is Participating?
 
TimCotteeConnect With a Mentor Head of Software ServicesCommented:
alariac, you can do it with a sql statement:

ALTER TABLE MyTable ALTER [MyFieldName] VarChar(50)"

Will change the size of the specified field to 50 characters and preserve the contents.
0
 
xSinbadCommented:
Try something like this assuming you created an access DB;

Set acObj = CreateObject("Access.Application")
Set acObj = GetObject("Path 2 database")
   with acObj
    currentdb.TableDefs(index).Fields(index).Size = 50
    currentdb.TableDefs(index).Fields(index).Name="NewName"
   End With



Cheers
Marcus
0
 
alariacAuthor Commented:
Run-TIme Error 3219
Invalid Operation

Didnt like that at all.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
alariacAuthor Commented:
Using
    Dim mySqlStr As String

    mySqlStr = "ALTER TABLE " & tableList.Text & " ALTER [" & colTXT.Text & "] VarChar(" & colWidth.Text & ")"
    mydb.Execute mySqlStr

I get a syntax error with the sql string.  

Drat this is getting frustrating.  The field has 1000's of records in it and copying the values of the field to a new field with the same name would take an exceedingly long time so I dont want to use that as an option.
0
 
Anthony PerkinsCommented:
One approach is the one Access uses and that is to:
1. Create a new field with the same attributes, but larger size.
2. Copy the data from all records to the new field
3. Delete the old field
4. Rename the field name to the original field name

Code in DAO is as follows:

Const DBName = "Your database path and nme goes here"
Const TableName = "Your table name goes here"
Const FldName = "Your text field goes here"
Const FldTempName = "TempName"
Const NewSize = 100
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tdf As DAO.TableDef
Dim fldOld As DAO.Field
Dim fldNew As DAO.Field

Set db = OpenDatabase(DBName)
Set tdf = db.TableDefs(TableName)
Set fldOld = tdf.Fields(FldName)
Set fldNew = tdf.CreateField(FldTempName, fldOld.Type, NewSize)
With fldNew
   .AllowZeroLength = fldOld.AllowZeroLength
   .DefaultValue = fldOld.DefaultValue
   .OrdinalPosition = fldOld.OrdinalPosition
   'Add any other attributes I may have missed
End With

tdf.Fields.Append fldNew
Set rs = tdf.OpenRecordset(dbOpenTable)
Do While Not rs.EOF
   rs.Edit
   rs.Fields(FldTempName).Value = rs.Fields(FldName).Value
   rs.MoveNext
Loop
rs.Close
tdf.Fields.Delete (FldName)
fldNew.Name = FldName

db.Close
0
 
alariacAuthor Commented:
Thanks! Put me in the right direction.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.