add default value

[Tim Mulholland]

I've seen conflicting stuff all over the internet for what i can/can't do
via a SQL query in Access with regards to default values.

Can i do something like this:

ALTER TABLE Notes ALTER COLUMN Parent SET DEFAULT 0

?

If not, what are my other options?

Thanks in advance,

-Tim

 

[Mingqing Cheng [MSFT]]

Hi Tim,

From your descriptions, I understood that youw would like to use ALTER
TABLE query to add a default value to the column. Have I understood you?
Correct me if I was wrong

Based on my knowledge, it seems you should include datatype in the query
like this

ALTER TABLE Notes ALTER COLUMN Parent INT SET DEFAULT 0

Hope it helps and if you have any questions or concerns, don't hesitate to
let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng

Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center

 

[Tim Mulholland]

WHen executing that via my ASP.NET page, i get the following error:

ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in
ALTER TABLE statement.

WHen i execute it directly through Access's query system, i get the same
error (less the ODBC stuff at the front) and it highlights the word "SET" in
the SQL statement.

 

[Mingqing Cheng [MSFT]]

Hi Tim,

Oops, I should have clarified it more clearly. Sorry for that.

DEFAULT is only available through ADO or Jet OLE DB Provider and I think
only this two ways are available for changing the default value of mdb file.

First of all, You should run the codes in this way: create a new form in
the mdb file, add a command button and its On Click Event like this
(using Jet OLE DB Provider)
Private Sub Command0_Click()

CurrentProject().Connection.Execute "ALTER TABLE YourTable " _
& "ALTER COLUMN YourColumn SET DEFAULT 50"

End Sub

Secondly, you could use DAO methods to change the default value like the
codes below
(using ADO)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs("YourTable")
Set fld = tdf.Fields("YourColumn")
fld.DefaultValue = "1"

More detailed information about this is described in the folloing document

Intermediate Microsoft Jet SQL for Access 2000
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/htm
l/acintsql.asp

Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng

Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center

 

[Michael Cheng [MSFT]]

Hi Tim,

I wanted to post a quick note to see if you would like additional
assistance or information regarding the information I have sent to you. We
appreciate your patience and look forward to hearing from you!

If you have any questions or concerns, don't hesitate to let me know. We
are here to be of assistance!


Sincerely yours,

Michael Cheng

Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center