I often get frustrated when I try to work on a database on a remote server and it is remotely locked. So I have created a simple form which allows a user to Add, Remove and Alter Tables. You can also Add, Remove, Alter columns inside a table. This code is very easy to modify and add or remove more functions. As it is, this is a very simple application (but I think it is not bad for a newbie like myself!)
Tested with ACCESS only.

As most forms which query a database, you will need two .cfm pages. This code is pretty much ?plug and play.? Just change the name of the DATASOURCE (three locations on the action page) and it should work with little or no alteration. 

The only drawback is having to remember the names of the tables and columns, but there are ways to find these names. I intended to add that at a later date. For now, Let me know what you think of my first tutorial!
The code for page one is below:

<html>
    <head>
        <title>
Database Remote Management Part 1</title>
        <meta http-equiv=
"Content-Type" content="text/html; charset=iso-8859-1">
    </head>

    <body>
        <P>
&nbsp;</P>
        <P>
Modify your database: </P>
        <form name="form1" method="post" action="modifytableaction.cfm">
            <p>
                <select name="CorM" id="CorM">
                    <option value=
"1">Create A New Table</option>
                    <option value=
"ALTER">Modify An Exisiting Table</option>
                    <option value=
"2">Delete A Table</option>
                </select>

            </p>
            <p>
Table to modify:
                <input name="table" type="text" id="table">
            </p>
            <p>
Do what to column:
                <select name="Syntax" id="Syntax">
                    <option value=
"Add">Add new column</option>
                    <option value=
"Alter">Alter</option>
                    <option value=
"Drop">Drop</option>
                    <option selected>
None</option>
                </select> 
            </p>
            <p>
Name of column:
                <input name="column" type="text" id="column"
            </p>
            <p>
Data Type: 
                <select name="type" id="type">
                    <option value=
"text">text</option>
                    <option value=
"memo">memo</option>
                    <option value=
"currency">currency</option>
                    <option value=
"yesno">yesno</option>
                    <option value=
"number">number</option>
                    <option selected>
None</option>
                </select>
            </p>
            <p>
                <input type="submit" name="Submit" value="Submit"
            </p>
        </form>
        <P> Instructions: Select what you would like to do (Add Table, Delete Table or Modify Table). If Add or Delete are picked, all you need to do is enter the name of the table. When add table is picked, it creates a &quot;primary key&quot; named &quot;ID&quot; </P>
        <P>If Modify Table is picked, you can choose to add a new column, modify and existing column or delete a column. If Delete is picked, you must leave NONE in both &quot;Do what to Column&quot; and &quot;Data Type.&quot; If Modify is picked, you must enter the name of an existing column and choose a different data type than it currently is. If Add is picked, you must select a datatype, but can be any (besides none).</P>
        <P>
This code is easily modified. </P>
    </body>
</html>


Here is the code for the action page:

<html>
    <head> 
        <title>
Table has been modified</title>
    </head>

    <body> 

    <CFIF FORM.CorM is "1">
        <cfquery datasource=
"YOURDNS" name="create"
            create table #FORM.table# (ID counter Primary Key)
        </cfquery>
    <cfelse>
        <CFIF FORM.CorM is
"2">
            <cfquery datasource=
"YOURDNS" name="create2"
        
        drop table #FORM.table# 
            </cfquery>
        <cfelse>
            <cfquery datasource=
"YOURDNS" name="create3"
                #FORM.CorM# TABLE #FORM.table#
                #FORM.Syntax# COLUMN #FORM.column# #FORM.type# NULL
            </cfquery>
        </cfif>
    </cfif>

    Finished! <a href="createtable.cfm">Back</a>
   
</body> 
</html>

About This Tutorial
Author: Drew Radley
Skill Level: Beginner 
 
 
 
Platforms Tested: CFMX7
Total Views: 88,004
Submission Date: November 11, 2005
Last Update Date: June 05, 2009
All Tutorials By This Autor: 1
Discuss This Tutorial
  • very accomedated

  • What a great idea! Thanks for the tutorial. One recommendation for anyone else using this is to set YOURDNS as a variable and then you only have to modify the datasource name once...

  • Glad you liked it!

  • Ding dong sugar in the morning. Ding dong sugar at night. Ding dong tap me on the shoulder. Ding ding and say it's all alright. That's a poem I wrote to express how please I am with this coding. I hate having to download my Access db make changes and then upload it again. This code is super fabulous. I'm so happy I want to give myself a firm bare bottom spanking. Here's another poem about this coding. Hyaw budda hyaw budda boo If Pablo can't code it, what about YOU?

Advertisement

Sponsored By...
Mobile App Development (IOS, Android, Cordova, Phonegap, Objective-C, Java) - Austin, Texas Mobile Apps - Touch512, LLC.