Redefine field data type

[Josh]

Hi,

I am trying to write a function in a module in MS Access 2000 that
will change the data type of a field called 'Start' in table
'bo_cpm_CS01ALL'. Here is the code that I have done so far but when I
run it nothing happens...no errors or changes to the table. The code
finds the table and field, creates a new field called 'temp' then
copys 'Start' to 'temp' then deletes 'Start' and renames 'temp'. The
field 'Start' has data type dbDouble.

Any help would be great!!

Public Function ChangeFieldType()

'Purpose: Changes a field's datatype

Dim db As DAO.Database
Dim tdef As DAO.TableDef 'Table to modify
Dim fldOld As DAO.Field 'Field to modify
Dim fldNew As DAO.Field 'Destination field
Dim Property As DAO.Property 'Field property
Dim strSQL As String 'SQL string to move the data

Set db = CurrentDb

'Get the table definition
Set tdef = db.TableDefs("bo_cpm_CS01ALL")

'Get the original field
Set fldOld = tdef.Fields("Start")

'Create the new field
Set fldNew = tdef.CreateField("temp", dbDate)

'Append the field
tdef.Fields.Append fldNew

'Copy the data
strSQL = "UPDATE bo_cpm_CS01ALL Set bo_cpm_CS01ALL.Start = temp"
db.Execute strSQL, dbFailOnError

'Delete the original field
tdef.Fields.Delete "Start"

'Rename the new field
fldNew.Name = "Start"

End Function

[Allen Browne]

Is your update query backwards:
strSQL = "UPDATE bo_cpm_CS01ALL Set temp = Start;"

In Access 2000 and later, you can change the field type on the fly:
strSql = "ALTER TABLE bo_cpm_CS01ALL ALTER COLUMN Start DATE;"
dbEngine(0)(0).Execute strSql, dbFailOnError
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Josh" <jo***********@tdsecurities.com> wrote in message
news:ac**************************@posting.google.c om...

I am trying to write a function in a module in MS Access 2000 that
will change the data type of a field called 'Start' in table
'bo_cpm_CS01ALL'. Here is the code that I have done so far but when I
run it nothing happens...no errors or changes to the table. The code
finds the table and field, creates a new field called 'temp' then
copys 'Start' to 'temp' then deletes 'Start' and renames 'temp'. The
field 'Start' has data type dbDouble.

Any help would be great!!

Public Function ChangeFieldType()

'Purpose: Changes a field's datatype

Dim db As DAO.Database
Dim tdef As DAO.TableDef 'Table to modify
Dim fldOld As DAO.Field 'Field to modify
Dim fldNew As DAO.Field 'Destination field
Dim Property As DAO.Property 'Field property
Dim strSQL As String 'SQL string to move the data

Set db = CurrentDb

'Get the table definition
Set tdef = db.TableDefs("bo_cpm_CS01ALL")

'Get the original field
Set fldOld = tdef.Fields("Start")

'Create the new field
Set fldNew = tdef.CreateField("temp", dbDate)

'Append the field
tdef.Fields.Append fldNew

'Copy the data
strSQL = "UPDATE bo_cpm_CS01ALL Set bo_cpm_CS01ALL.Start = temp"
db.Execute strSQL, dbFailOnError

'Delete the original field
tdef.Fields.Delete "Start"

'Rename the new field
fldNew.Name = "Start"

End Function

[Chris Mills]

Excellent, Allen!

Would that apply to upgrading a BE via an FE, assuming one had design perms to
the table?

However, the originally posted method also works, if done properly, and is
compatible with all versions.

My code, similar to that posted, appends a new field and everything is fine
(for me). Maybe they are trying to write to it before it is "updated/stored"?
I can't see any other reason.

Chris

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

In Access 2000 and later, you can change the field type on the fly:

[Chris Mills]

A "Doevents", or some such delay/cleanup, might assist between running code
and trying to "execute" something. Who can know what order things are executed
in, if you don't place some "Doevents" amongst it.

Merely a guess!
Chris

"Josh" <jo***********@tdsecurities.com> wrote in message
news:ac**************************@posting.google.c om...

Hi,

I am trying to write a function in a module in MS Access 2000 that
will change the data type of a field called 'Start' in table
'bo_cpm_CS01ALL'. Here is the code that I have done so far but when I
run it nothing happens...no errors or changes to the table. The code
finds the table and field, creates a new field called 'temp' then
copys 'Start' to 'temp' then deletes 'Start' and renames 'temp'. The
field 'Start' has data type dbDouble.

Any help would be great!!

Public Function ChangeFieldType()

'Purpose: Changes a field's datatype

Dim db As DAO.Database
Dim tdef As DAO.TableDef 'Table to modify
Dim fldOld As DAO.Field 'Field to modify
Dim fldNew As DAO.Field 'Destination field
Dim Property As DAO.Property 'Field property
Dim strSQL As String 'SQL string to move the data

Set db = CurrentDb

'Get the table definition
Set tdef = db.TableDefs("bo_cpm_CS01ALL")

'Get the original field
Set fldOld = tdef.Fields("Start")

'Create the new field
Set fldNew = tdef.CreateField("temp", dbDate)

'Append the field
tdef.Fields.Append fldNew

'Copy the data
strSQL = "UPDATE bo_cpm_CS01ALL Set bo_cpm_CS01ALL.Start = temp"
db.Execute strSQL, dbFailOnError

'Delete the original field
tdef.Fields.Delete "Start"

'Rename the new field
fldNew.Name = "Start"

End Function

[Brendan Reynolds]

If the code is making changes to the structure or content of the database, I
find that a DbEngine.Idle is sometimes required. For example, in this
situation I'd try a DbEngine.Idle between the Fields.Append and db.Execute
statements.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
"Chris Mills" <ph*********@cleardotnet.nz> wrote in message
news:%2****************@TK2MSFTNGP15.phx.gbl...

A "Doevents", or some such delay/cleanup, might assist between running code
and trying to "execute" something. Who can know what order things are
executed
in, if you don't place some "Doevents" amongst it.

Merely a guess!
Chris

"Josh" <jo***********@tdsecurities.com> wrote in message
news:ac**************************@posting.google.c om...

Hi,

I am trying to write a function in a module in MS Access 2000 that
will change the data type of a field called 'Start' in table
'bo_cpm_CS01ALL'. Here is the code that I have done so far but when I
run it nothing happens...no errors or changes to the table. The code
finds the table and field, creates a new field called 'temp' then
copys 'Start' to 'temp' then deletes 'Start' and renames 'temp'. The
field 'Start' has data type dbDouble.

Any help would be great!!

Public Function ChangeFieldType()

'Purpose: Changes a field's datatype

Dim db As DAO.Database
Dim tdef As DAO.TableDef 'Table to modify
Dim fldOld As DAO.Field 'Field to modify
Dim fldNew As DAO.Field 'Destination field
Dim Property As DAO.Property 'Field property
Dim strSQL As String 'SQL string to move the data

Set db = CurrentDb

'Get the table definition
Set tdef = db.TableDefs("bo_cpm_CS01ALL")

'Get the original field
Set fldOld = tdef.Fields("Start")

'Create the new field
Set fldNew = tdef.CreateField("temp", dbDate)

'Append the field
tdef.Fields.Append fldNew

'Copy the data
strSQL = "UPDATE bo_cpm_CS01ALL Set bo_cpm_CS01ALL.Start = temp"
db.Execute strSQL, dbFailOnError

'Delete the original field
tdef.Fields.Delete "Start"

'Rename the new field
fldNew.Name = "Start"

End Function