Changing field size of a text in the table

[Benjamins via AccessMonster.com]

Hi,

Is it possible to change the field size through coding?

If yes, how do i go about doing it?

 

[Brendan Reynolds]

Hi,

Is it possible to change the field size through coding?

If yes, how do i go about doing it?

Public Sub ChangeSize()

Dim strSQL As String

strSQL = "ALTER TABLE tblTest ALTER TestText nvarchar(50)"
CurrentProject.Connection.Execute strSQL

End Sub

 

[Benjamins via AccessMonster.com]

Hi,

Thanks Brendan

if i wish to change the field form text to memo, what will the code be.

Is it : ALTER TABLE tblTest ALTER TestText memo

 

[John W. Vinson]

Hi,

Thanks Brendan

if i wish to change the field form text to memo, what will the code be.

Is it : ALTER TABLE tblTest ALTER TestText memo

If the table is at all large, you may run out of memory trying to do this. I
would really recommend creating a new, empty table (you can copy and paste
your current table, design mode only and change the field type there); then
run an Append query to populte it.

This should NOT be a routine operation; if you're needing to do this datatype
change repeatedly there is something amiss!

 

[Benjamins via AccessMonster.com]

Hi

Actually this is only done 1 time. The table is quite small and it is in mde
format.

Since the system is currently in use, and each team has it own database
located at different location thus i can't go around collecting the data,
convert back to mdb, change the field type, convert back to mde, put back the
data. We can't afford the down time for changing the data field as the system
is used 24/7.

I was creating a code that when you run the application, it will ask for the
location of the data file and it will alter the field type. This will only
affect user who are using this table which is quite seldom.

Hi,

[quoted text clipped - 3 lines]

Is it : ALTER TABLE tblTest ALTER TestText memo

If the table is at all large, you may run out of memory trying to do this. I
would really recommend creating a new, empty table (you can copy and paste
your current table, design mode only and change the field type there); then
run an Append query to populte it.

This should NOT be a routine operation; if you're needing to do this datatype
change repeatedly there is something amiss!

 

[Brendan Reynolds]

Hi,

Thanks Brendan

if i wish to change the field form text to memo, what will the code be.

Is it : ALTER TABLE tblTest ALTER TestText memo

Memo or ntext would work. BTW, you can't convert an MDE back to an MDB. But
there is no need to do so, anyway, as only design changes to application
objects (e.g. forms and reports) are disabled in an MDE, design changes to
tables and queries are not disabled.

 

[Benjamins via AccessMonster.com]

Hi

What i mean by convert is to import data from mde to mdb. All our data has
all the thing hidden. The only things that can been seen is an exit at the
menu bar thus nothing can be change.

Hi,

[quoted text clipped - 3 lines]

Is it : ALTER TABLE tblTest ALTER TestText memo

Memo or ntext would work. BTW, you can't convert an MDE back to an MDB. But
there is no need to do so, anyway, as only design changes to application
objects (e.g. forms and reports) are disabled in an MDE, design changes to
tables and queries are not disabled.