Why do I get "Syntax Error in ALTER statement" for the following? I am using VB6 and msjet4.0

conn.Execute "ALTER TABLE Table1 RENAME field1 TO field2"

I also tried this to no avail:

conn.Execute "ALTER TABLE Table1 RENAME COLUMN field1 TO field2"

I can execute DROP and ADD with ALTER TABLE with no problem, but get the same error when using MOVE with ALTER TABLE.

Any help greatly appreciated!

Try using the adox reference - Microsoft ADO Extensions for DDL and Security in your vb6 code.

The following is a code extraction using the northwind database:

Dim col As ADOX.Column
Dim cn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

Set cn = New ADODB.Connection

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB;Persist Security Info=False"
cn.Open
Set cat = New ADOX.Catalog
cat.ActiveConnection = cn
Set tbl = cat("customers")
Set col = tbl("Region")
col.Name = "Region1"