how to create a new column in an existing table without uploading a new db file

[Nick Way]

hi there, i often find myself needing to add new columns to a table. currently i choose a quiet time of day, download the db from my live server, quickly open it up and add in my new column, then upload it back to the live server - whilst hoping that none of my visitors a) changed anything during this process and b) that no one exeperinced any errors

..not ideal!

is there a way to create new columns in tables (and even to create new tables perhaps) via CF so i dont have to do this downloading / uploading procedure to my mdb documnet (i'm using an access database by the way)

i'm thinking something like this??...

<cfquery datasource="blah">
CREATE COLUMN name="new_variable" type="yes/no"...?
</cfquery>

is there such a thing? thank you very much indeed for any assistance you can provide.

[Stressed_Simon]

Use google it is your friend!

http://www.w3schools.com/sql/sql_alter.asp

[Nick Way]

hi Simon - thanks for your reply.

naturally i did try google, i tried searching for "coldfusion add column access" and "how to add a column coldfusion" etc - i've also trawled the macromedia knowledge base

i have now tried again searching for "alter table" etc but unfortunatley i am still none the wiser

having read the page you suggested i have tried a few differnent ways of implementing their suggestion - but alas my computer says no..

Error Executing Database Query.
Syntax error in ALTER TABLE statement.

The error occurred in C:\CFusionMX7\wwwroot\nyalocal\admin\testcolumn.cfm: line 1

<cfquery datasource="nya" >
ALTER TABLE tbl_shoots-few ADD city text
</cfquery>

if you or anyone else could please help i'd be very grateful indeed. thank you

[Stressed_Simon]

What database are you using?

[Nick Way]

hi Simon

I am using Access

[Newsgroup_User]

> I am using Access
http://www.blueclaw-db.com/alter_table_ddl.htm

> naturally i did try google, i tried searching for "coldfusion add column
> access" and "how to add a column coldfusion" etc - i've also trawled the
> macromedia knowledge base

When looking up how to do things to your DB, it's probably an idea to omit
"ColdFusion" from the search string. It's irrelevant: CF passes all DB
instructions straight to the DB driver / engine, so it doesn't actually
play any part in the specific solution: it's just the messenger.

--
Adam

[Nick Way]

ok thanks Adam I appreciate the tip

so in order to implement this solution then do it wrap this up in cfquery tags then?

like this..

<cfquery datasource="blah">
ALTER TABLE Employees ADD COLUMN Emp_Email TEXT(25);
</cfquery>

also do you know what the correct syntax is to add a yed/no field for an access database by any chance please?

thanks very much for your help i really appreciate it - this is a subject that doesnt seem to be documented anywhere 😞

[Nick Way]

ah ha!! - it seems that on this occasion trial and error (as well as the trusty cf forum) was my friend..

in case anyone else finds themselves trying to do what i was doing i now have the following...

<cfquery datasource="blah" >
ALTER TABLE tbl_fred ADD varA BIT
</cfquery>

or (if you want to add a number column to your access db)..

<cfquery datasource="blah" >
ALTER TABLE tbl_fred ADD varB NUMBER
</cfquery>

thanks for your helps chaps