EasyCFM.COM ColdFusion Forums / MS Access Related Issues / Altering columns with Coldfusion

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Altering columns with Coldfusion

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

rickaclark
05-01-2010 @ 10:11 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 82
Joined: Dec 2005

I know I can alter columns in an access database with ColdFusion. Can I rename a column or do I have to create a new column, transfer the data, then delete the old column?

If the later is my only option, how would I do this?

Thanks for any help or direction.

rickaclark
05-01-2010 @ 9:17 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 82
Joined: Dec 2005

So far this is what I have been able to try:

<cfquery name="qQueryChangeColumnName" datasource="#dsn#">
  ALTER TABLE leaderboard
  ADD COLUMN points3 number
  UPDATE leaderboard SET  points2 = points3
  ALTER TABLE leaderboard DROP COLUMN points2
</cfquery>

I can add the points3 column. I am having trouble with the update part. Any ideas why?

rickaclark
05-02-2010 @ 6:11 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 82
Joined: Dec 2005

Here’s the skinny:

I have two columns, one that stores current month’s data and one that stores last month’s data. Every month data from column 2 (this month’s data) needs to be moved to column 1 that holds last month’s data. I then null out column 2 so I can accumulates this month’s data.

I understand how to drop a column or add a column, how do I transfer data from one column to another in my access table?

Here is my trial code:

<cfquery name="qQueryChangeColumnName" datasource="#dsn#">
  ALTER TABLE leaderboard
  UPDATE leaderboard SET  points2 = points3
</cfquery>

Unfortunately, I get the following error:
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in ALTER TABLE statement.

How can I transfer my data with the alter table method?



Website Designed and Developed by Pablo Varando.