How To ALTER table definition?????

[WebbWill]

I have an Access 97 database as a backend.
VB6 Code supplies the GUI.
I am having trouble changing the field type in a table.
When the data is gathered it is all text.
I would like to change some of the fields to Double's or integer's.
What would the syntax be for the SQL statement.
Have tried
CurCmd.CommandText = "ALTER TABLE i-price ALTER COLUMN ip_a_cost double"
CurCmd.Execute

I use ADODB through code for my connection and command objects.

Thanks for any help.
WebbWill

 

[IFELSETHEN]

The Microsoft Jet database engine doesn't support the use of ALTER TABLE, or any of the data definition language (DDL) statements, with non-Microsoft Jet databases.

 

[CajunCenturion]

To Alter a Column Defintion you can do the following, but you must include a reference to Microsoft ADO Extensions 2.7 for DDL and Security:

Dim cat As ADOX.Catalog
Dim col As ADOX.Column
Dim conn As ADODB.Connection

Set cat = New ADOX.Catalog
cat.ActiveConnection = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<DatabasePath>;&quot;

Set col = New ADOX.Column
col.Name = &quot;NewColName&quot;
col.Type = adDouble
Set col.ParentCatalog = cat
cat.Tables(<TableName>).Columns.Append col

Set conn = New ADODB.Connection
conn.Provider = &quot;Microsoft.Jet.OLEDB.4.0;Data Source=<DatabasePath>;&quot;
conn.Mode = adModeReadWrite
conn.Open
conn.Execute &quot;UPDATE <TableName> SET NewColName = VAL(<OldColName>)&quot; ' Type Conversion Function

cat.Tables(&quot;<TableName>&quot.Columns.Delete (&quot;<OldColName>&quot
Set col = Nothing
Set cat = Nothing
set conn = Nothing
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein