Alter Field Type Sql

[gemma-the-husky]

sqlstrg = "ALTER TABLE " & tblname & " ALTER COLUMN [SEQUENCE] TEXT(20);

I am trying to use the above code, which i got from other threads to change a field type in a table from number to text, but i am getting a syntax error executing it.

it isnt the tblname, as if i make a string with the tablename in i still get the same error

any ideas please?

sequence is the column name - its not a reserved word is it?

 

[Atomic Shrimp]

What is the table name? Does it contain spaces?

 

[LPurvis]

It is an ODBC reserved word yes (Reserved Words S), but that shouldn't be a problem and even then you're escaping it in square brackets.

The only obvious potential issue is as Mike suggests... It would be common to escape the table name too in your code, just in case.
sqlstrg = "ALTER TABLE [" & tblname & "] ALTER COLUMN [SEQUENCE] TEXT(20)"

Have you executed it as a literal statement in a query window (in SQL view of course)?
Any syntax errors would be more readily highlighted for you there.
(And a space in the table name would jump out at you).

 

[gemma-the-husky]

no just called dailyexcel

currentdb.execute "ALTER TABLE DAILYEXCEL ALTER COLUMN [SEQUENCE] TEXT(20);"

all versions of this are failing with syntax error (with /without the square brackets)

error 3293 syntax error in statment

----------
using A97 - is that the problem?

the help in A97, doesnt mention alter table alter column

---------
i will try to do it, by adding a column, copying data, dropping column - see if that does it

 

[LPurvis]

Ah right - 97. Yes that will make a difference.
Jet 3.X didn't support changes to datatype.
It's the old hack of adding a column - updating the contents across and dropping the old column.
Three statements instead of one.

FWIW that limitation persisted in DAO into Access 2000 and beyond - but Jet 4 DDL introduced the ALTER COLUMN syntax.

 

[LPurvis]

FWIW - this might be a workaround for you.... i.e. use the Jet 4 provider to update the Jet 3.5 format file.
I say "might" because if the application is open exclusively then ADO won't be able to open a connection to the running file.
In which case you're a bit knackered. If it's a separate BE MDB file then all should be OK - but you obviously will want to change the path to the MDB in the code...

Sub ADOUpdate(strtblname as String)
    
    Dim cnn As Object
    Set cnn = CreateObject("ADODB.Connection")
    
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentDb.Name
    cnn.Execute "ALTER TABLE [" & strtblname & "] ALTER COLUMN [SEQUENCE] TEXT(20)"

    cnn.Close
    Set cnn = Nothing

End Sub

Cheers.

 

[gemma-the-husky]

yeah

must be A97 - done it the hard way, with multiple statements
--------
[edited]

thanks for all the thoughts - i tried to do it also by changing the datatype of the field in the tabledefs (text is type 10) but it wouldnt let me - perhaps its read only)

 

[LPurvis]

Manually in the table designer? ;-) Or using three SQL statements?
The ADO method will work in 97 (given the caveats mentioned) - and allows similar code to work across BE file versions.

 

[gemma-the-husky]

in code

a couple of SQL DDE statements
an update statement
and renaming with tabledefs