Using SQL to CREATE a TABLE

[PublisoftTelecom]

I'm using an ACCESS database for a Web application in Cold Fusion and I need to create a table dynamically.&nbsp;&nbsp;<br><br>So far it's not so complicated... I use CREATE TABLE and that's it!!!&nbsp;&nbsp;My problem is that I need to define a TEXT field that will allows NULL entries. (Allow zero length Property)<br><br>Thanks for the help!

 

[DougP]

Q128016 article<br>This code changes a fields type but deletes the original and creates a new one with new type at the bottom of the field list<br><br>----------------------------------------<br>Sub AlterFieldType(TblName As String, FieldName As String, NewDataType As String)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Dim db As Database<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Dim qdf As QueryDef<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Set db = CurrentDb()<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' Create a dummy QueryDef object.<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Set qdf = db.CreateQueryDef(&quot;&quot;, &quot;Select * from Table1&quot<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' Add a temporary field to the table.<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;qdf.SQL = &quot;ALTER TABLE [&quot; & TblName & &quot;] ADD COLUMN AlterTempField &quot; & NewDataType<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;qdf.Execute<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' Copy the data from old field into the new field.<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;qdf.SQL = &quot;UPDATE DISTINCTROW [&quot; & TblName & &quot;] SET AlterTempField = [&quot; & FieldName & &quot;]&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;qdf.Execute<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' Delete the old field.<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;qdf.SQL = &quot;ALTER TABLE [&quot; & TblName & &quot;] DROP COLUMN [&quot; & FieldName & &quot;]&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;qdf.Execute<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' Rename the temporary field to the old field's name.<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;db.TableDefs(&quot;[&quot; & TblName & &quot;]&quot.Fields(&quot;AlterTempField&quot.Name = FieldName<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' Clean up.<br>End Sub<br>----------------------------------<br><br>to call sub <br><br> AlterFieldType &quot;Table1&quot;, &quot;Employee&quot;, &quot;LONG&quot;<br>