Solved

DAO Field Name and Size

Posted on 2001-07-15
6
335 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
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.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
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…

733 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