Changing a field's datatype in Access via VBScript?

I've got an Access DB that is a back-end to an HTA. I've changed
something in the script so that requires me to change a specific field
in the DB from a TEXT to a MEMO field.

I don't want to destroy the data that's already in the field so how I
can simply update its datatype using a script?

Volker
Tue Feb 21 07:19:57 CST 2006

Ginolard schrieb:
> I've got an Access DB that is a back-end to an HTA. I've changed
> something in the script so that requires me to change a specific field
> in the DB from a TEXT to a MEMO field.
>
> I don't want to destroy the data that's already in the field so how I
> can simply update its datatype using a script?
Don't see how you can do that properly. All ddl does an implicit commit,
so if you alter table modify (colname memo) and it doesn't preserve the
contents you have a window in which people see an empty column.

Lots of Greetings!
Volker

Ginolard
Tue Feb 21 07:23:56 CST 2006

Surely if I can change the datatype to MEMO via Access itself it should
be scriptable?

Volker
Tue Feb 21 07:39:18 CST 2006

Ginolard schrieb:
> Surely if I can change the datatype to MEMO via Access itself it should
> be scriptable?
Sure is. But not within one transaction and probably not without losing
the data temporarily. Maybe you can lock the table and so prevent others
from accessing it until you have changed it?

<create new table identical to the old one except for the name and the coltype>
<lock both tables exclusively>
<delete/disable any triggers and foreign key constraints on/pointing to oldtable>
select * from oldtable into newtable
delete from oldtable
alter table oldtable modify ...
select * from oldtable into newtable
<reenable/recreate any triggers and foreign key constraints>
<unlock table>
drop new_table

If your table has a primary key you'll probably get away with an
update set instead of the delete and the second insert.

Summary: If you try an alter table on a test system and it loses the
column data, you have to spend effort on it.

Lots of Greetings!
Volker

Ginolard
Tue Feb 21 08:20:46 CST 2006

I've found out how to do it now. No data loss too.

Set conn= CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open ".\Database\ManagePCDB.mdb"
conn.Execute("ALTER TABLE tbl_machines ALTER COLUMN CPU MEMO")
conn.Close

Volker
Tue Feb 21 08:23:33 CST 2006

Ginolard schrieb:
> I've found out how to do it now. No data loss too.
>
> Set conn= CreateObject("ADODB.Connection")
> conn.Provider="Microsoft.Jet.OLEDB.4.0"
> conn.Open ".\Database\ManagePCDB.mdb"
> conn.Execute("ALTER TABLE tbl_machines ALTER COLUMN CPU MEMO")
> conn.Close
Perfect!
Volker