Solved

DAO Field Name and Size

Posted on 2001-07-15
6
336 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 50 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

718 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