Solved I can't seem to make ALTER COLUMN SET DEFALT to work

[ADIGA88]

Hi guys,

I am trying to edit the default value of a text field through the SQL ALTER command but just getting this syntax error.

the command:

Sub ChangeDefaultForPostedByInTransmittal(db As DAO.Database)
  
    Dim SQL As String
    SQL = "ALTER TABLE tblTransmittal ALTER COLUMN strPostedBy SET DEFAULT 'Hi';"
    db.Execute SQL
  
End Sub

I copied the syntax from the W3 website.

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'Sandnes';

Thanks,

 

[cheekybuddha]

Try:

' ...
    SQL = "ALTER TABLE tblTransmittal ALTER COLUMN strPostedBy CHAR(255) DEFAULT 'Hi';"
' ...

 

[ADIGA88]

Try:

' ...
    SQL = "ALTER TABLE tblTransmittal ALTER COLUMN strPostedBy CHAR(255) DEFAULT 'Hi';"
' ...

I tried it with the same syntax error message.

 

[cheekybuddha]

ALTER COLUMN seems weird, but that's the syntax specified in the Access help.

Perhaps try:

' ...
    SQL = "ALTER TABLE tblTransmittal MODIFY COLUMN strPostedBy CHAR(255) DEFAULT 'Hi';"
' ...

 

[ADIGA88]

didn't work

 

[cheekybuddha]

It's the DEFAULT clause causing the problem.

Not sure what the solution is, other than to add a new column with a temp name and the correct default, copy the data over and then drop the original column and rename.

 

[cheekybuddha]

Perhaps try ADO:

' ...
    SQL = "ALTER TABLE tblTransmittal ALTER COLUMN strPostedBy TEXT(255) DEFAULT 'Hi';"
    CurrentProject.Connection.Execute SQL

 

[ADIGA88]

It worked With ADO.

It seems the DAO Parsing issue.

 

[cheekybuddha]

Excellent! Glad it worked for you!