changing a field type from a form...

[skinicod]

Hi,

I have a form which creates a table based on various criteria:

Dim conDatabase As ADODB.connection
Dim rstLookup As ADODB.Recordset
Dim strSQL As String

Set conDatabase = CurrentProject.connection
strSQL = "Select [Audit_Freq].[value], '' AS lookup, '' AS notes INTO " & tblName & " from [Audit_Freq] where [Audit_Freq].

= '" & CmbTable.value & "' and [Audit_Freq].[name] = '" & CmbField.value & "' order by [Audit_Freq].[value];"

Set rstLookup = New Recordset
rstLookup.Open strSQL, conDatabase, _
adOpenDynamic, adLockOptimistic

This code all works fine, but after I have created it, i need to make the blank field "notes" into a memo type field.

Does anyone know if you can do this, and if so how??

Cheers,

skinicod

 

[Golom]

Not so simple as it sounds. Although you can, in table design, change the data type of a column, Access behind the scenes, generates considerable code to convert the current contents of the field to the new data type. If you want to change the data type of a field outside that design environment then you need to duplicate that action. In essence it comes down to.

[li]Create a new field of type Memo (use ALTER TABLE ADD COLUMN)[/li]
[li]Run a query that UPDATES the new column with the values from the old one.[/li]
[li]Drop the old column[/li]
[li]Rename the new column to the old column's name.[/li]
[li]Reset the OrdinalPosition of the new column to that of the old column[/li]

You need to mess with ADOX property settings to do all this.

My approach would be to explicitly create the new table with CREATE TABLE DDL; setting the field names and types to what I want, then run an INSERT INTO (rather than SELECT INTO) command to populate it.

 

[skinicod]

I've tried to lookup "CREATE TABLE DDL" and have found code like:

create table [Customer]
(
[ID1] long UNIQUE NOT NULL,
[First Name] text (50),
[Last Name] text (50),
[House Number] text (5),
[Street] text (50),
[City] text (50),
[Post Code] text (15),
[Birth Date] datetime,
[Country] text (50),
)


But how do I add this in to my code - does it have to be saved as dll and the located via a reference??


Cheers,

skinicod