Solved I can't seem to make ALTER COLUMN SET DEFALT to work (1 Viewer)

ADIGA88

Member
Local time
Tomorrow, 02:16
Joined
Apr 5, 2020
Messages
94
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.
1650702264154.png


the command:
Code:
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.


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

Thanks,
 

cheekybuddha

AWF VIP
Local time
Today, 23:16
Joined
Jul 21, 2014
Messages
2,237
Try:
Code:
' ...
    SQL = "ALTER TABLE tblTransmittal ALTER COLUMN strPostedBy CHAR(255) DEFAULT 'Hi';"
' ...
 

ADIGA88

Member
Local time
Tomorrow, 02:16
Joined
Apr 5, 2020
Messages
94
Try:
Code:
' ...
    SQL = "ALTER TABLE tblTransmittal ALTER COLUMN strPostedBy CHAR(255) DEFAULT 'Hi';"
' ...

I tried it with the same syntax error message.

Screenshot 2022-04-23 124653.png
 

cheekybuddha

AWF VIP
Local time
Today, 23:16
Joined
Jul 21, 2014
Messages
2,237
ALTER COLUMN seems weird, but that's the syntax specified in the Access help.

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

cheekybuddha

AWF VIP
Local time
Today, 23:16
Joined
Jul 21, 2014
Messages
2,237
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

AWF VIP
Local time
Today, 23:16
Joined
Jul 21, 2014
Messages
2,237
Perhaps try ADO:
Code:
' ...
    SQL = "ALTER TABLE tblTransmittal ALTER COLUMN strPostedBy TEXT(255) DEFAULT 'Hi';"
    CurrentProject.Connection.Execute SQL
 

ADIGA88

Member
Local time
Tomorrow, 02:16
Joined
Apr 5, 2020
Messages
94
It worked With ADO. 👍

It seems the DAO Parsing issue.
 

Users who are viewing this thread

Top Bottom