Solved

DAO Field Name and Size

Posted on 2001-07-15
6
330 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 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DO Loop not working 4 72
Controlling which port to download from 4 72
ms access #TYPE! error on report when no data 4 68
passing parameter in sql procedure 9 57
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

863 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now