How to change field's type and field's size of existing table's field? 3

[Aivars]

I would like to change some MS Access table field's properties in VBA codes, but when I try to change properties value "Type" or "Size", VBA calls error Number 3219 - "Invalid operation". I understand that these properties in VBA are read only, but maybe exist any solution of this. It's very important for me that it could be possibly to solve this problem.

Code example. (I want to change structure of old table like other new one)
****************************
Sub ChangePropertyValue(fldNew as Field)
Dim tdf as TableDef
Dim fld as Field 'Field of Old Table
Dim prp as Property

Set tdf=currentDB.TableDefs("OldTable&quot
Set fld = tdf.Fields(fldNew.Name)

'Change field position
Set prpNew = fldNew.Properties("OrdinalPosition&quot
Set prp = fld.Properties("OrdinalPosition&quot
prp.Value = prpNew.Value 'Here program works normally

'Change field type
Set prpNew = fldNew.Properties("Type&quot
Set prp = fld.Properties("Type&quot
prp.Value = prpNew.Value '>>>>>>>>>> Here VBA call error No 3219 - "Invalid operation"

'Change field size
Set prpNew = fldNew.Properties("Size&quot
Set prp = fld.Properties("Size&quot
prp.Value = prpNew.Value '>>>>>>>>>> Here VBA call error No 3219 - "Invalid operation"
End sub

 

[CPUburn]

hi there,

an easy and decent solution to this one is:

1 create a new Field with the correct properties
tabledefs("x&quot.fields.append("blabla_new",dbtext,50)

2 update that new Field with the value of the old one
update x set blabla_new = blabla_old

3 delete the old field
tabledefs("x&quot.fields.delete "blabla_old"

4 rename the new Field to the old name
tabledefs("x&quot.fields("blabla_new&quot.name = "blabla_old"

the kid

 

[MichaelRed]

easier yet. Do it directly in design mode for the table.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[CPUburn]

yes Michael, that's a solution if one has access to the db, when distributing a software solution for 5000 clients like we do, it might become a hell of a job for us to visit them all and change the table-layout ;-)

the kid

 

[Aivars]

You right Kid, until we don't talk about fields which have index "Primary key" and relations to other tables. But otherwise?

Aivars

 

[Aivars]

I may upon explain my question. I have many same applications (~70) which be used in different cities. From time to time it's needed to make some changes in the application (int.al. in the tables' structure - database contain approximately 30 related tables). I made utility for upgrade of application. This utility copy all data from old database into new database and then replace its. This process cost much time and sometimes in the work of this utility proceed errors (in great databases). Thereof I find other way for solve this problem. Maybe somebody can recommend me any solution?

Aivars

 

[CPUburn]

Oups,

maybe in that case you'd better consider downgrading your "upgrade wizard" into making a new table and filling it in with the right data; as for the relation(s) you can create the new table with all relations that are needed, then fill in the table, based on a query containing the "old" tables in order not to mess up the prime keys.. then delete the old tabledef and rename the new one...

the kid

 

[nickperez22]

I was wondering if you found a satisfactory response to your question, I have a similar situation and the CPUBurn's seems like the most logical alternative, however I'm having trouble turning his pseudocode into vb code.

1. I know how to creat a new field
2. I am unsure of how to update the new field with the old field, but assume it could be done with record set or query.
3. I tried deleting my old field, but got the same error "3219".
4. I didn't get to his step 4.

The following code just creates a new field "bottlerID2" and then tries to deletes the old field "bottlerID". There is no problem with the creation of the new field but I get the error message on the section of code deleting the old field. Any help would be appreciated.

Sub Makeatable()
Dim db As Database
Dim tbl As TableDef
Dim fld As Field

'start by opening the database
Set db = CurrentDb()

'Open the tabledefenition
Set tbl = db.TableDefs("Bottlers&quot

'Create a new field
Set fld = tbl.CreateField("bottlerID2", dbLong)

'Append the field
tbl.Fields.Append fld

'Delete the old field
Set fld = tbl.Fields("bottlerID&quot
tbl.Fields.Delete fld

End Sub

 

[andymak99]

I also have the same question as nick and would like a reply. Thanks in advance

mak

 

[llocklin]

Your code is passing a field object as the argument to the delete method. The method is documented as taking a string containing the name of the field as its argument.

Using your example:
change: tbl.Fields.Delete fld
to: tbl.Fields.Delete fld.name

Luther

 

[martaccess]

Hi

Can someone put the entire final code ?

how we call that sub in access ?

and we can call it form a macro, i thinks its by a function but i don't know how..

Thanks !!

 

[llocklin]

I copied this code directly from the MS Knowledgebase search results for "change field data type": ACC: How to Change a Field's Data Type at Run Time with Code (Q128016)


'*****************************************************************
' The AlterFieldType Sub procedure requires three string
' parameters. The first string specifies the name of the table
' containing the field to be changed. The second string specifies
' the name of the field to be changed. The third string specifies
' the new data type for the field.
'*****************************************************************

Sub AlterFieldType (TblName As String, FieldName As String, _
NewDataType As String)
Dim db As Database
Dim qdf As QueryDef
Set db = CurrentDb()

' Create a dummy QueryDef object.
Set qdf = db.CreateQueryDef("", "Select * from PROD1&quot

' Add a temporary field to the table.
qdf.SQL = "ALTER TABLE [" & TblName & "] ADD COLUMN_
AlterTempField " & NewDataType
qdf.Execute

' Copy the data from old field into the new field.
qdf.SQL = "UPDATE DISTINCTROW [" & TblName & "] SET_
AlterTempField = [" & FieldName & "]"
qdf.Execute

' Delete the old field.
qdf.SQL = "ALTER TABLE [" & TblName & "] DROP COLUMN ["_
& FieldName & "]"
qdf.Execute

' Rename the temporary field to the old field's name.
db.tabledefs("[" & TblName & "]&quot.Fields("AlterTempField&quot._
Name = FieldName
' Clean up.
End Sub

Your really should read the entire article at it contains other relevant information.

Luther