[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

DAO Field Name and Size

Posted on 2001-07-15
6
Medium Priority
?
343 Views
Last Modified: 2012-05-04
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.
0
Comment
Question by:alariac
6 Comments
 
LVL 6

Expert Comment

by:xSinbad
ID: 6284713
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
 

Author Comment

by:alariac
ID: 6285302
Run-TIme Error 3219
Invalid Operation

Didnt like that at all.
0
 
LVL 43

Accepted Solution

by:
TimCottee earned 150 total points
ID: 6285376
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:alariac
ID: 6285950
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6287101
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
 

Author Comment

by:alariac
ID: 6303197
Thanks! Put me in the right direction.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month17 days, 21 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question