Modifying a table by a command button

[Guest]

i have a command button which runs a make table query, but i need to chage
the data type of one of the fields of the new table. (see below for fields)

do i use vb to change this or do i change the sql code? either way i am
stuck so need help with the code.

thanks in advance.

CSRid - Number
closed_code - text
cost_center - text (this is the field) i need to change thisto a number
field with no decimalplaces
sumoforder - currency
sumofvalue - currency
sumofinvioce - currency

 

[Allen Browne]

Use a DDL statement like this:

Dim strSql As String
strSql = "ALTER TABLE [Table1] ALTER COLUMN [cost_code] LONG;"
dbEngine(0)(0).Execute strSql, dbFailOnError


More info on DDL:
http://allenbrowne.com/func-DDL.html#ModifyFieldDDL

For the names to use for alternative field types, see:
http://allenbrowne.com/ser-49.html

 

[Guest]

Thanks Allen,

that didn't quite work as it came up with a data missmatch error.

however i changed the code arround to create the table first then append the
query results to it.

Use a DDL statement like this:

Dim strSql As String
strSql = "ALTER TABLE [Table1] ALTER COLUMN [cost_code] LONG;"
dbEngine(0)(0).Execute strSql, dbFailOnError


More info on DDL:
http://allenbrowne.com/func-DDL.html#ModifyFieldDDL

For the names to use for alternative field types, see:
http://allenbrowne.com/ser-49.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

i have a command button which runs a make table query, but i need to chage
the data type of one of the fields of the new table. (see below for
fields)

do i use vb to change this or do i change the sql code? either way i am
stuck so need help with the code.

thanks in advance.

CSRid - Number
closed_code - text
cost_center - text (this is the field) i need to change thisto a number
field with no decimalplaces
sumoforder - currency
sumofvalue - currency
sumofinvioce - currency