Updating a field using Access Code

[connieharper]

I know you can use CREATEFIELD to create a new field but I need to update the field type. I read where the only way to do it was to create a new field and append the old data to the new field, but the new field I create has to have the same field name as the old one otherwise I have to recode everything that has used the field name in the past.

When I try using CREATEFIELD to create a field with a name the same as another in the table, I get an error message that it can't be done. How can I get around this?

 

[Atomic Shrimp]

Not sure exactly what you're trying to do and why, but as far as general principles go, there are a couple of ways that might work for you:

1: you could create a new, empty table with the right field names and types, append all of the old data into it, delete or rename the old table, then rename the new table to the old one's name.

2: same sort of idea, but with a field; create a new field in the same table called 'temp', copy the data across, delete or rename the old field then rename temp.

is this something that you want to do as a one-off, or regularly?

HTH

Mike

[This message has been edited by Mike Gurman (edited 11-26-2000).]

 

[connieharper]

Sorry, I should give you some background information. I have an application I distribute using Developer and I have made some changes to one of the tables in the application. The application is distributed in two databases, one with all the tables and one with all the programs.

I am trying to keep from involving the client in having to send me their table database in order for me to change a field type in one of the tables. I have had this situation come up before and wonder how software developers deal with it. It is not practical to ask the client to send me back the database so I can make changes to it and obviously, they aren't interested in losing all their data.

There is a bug in the program that requires a field type to be changed from Number, Long Integer to Number,Double,Fixed,Auto.

I guess your suggestion of creating a new field with a different name, then appending all the data to it, then deleting the original field and then renaming the new field would work. Can you provide me with some sample code that would accomplish those steps? The table name is [Schedule] and the field name is [Hours]. Also, my tables are linked. Will this cause a problem?

 

[Atomic Shrimp]

Ah.. sample code.. er.. I've never built or modified table structures programmatically, so I'm afraid I can't help there (I had assumed you were going to do it once only, in which case it would be relatively painless 'by hand')

Sorry

Mike

 

[Keith P]

Public Sub Amend_My_Field()
Dim myDb As Database
Dim TableName As TableDef
Dim TargetDB as string
TargetDB = “C:\MyFolder\MyDatabase.mdb”
Set myDb = OpenDatabase(TargetDB)
Set TableName = myDb.TableDefs![Schedule]
With TableName
.Fields.Append .createField("Temp Hours", dbDouble)
.Fields![Temp Hours].Attributes = dbFixedField
.Fields.Refresh
End With
myDb.Close
End Sub

Public Sub Another_Sub_To_Copy_Old_Hours()

Your code using openrecordset etc. to update temp hour with ‘old’ hour values

End sub

Public Sub Delete_Old_Hour()
On Error Resume Next
Dim myDb As Database
Dim TableName As TableDef
Dim TargetDB as string
TargetDB = “C:\MyFolder\MyDatabase.mdb”
Set myDb = OpenDatabase(TargetDB)
Set TableName = myDb.TableDefs![Schedule]
With TableName
.Fields.Delete “Hours”
End With
myDb.Close
End Sub

Repeat a similar set of processes to recreate [Hours], copy data, delete [Temp Hours] .

Target the database that actually has the tables not the database in which they are linked.
I also posted a reply to a question by TomC on 15 Nov under General that may be of help to you.

I hope this helps.